Python Forum

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

football.json

{
	"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:

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.

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

Can use json_normalize() to take into pandas.

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')

>>> 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?

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

To Excel.

df.to_excel("fooball.xlsx")

(Aug-31-2022, 04:42 PM)deanhystad Wrote: [ -> ]Excel (.xlsx, .xlsm, .xlsb) or CSV?

*.xlsx

(Aug-31-2022, 04:59 PM)snippsat Wrote: [ -> ]Can use json_normalize() to take into pandas.

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

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

how can we implement into urs response?

Use filters.

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!

(Aug-31-2022, 08:48 PM)deanhystad Wrote: [ -> ]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.