Convert a json format into a structured dataframe

user_11504069 0
Viewed 33

I have this pandas:

results = requests.request("POST", url, headers=headers, data=payload).json()

results

{‘ABC: {’26/03/2021': {‘A’: ‘1234’,
  ‘B’: ‘5678’},
 '29/03/2021': {‘A’: ‘5555’,
  ‘B’: ‘6666’},
 '30/03/2021': {‘A’: '44779',
  ‘B’: '10364'} }

And would you like to convert this dataframe?

COLUMN1 COLUMN2 A B
ABC 26/03/2021 1234 5678
ABC 29/03/2021 5555 6666
ABC 30/03/2021 44779 10364

Could you help me find a way out of this?

pandas dataframe python json
1 Answers

Try (results is your dictionary from the question):

all_data = []
for k, v in results.items():
    for kk, vv in v.items():
        all_data.append({"COLUMN1": k, "COLUMN2": kk, **vv})

df = pd.DataFrame(all_data)
print(df)

Prints:

  COLUMN1     COLUMN2      A      B
0     ABC  26/03/2021   1234   5678
1     ABC  29/03/2021   5555   6666
2     ABC  30/03/2021  44779  10364

Prints:

user_100359850
Related