Dataframe nested dictionary to multi-index dataframe

Question

First to construct a simplified version of my question dataset, this is only helping to create a sample, I only have the final nest_dict on hand. Meaning I dont have dfs separately:

df1 = pd.DataFrame({'val':{'a': 1, 'b':2, 'c':3}})
df2 = pd.DataFrame({'val':{'a': 3, 'b':4, 'c':1}})
df3 = pd.DataFrame({'val':{'a': 2, 'b':1, 'c':4}})
dfs = [df1, df2, df3]
dates = ['2017-01-26','2017-02-03','2017-02-10']
nest_dict = {}
for date, d in zip(dates, dfs):
        nest_dict[date] = d

I would like to convert it to a Multi-Index Dataframe.

date1 a val1
      b val2
      c val3
date2 a val1
      b val2
      c val3
....

Its basically a dataframe with a date as key, it could easily become a panel, but since its been deprecated, how would I transform it to a multiindex dataframe? I did try following, but it does not work:

d = {(key, item) for key, item in corr_SH50.items()}
TypeError: 'DataFrame' objects are mutable, thus they cannot be hashed

Thanks for replying. I would like the date be the primary index, and a,b,c be secondary index. — user14384765
– user14384765, Commented Jun 5, 2021 at 19:41
later, i will need to use pivotal table to add different dates' value up — user14384765
– user14384765, Commented Jun 5, 2021 at 19:43

anky · Accepted Answer · 2021-06-05 19:43:30Z

1

concat with keys:

pd.concat(dfs,keys=dates)

              val
2017-01-26 a    1
           b    2
           c    3
2017-02-03 a    3
           b    4
           c    1
2017-02-10 a    2
           b    1
           c    4

answered Jun 5, 2021 at 19:43

anky

75.3k11 gold badges46 silver badges76 bronze badges

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

2 Comments

user14384765 Over a year ago

I don't have access to dfs though, its only helping to create a nested dictionary. I only have the final nest_dict on hand

anky Over a year ago

@user14384765 even better: pd.concat(nest_dict) would also work then

Collectives™ on Stack Overflow

Dataframe nested dictionary to multi-index dataframe

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related