export into excel, how to implement pandas into for-loop

[deneme2]

Hello all,
I want to export json data file into excel in terms of loop code I wrote in the below.

football.json

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38

{     "results": [         {             "leagues": [                 {                     "matches": [                         {                             "league": "Premier League",                             "teams": "Liverpool - Chelsea",                             "score": [                                 "3:0"                             ]                         },                         {                             "league": "Premier League",                             "teams": "Man Utd - Arsenal",                             "score": [                                 "2:1"                             ]                         }                       ]                 },                 {                     "matches": [                         {                             "league": "La Liga",                             "teams": "Atletico Madrid - Villareal",                             "score": [                                 "0:2"                             ]                         }                     ]                 }             ]         }     ] }

football.py:

1 2 3 4 5 6 7 8 9 10

import json   with open("football.json") as f:     jsondata = json.load(f)   for a in jsondata["results"][0]["leagues"]:     for b in a["matches"]:         if "Liverpool" in b["teams"] or "La Liga" in b["league"]:             continue         print(b["league"],b["teams"],b["score"][0])

output:

Output:
Premier League Man Utd - Arsenal 2:1

I want to have this output in excel file in this format

Output:
Premier League,Man Utd - Arsenal,2:1

Thanks all for your interest.

[deanhystad]

Excel (.xlsx, .xlsm, .xlsb) or CSV?

[snippsat]

Can use json_normalize() to take into pandas.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45

import pandas as pd pd.set_option('display.max_columns', None) pd.set_option('display.expand_frame_repr', False) pd.set_option('max_colwidth', None)   data={   "results": [     {       "leagues": [         {           "matches": [             {               "league": "Premier League",               "teams": "Liverpool - Chelsea",               "score": [                 "3:0"               ]             },             {               "league": "Premier League",               "teams": "Man Utd - Arsenal",               "score": [                 "2:1"               ]             }           ]         },         {           "matches": [             {               "league": "La Liga",               "teams": "Atletico Madrid - Villareal",               "score": [                 "0:2"               ]             }           ]         }       ]     }   ] }   d = pd.json_normalize(data['results']) df = pd.json_normalize(d['leagues'][0],  record_path='matches')

1 2 3 4 5 6 7 8 9 10 11

>>> df            league                        teams  score 0  Premier League          Liverpool - Chelsea  [3:0] 1  Premier League            Man Utd - Arsenal  [2:1] 2         La Liga  Atletico Madrid - Villareal  [0:2] >>> >>> df.iloc[1] league       Premier League teams     Man Utd - Arsenal score                 [2:1] Name: 1, dtype: object

What was the score between Liverpool - Chelsea?

1 2	>>> df.loc[df['teams'] == 'Liverpool - Chelsea', 'score'] 0    [3:0]

To Excel.

1	df.to_excel("fooball.xlsx")

[deneme2]

Excel (.xlsx, .xlsm, .xlsb) or CSV?

*.xlsx

[deneme2]

Can use json_normalize() to take into pandas.

thanks a lot for reply Admin.
but what about this part in my loop?

1 2	if "Liverpool" in b["teams"] or "La Liga" in b["league"]:     continue

how can we implement into urs response?

[deanhystad]

Use filters.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40

import pandas as pd   data = {     "results": [         {             "leagues": [                 {                     "matches": [                         {                             "league": "Premier League",                             "teams": "Liverpool - Chelsea",                             "score": ["3:0"],                         },                         {                             "league": "Premier League",                             "teams": "Man Utd - Arsenal",                             "score": ["2:1"],                         },                     ]                 },                 {                     "matches": [                         {                             "league": "La Liga",                             "teams": "Atletico Madrid - Villareal",                             "score": ["0:2"],                         }                     ]                 },             ]         }     ] }   df = pd.json_normalize(data["results"][0]["leagues"], record_path="matches") print("Everyone", df, sep="\n") no_chelsea_or_laliga = df[     ~((df["teams"].str.contains("Chelsea")) | (df["league"] == "La Liga")) ] print("\nAfter exclusions", no_chelsea_or_laliga, sep="\n")

Output:
Everyone
           league                        teams  score
0  Premier League          Liverpool - Chelsea  [3:0]
1  Premier League            Man Utd - Arsenal  [2:1]
2         La Liga  Atletico Madrid - Villareal  [0:2]

After exclusions
           league              teams  score
1  Premier League  Man Utd - Arsenal  [2:1]

df["teams"].str.contains("Chelsea") returns a dataframe of booleans where 1 indicates one of the teams is Chelsea

df["league"] == "La Liga" returns a dataframe of bolleans where 1 indicates the league is La Liga

Or ( | ) the results to get a data frame where 1 indicates that Chelsea played or the league is La Liga

Finally invert ( ~ ) the results to get a dataframe where 1 indicates Chelsea did not play and the league is not La Liga. Use this data frame to create a new dataframe consisting of rows where Chelsea did not play and the league was not La Liga.

I cannot believe you want to exclude Liverpool!

[deneme2]

I cannot believe you want to exclude Liverpool!

:)))))))
thanks a lot @deanhystad , it worked so clean.
@snippsat , no more modification needed.
thanks a lot for giving your valuable time.