I have a JSON file with array key and i want to convert to a excel file with two tables. How can I do this with Python? This is my JSON file :

[{"information": {"title": " KUWAIT ENERGY CO. KSCC", "Member_No": " 100018", "Auth_Capital":" 127000000",  "Email": " [email protected]"}, "users": [{"name": "منصور احمد محمد بوخمسين", "role": "  Chairman of the board of Directors", "nationality": " KUWAIT"},{"name": "محمد عادل التميمي", "role": "  Vice Chairman and Cheif Executive", "nationality": " CANADA"}]},
{"information":{"title": " SALEM INTERNATIONAL ADVERTISING CO.", "Member_No": " 100021",   "Auth_Capital": " 10000",     "Email": null},  "users": [{"name": "SALEM M. AL-AJMI", "role": "  Authorized partner with Doc.", "nationality": " KUWAIT"}]},
{"information":{"title": " POWER TOUCH GEN. TRAD. & CONT. CO.", "Member_No": " 100026",   "Auth_Capital": " 500000",     "Email": null},  "users": [{"name": "ريم محمد سالم الحريص", "role": "  Partner", "nationality": " KUWAIT"},{"name": "محمد 0 0", "role": "  Special Power of Attorney", "nationality": " KUWAIT"}, {"name": "سالم 0 0", "role": "  Special Power of Attorney", "nationality": " KUWAIT"}]}]

However i want to convert it to excel table with relation with the two tables many-to-many.

Like this : The first table contains the information of compagnies:

And for the second table contains the information of users and the associated company :

I am still a beginner in Python.

I coded the following code which can convert a simple json file

import json
import pandas as pd

with open('C:/Users/DELL-PC/Desktop/sample.json',encoding="utf8") as json_file:
    data = json.load(json_file)


df = pd.DataFrame(data)

df.to_excel('C:/Users/DELL-PC/Desktop/Test.xlsx')

CodePudding user response：

You need to split the data yourself and then create 2 separate dataframes. See the code example below.

EDIT: if you need relations between the two tables, you need to add the Member_No from the company to each user. I've updated the code to show how.

import json
import pandas as pd


def split_data(data):
    users = []
    companies = []
    for item in data:
        for user in item["users"]:
            user["Member_No"] = item["information"]["Member_No"]
            users.append(user)
        companies.append(item["information"])
    return users, companies




with open("jsonfilegoeshere.json") as infile:
    input_data = json.load(infile)


users, companies = split_data(input_data)

users_df = pd.DataFrame(users)
companies_df = pd.DataFrame(companies)
print(users_df)
print("\n\n\n")
print(companies_df)

output

                      name                                  role nationality Member_No
0  منصور احمد محمد بوخمسين    Chairman of the board of Directors      KUWAIT    100018
1        محمد عادل التميمي     Vice Chairman and Cheif Executive      CANADA    100018
2         SALEM M. AL-AJMI          Authorized partner with Doc.      KUWAIT    100021
3     ريم محمد سالم الحريص                               Partner      KUWAIT    100026
4                 محمد 0 0             Special Power of Attorney      KUWAIT    100026
5                 سالم 0 0             Special Power of Attorney      KUWAIT    100026




                                  title Member_No Auth_Capital                             Email
0                KUWAIT ENERGY CO. KSCC    100018    127000000   [email protected]
1   SALEM INTERNATIONAL ADVERTISING CO.    100021        10000                              None
2    POWER TOUCH GEN. TRAD. & CONT. CO.    100026       500000                              None