CODEWITHSUNDEEP
pythonpandaspandas-groupby
Built13
Built13

Reputation: 125

How to Groupby two columns and keep the rest of columns the same?

The DataFrame looks this way: 

Date       Symbol    Open     High    Low    Close   Volume

2016-01-04  AAPL    102.61  105.368  102.00  105.35  67649387.0
2016-01-05  AAPL    105.75  105.850  102.41  102.71  55790992.0
2016-01-06  KO      100.56  102.370  99.87   100.70  68457388.0

And I am trying to make it look this way:

                          Open    High     Low       Close    Volume
Symbol     Date
 AAPL      2016-01-04    102.61   105.368  102.00    105.35  67649387.0
           2016-01-05    105.75   105.850  102.41    102.71  55790992.0
           2016-01-06    100.56   102.370  99.87     100.70  68457388.0

 KO        2016-01-04    102.61   105.368  102.00    105.35  67649387.0
           2016-01-05    102.61   105.368  102.00    105.35  67649387.0
           2016-01-06    102.61   105.368  102.00    105.35  67649387.0

I tried:

  mydata.groupby(['Symbol','Date']).agg()

I also tried reset_index and merging on index, but didn't work out either

Upvotes: 0

Views: 68

Answers (1)

BENY
BENY

Reputation: 323226

If I understand your point ..

df.set_index(['Date','Symbol']).unstack(0).stack(dropna=False).groupby(level='Symbol').ffill().bfill()
Out[231]: 
                     Open     High     Low   Close      Volume
Symbol Date                                                   
AAPL   2016-01-04  102.61  105.368  102.00  105.35  67649387.0
       2016-01-05  105.75  105.850  102.41  102.71  55790992.0
       2016-01-06  105.75  105.850  102.41  102.71  55790992.0
KO     2016-01-04  100.56  102.370   99.87  100.70  68457388.0
       2016-01-05  100.56  102.370   99.87  100.70  68457388.0
       2016-01-06  100.56  102.370   99.87  100.70  68457388.0

Upvotes: 3

Related Questions