Unpacking a nested dictionary to a pandas DataFrame

Question

My dictionary is currently setup this way:

{'0001': {'Batting Hours': [79, 154, 50, 172],
  'Bowling Hours': [101, 82, 298],
  'Date': ['02/02/2019', '02/01/2019', '02/04/2019', '02/03/2019']},
 '0002': {'Batting Hours': [7, 23, 40],
  'Bowling Hours': [14, 30, 43],
  'Date': ['02/04/2019', '02/01/2019', '02/02/2019']}}

How do I unwrap this dictionary so that the dataframe has an output like this:

Code        Date              Batting Hours     Bowling Hours 
0001        02/02/2019                79                   101            
0001        02/01/2019                154                   82

I tried looking at documentation on how other similar data structures are unwrapped but I can't seem to get through to mine.

I'm currently appending the values into a list like this

player_agg_hours_dict[Player]['Batting Hours'].append(aggregate_batting_hours)

I'm trying to output to a dataframe like this:

output_df = pd.DataFrame.from_dict(player_agg_hours_dict, orient='index').transpose() # convert dict to dataframe

and I know that the from_dict() parameters have to be something different.

for the first entry in the dictionary there're 4 dates but only 3 Bowling Hours — sam46, Feb 04 '19 at 18:09
Looks like I closed it a bit too quickly. Thanks for your patience. Will write an answer. — cs95, Feb 04 '19 at 18:30

score 2 · Answer 1 · answered Feb 04 '19 at 18:31

One method is to use a combination of stack and unstack:

v = pd.DataFrame(dct).stack()

(pd.DataFrame(v.tolist(), index=v.index)
   .stack()
   .unstack(0)
   .reset_index(level=1, drop=True)
   .rename_axis('Code')
   .reset_index())

   Code Batting Hours Bowling Hours        Date
0  0001            79           101  02/02/2019
1  0001           154            82  02/01/2019
2  0001            50           298  02/04/2019
3  0001           172           NaN  02/03/2019
4  0002             7            14  02/04/2019
5  0002            23            30  02/01/2019
6  0002            40            43  02/02/2019

You can also do this in a single step by starting with concat:

(pd.concat({k: pd.DataFrame.from_dict(v, orient='index') for k,v in dct.items()})
   .stack()
   .unstack(1)
   .reset_index(level=1, drop=True)
   .rename_axis('Code')
   .reset_index())

   Code        Date Batting Hours Bowling Hours
0  0001  02/02/2019            79           101
1  0001  02/01/2019           154            82
2  0001  02/04/2019            50           298
3  0001  02/03/2019           172           NaN
4  0002  02/04/2019             7            14
5  0002  02/01/2019            23            30
6  0002  02/02/2019            40            43

score 0 · Answer 2 · answered Feb 04 '19 at 18:37

You can use pd.concat with a generator expression. This assumes an input dictionary dct and your lists, for any given Code, are all the same length.

df = pd.concat((pd.DataFrame({**{'Code': key}, **value}) \
                for key, value in dct.items()), ignore_index=True)

print(df)

   Batting Hours  Bowling Hours  Code        Date
0             79            101  0001  02/02/2019
1            154             82  0001  02/01/2019
2             50            298  0001  02/04/2019
3            172            100  0001  02/03/2019
4              7             14  0002  02/04/2019
5             23             30  0002  02/01/2019
6             40             43  0002  02/02/2019

Unpacking a nested dictionary to a pandas DataFrame

2 Answers2