Python dataframe to nested json file

Question

I have a python dataframe as below. python dataframe:-

Emp_No  Name    Project Task
1   ABC P1  T1
1   ABC P2  T2
2   DEF P3  T3
3   IJH Null    Null

I need to convert it to json file and save it to disk as below

Json File

 {
   "Records"[
      {
         "Emp_No":"1",
         "Project_Details":[
            {
               "Project":"P1",
               "Task":"T1"
            },
            {
               "Project":"P2",
               "Task":"T2"
            }
         ],
         "Name":"ÄBC"
      },
      {
         "Emp_No":"2",
         "Project_Details":[
            {
               "Project":"P2",
               "Task":"T3"
            }
         ],
         "Name":"DEF"
      },
      {
         "Emp_No":"3",
         "Project_Details":[
            
         ],
         "Name":"IJH"
      }
   ]
}

Answer 1

I feel like this post is not a doubt per se, but a cheecky atempt to avoid formatting the data, hahaha. But, since i'm trying to get used to the dataframe structure and the different ways of handling it, here you go!

import pandas as pd

asutosh_data = {'Emp_No':["1","1","2","3"], 'Name':["ABC","ABC","DEF","IJH"], 'Project':["P1","P2","P3","Null"], 'Task':["T1","T2","T3","Null"]}

df = pd.DataFrame(data=asutosh_data)

records = [] 

dif_emp_no = df['Emp_No'].unique()

for emp_no in dif_emp_no :

    emp_data = df.loc[df['Emp_No'] == emp_no]
    emp_project_details = []
    for index,data in emp_data.iterrows():
        if data["Project"]!="Null":
            emp_project_details.append({"Project":data["Project"],"Task":data["Task"]})
    records.append({"Emp_No":emp_data.iloc[0]["Emp_No"], "Project_Details":emp_project_details , "Name":emp_data.iloc[0]["Name"]})

final_data = {"Records":records}

print(final_data)

If you have any question about the code above, feel free to ask. I'll also leave below the documentation i've used to solve your problem (you may wanna check that):

• unique : https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.unique.html
• loc : https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.loc.html
• iloc : https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.iloc.html