Split/Expand Dataframe based on column values

Question

I have a DataFrame like the below one, with identifiers as a column on top of an existing dateindex.

pd.DataFrame(index = [pd.to_datetime('2021-01-01'), pd.to_datetime('2021-01-01'),pd.to_datetime('2021-01-02'),pd.to_datetime('2021-01-02'), pd.to_datetime('2021-01-03'),pd.to_datetime('2021-01-03')], columns=['id','A', 'B'], data=[['foo',1,5],['bar',8,12],['foo',7,1], ['bar',5,1], ['foo',4,3],['bar',7,1]])

Out[6]: 
             id  A   B
2021-01-01  foo  1   5
2021-01-01  bar  8  12
2021-01-02  foo  7   1
2021-01-02  bar  5   1
2021-01-03  foo  4   3
2021-01-03  bar  7   1

My goal is to create a new sub-dataframes for each of the columns (A and B) except id, with dateIndex as single Index, and id (foo, bar) as column names. The expected output is shown below:

A
Out[9]: 
            foo  bar
2021-01-01    1    8
2021-01-02    7    5
2021-01-03    4    7

B
Out[11]: 
            foo  bar
2021-01-01    5   12
2021-01-02    1    1
2021-01-03    3    1

Answer 1

A, B = map(df.set_index('id', append=True).unstack().get, ['A', 'B'])

print(A)

id          bar  foo
2021-01-01    8    1
2021-01-02    5    7
2021-01-03    7    4

print(B)

id          bar  foo
2021-01-01   12    5
2021-01-02    1    1
2021-01-03    1    3

Answer 2

This just simply:

out = df.set_index('id',append=True).unstack('id')
# if you have columns other than `A`,`B`:
# out = df.set_index('id',append=True)[['A','B']].unstack('id')

then you can do

out['A']

which gives:

id          bar  foo
2021-01-01    8    1
2021-01-02    5    7
2021-01-03    7    4

and similarly for out['B']. I found this is much easier and less error prone than hard-coding the variables to A,B.