CODEWITHSUNDEEP
pythonpandasdataframepivot-table
00__00__00
00__00__00

Reputation: 5347

dataframe to list of dataframes based on two columns

I have a dataframe which looks as follows:

df=
         columnDate    column_key      v1   v2 ... vN
0         01/01/2000       'a'         1    3
1         01/01/2000       'b'         2    4
2         02/01/2000       'a'         3    5
3         02/01/2000       'b'         4    6
...

I would like to split this dataframe into a dictionary of dataframes, one for each of the original v1..vN columns, and to have the possible values in columnDate as new columns and values of columns_key as index.

For this example, the desired output is:

output['v1']=
      01/01/2000    02/01/2000    
'a'    1               3      
'b'    2               4       

output['v2']=
      01/01/2000    02/01/2000    
'a'    3               5      
'b'    4               6

N.B: the missing values in df should be filled with np.nan

Upvotes: 1

Views: 33

Answers (1)

jezrael
jezrael

Reputation: 863166

Use set_index with unstack:

df1 = df.set_index(['column_key','columnDate']).unstack()
print (df1)
                   v1                    v2           
columnDate 01/01/2000 02/01/2000 01/01/2000 02/01/2000
column_key                                            
'a'                 1          3          3          5
'b'                 2          4          4          6

Then in dict comprehension select by first level of MultiIndex by xs:

output = {x:df1.xs(x, axis=1) for x in df1.columns.levels[0]}
print (output)
{'v1': columnDate  01/01/2000  02/01/2000
column_key                        
'a'                  1           3
'b'                  2           4, 'v2': columnDate  01/01/2000  02/01/2000
column_key                        
'a'                  3           5
'b'                  4           6}

print (output['v1'])
columnDate  01/01/2000  02/01/2000
column_key                        
'a'                  1           3
'b'                  2           4

print (output['v2'])
columnDate  01/01/2000  02/01/2000
column_key                        
'a'                  3           5
'b'                  4           6

Upvotes: 1

Related Questions