how to convert multi index data to dataframe

Question

i have a list like this.

result =  [
{'Global': {'NewConfirmed': 100282, 'TotalConfirmed': 1162857, 'NewDeaths': 5658, 'TotalDeaths': 63263, 'NewRecovered': 15405, 'TotalRecovered': 230845}},
{'Global': {'NewConfirmed': 106598, 'TotalConfirmed': 1252421, 'NewDeaths': 5972, 'TotalDeaths': 67572, 'NewRecovered': 11066, 'TotalRecovered': 241599}}
]

I want to convert this data into dataframe as below.

   NewConfirmed  TotalConfirmed  NewDeaths  TotalDeaths  NewRecovered  TotalRecovered
0        100282         1162857       5658        63263         15405          230845
1        106598         1252421       5972        67572         11066          241599

tried many possibilites, but could not find a solution.

jezrael · Accepted Answer · 2020-04-06 07:08:05Z

4

Use list comprehension for select by Global with DataFrame constructor:

df = pd.DataFrame([x['Global'] for x in result])
print (df)
   NewConfirmed  TotalConfirmed  NewDeaths  TotalDeaths  NewRecovered  \
0        100282         1162857       5658        63263         15405   
1        106598         1252421       5972        67572         11066   

   TotalRecovered  
0          230845  
1          241599

If more keys in outer dictionaries use json.json_normalize:

from pandas.io.json import json_normalize

df = json_normalize(result)
print (df)
   Global.NewConfirmed  Global.TotalConfirmed  Global.NewDeaths  \
0               100282                1162857              5658   
1               106598                1252421              5972   

   Global.TotalDeaths  Global.NewRecovered  Global.TotalRecovered  
0               63263                15405                 230845  
1               67572                11066                 241599

answered Apr 6, 2020 at 7:08

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Vijaya Bhaskar Over a year ago

Thanks a lot.. the second option i did get it, however, the header had key= global added to each filed which i was not looking for. Thanks a lot.

Vijaya Bhaskar Over a year ago

But why is that when we use a for loop instead of list comprehension, it throws error like raise ValueError("If using all scalar values, you must pass an index") ValueError: If using all scalar values, you must pass an index ? This is why i was not able to obtain desired result.

jezrael Over a year ago

@VijayaBhaskar - then is possible use df.columns = df.columns.str.replace('Global.','') or df.columns = df.columns.str.split('.').str[1]

jezrael Over a year ago

@VijayaBhaskar - Is possible edit question with your loop solution?

Collectives™ on Stack Overflow

how to convert multi index data to dataframe

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related