convert a dict to a dataframe with one more level

Question

i have a dict, whose two elements are two dataframes with same index and column names. Like the code below:

my_dict = {}
my_dict['df1'] = pd.DataFrame(np.arange(6).reshape(3,2),index = ['r1','r2','r3'],columns=['c1', 'c2'])
my_dict['df2'] = pd.DataFrame((np.arange(6)+10).reshape(3,2),index = ['r1','r2','r3'],columns=['c1', 'c2'])

my_dict['df1'] 
Out[1]: 
    c1  c2
r1   0   1
r2   2   3
r3   4   5

my_dict['df2']
Out[2]: 
    c1  c2
r1  10  11
r2  12  13
r3  14  15

How can i convert to the dict into one dataframe. Index will be kept same as the original one, but i have one more level for columns like the following:

    df1     df2
    c1  c2  c1  c2
r1   0   1  10  11
r2   2   3  12  13
r3   4   5  14  15

Did you try this answer: https://stackoverflow.com/questions/18262962/convert-dataframe-columns-to-multiindex — Martien Lubberink, Aug 04 '18 at 02:35
Possible duplicate of [Concatenate Pandas columns under new multi-index level](https://stackoverflow.com/questions/23600582/concatenate-pandas-columns-under-new-multi-index-level) — niraj, Aug 04 '18 at 02:42
As suggested in link above, you can try `pd.concat(my_dict.values(), axis=1, keys=my_dict.keys())` — niraj, Aug 04 '18 at 02:42
Thank you! @student thats great.One minor question, the link mentions 'using d.values and d.keys this way should be avoided, since it's not guaranteed that the order will be maintained'. Do you know how to preserve the order? — AAA, Aug 04 '18 at 03:21
I added the answer following the discussion pointed, you can check if it works. — niraj, Aug 04 '18 at 04:01

Martien Lubberink · Answer 1 · 2018-08-04T03:48:21.527

0

Did you try this:

df1=my_dict['df1']
df2=my_dict['df2']
df1.columns = [['df1']*df1.shape[1],df1.columns]
df2.columns = [['df2']*df2.shape[1],df2.columns]
df1.join(df2)

edited Aug 04 '18 at 03:48

answered Aug 04 '18 at 02:46

Martien Lubberink

2,614
1
19
31

thanks. it works. but i think student's answer fit my case better, as i have to combine hundreds of dataframes in the same week. – AAA Aug 04 '18 at 03:21
yep student beat me to it! – Martien Lubberink Aug 04 '18 at 03:51

niraj · Answer 2 · 2018-08-04T04:07:12.507

May be you need something similar to the other answer. As discussion in other answer points out, simply using pd.concat(my_dict.values(), axis=1, keys=my_dict.keys()) does not guarantee same order for .values() and .keys().

So, instead k, values = zip(*my_dict.items()) can be used to preserve the order and use them with pd.concat:

k, values = zip(*my_dict.items())
new_df = pd.concat(values, keys = k, axis=1)

convert a dict to a dataframe with one more level

2 Answers2