stuck on how to fill multiIndexed column dataframe

Triki Sadok 0
Viewed 29

I have created a dataframe with multi index columns from lists using:

trans = ['Scale Up', 'Scale Down', 'U']
sources = ['P1', 'P2']
cols = ['date', 'sec']


index = pd.MultiIndex.from_product([trans, sources, cols])
df=pd.DataFrame(columns=index)

df is the dataframe I am supposed to fill, just for printing and understanding the columns I use:

index.to_frame().transpose()

enter image description here

for each trans and source I want to fill date and sec based on another data frame new_positions:

my_data=new_positions.loc[((new_positions['trans']=='Scale Up') & (new_positions['source'] == 'P1'))].sort_values(['score']).tail(10)[['date', 'sec']].copy()

the whole dataframe (sec and date) is supposed to be filled with a loop over level0 and 1. Here is how I want to fill sec and date for level0='Scale Up' and level1='P1'

enter image description here

multi-index dataframe pandas python
1 Answers

Create MultiIndex in columns by DataFrame.set_index with DataFrame.stack and Series.unstack:

new_positions = pd.DataFrame({'trans':['Scale Up'] * 4 + ['Scale Down'] * 4,
                    'source':['P1'] * 2 + ['P2'] * 2 + ['P1'] * 2 + ['P2'] * 2,
                    'date':pd.date_range('2021-01-01', periods=8),
                    'sec':[1,2,3,4,5,6,7,8]})


print (new_positions)
        trans source       date  sec
0    Scale Up     P1 2021-01-01    1
1    Scale Up     P1 2021-01-02    2
2    Scale Up     P2 2021-01-03    3
3    Scale Up     P2 2021-01-04    4
4  Scale Down     P1 2021-01-05    5
5  Scale Down     P1 2021-01-06    6
6  Scale Down     P2 2021-01-07    7
7  Scale Down     P2 2021-01-08    8

s = new_positions.groupby(['trans','source']).cumcount()
df = (new_positions.assign(idx=s)
                   .set_index(['trans','source','idx'])[['date', 'sec']]
                   .stack()
                   .unstack([0,1,3]))
print (df)
trans    Scale Up                    Scale Down                   
source         P1             P2             P1             P2    
             date sec       date sec       date sec       date sec
idx                                                               
0      2021-01-01   1 2021-01-03   3 2021-01-05   5 2021-01-07   7
1      2021-01-02   2 2021-01-04   4 2021-01-06   6 2021-01-08   8
jezrael0
Related