0

How to convert the below in pandas :

enter image description here

To

enter image description here

Here is my answer : The data I had was a nested json :

The json I had consist of a row containing a Json , which when normalized contained another json . Below I have normalized the entire json into a dataframe.

import json
import pandas as pd
from pandas.io.json import json_normalize
#Load Json to DF
with open(r"C:\Users\data\Collection.json") as f:
    data = json.load(f,strict=False)

deep_df = pd.DataFrame(data)
deep_df.head
df_s1= json_normalize(deep_df.data.loc["mdata"])
df_s1.rename(columns={"docId": "docId_head", "id_colId": "id_colId_head"},inplace='True')
df_s2 = df_s1.set_index(['docId_head', 'id_colId_head','moref','objType'])
df_s3=pd.DataFrame()
for idx in df_s2['values'].index:
    #print(idx[0])
    df_s3=df_s3.append(json_normalize(df_s2['values'][idx]).drop('docId',axis=1).set_index(['retrievePath']).T.reset_index().join(pd.DataFrame(df_s2.index[idx[0]-1]).T),sort=False)
Kimi
  • 332
  • 2
  • 11
  • 25
  • That's a straightforward `pivot_table` ;) – rafaelc Jul 25 '19 at 19:07
  • You have the dupe you need, but in the future, please review [ask] and learn how to create a [mcve], that means no images of code! It's also important to demonstrate that you have put forth effort to solve your own problem – user3483203 Jul 25 '19 at 19:08
  • what should be the aggfunc ? – Kimi Jul 25 '19 at 19:15
  • I did try but couldn't do it with pivot or pivot_table, looking forward for you inputs – Kimi Jul 25 '19 at 19:40
  • 1
    this worked for me : pd.concat({a: b.reset_index(drop=True) for a, b in df.groupby('retrievePath')['value']}, axis=1) – Kimi Jul 25 '19 at 19:40
  • Can you please remove the duplicate, since the problem which I asked cannot be handled using pivot_table without storing NAN values ? or if you know it can be achieved by pivot_table , please provide your inputs. – Kimi Jul 26 '19 at 09:36

0 Answers0