python - How to turn a Pandas Dataframe into a Dictionary of Dataframes by grouping columns

Question

I have a DataFrame that was built from 3D data and takes the form:

Index: A, B

Columns: 1.a, 1.b, 2.a, 2.b

I'm trying to unpack this into a dictionary mapping {A, B} to DataFrames with Index {1,2} and Columns {a,b}

example input:

aa = pandas.DataFrame({'1.a':[1,2], '1.b':[3,4], '2.a':[5,6], '2.b':[7,8], 'index':['A', 'B']}).set_index('index')

goal output:

bb = {'A': pandas.DataFrame({'a':[1,5], 'b':[3,7], 'index':[1,2]}), 'B': pandas.DataFrame({'a':[2,6], 'b':[4,8], 'index':[1,2]}) }

Any thoughts?

question from:https://stackoverflow.com/questions/65943874/how-to-turn-a-pandas-dataframe-into-a-dictionary-of-dataframes-by-grouping-colum

Answer 1

Looks like you can change the column name to MultiIndex and unstack:

aa.columns = pd.MultiIndex.from_tuples([a.split('.') for a in aa.columns])

out = {k:v.unstack() for k,v in aa.iterrows()}

Output:

{'A':    a  b
 1  1  3
 2  5  7,
 'B':    a  b
 1  2  4
 2  6  8}