Pandas multi index/groupby

Question

I have a data frame containing several registers from sold products, revenues, prices and dates

DATA        CLIENT     PRODUCT   PRICE
2020-08-28  xxxxxxx    RIOT      20.0

I am to group my information by year/month and product. I am running a group by to_period that extract the exactly information :

dfgmv = dfgift[['PRODUCT','PRICE']].groupby([dfgift.DATA.dt.to_period("M"), 'PRODUCT']).agg(['count','sum'])

This is the output :

                        PRICE
                        count   sum 
DATA       PRODUCT 
2020-08    RIOT         2       40.00

The question is that, as I export to excel, the date column is not interpreted as da date (yyyy-mm). I am trying to convert the yyyy-mm to something like yyyy-mm-dd so Excel understand it.

I´ve read several questions about multi index but my knowledge wasn't enough to use that info to apply here. I tried to change my index to datetime, but, as I run it, I lost the second index column (product).

dfgmv.index = pd.to_datetime(dfgmv.index.get_level_values(0).astype('datetime64[ns]'))

.

             VALOR  
             count  sum
DATA                
2020-08-01   2      40.00

So, How can I change the information format without losing my index?

jch · Accepted Answer · 2022-08-01T21:32:55.900

2

Index.set_levels is designed to allow for the setting of specific index level(s).

dfgmv.index = (
    dfgmv.index.set_levels(dfgmv.index.levels[0].astype('datetime64[ns]'), level=0)
)

Result

                   PRICE      
                   count   sum
DATA       PRODUCT            
2020-08-01 RIOT        1  20.0

edited Aug 01 '22 at 21:32

answered Aug 01 '22 at 20:27

jch

3,600
1
15
17

I am getting an error "ValueError: Level values must be unique: ' and hundreds of timestamp values. – FábioRB Aug 01 '22 at 20:57
1

Try this instead: `dfgmv.index = dfgmv.index.set_levels(dfgmv.index.levels[0].astype('datetime64[ns]'), level=0)` – jch Aug 01 '22 at 21:10
This worked. If I understood, instead of replacing the index for a new one, you just changed its format, that´s right? – FábioRB Aug 01 '22 at 21:23
Well Pandas loves to make copies of things. So the flow of this would actually be that `dfgmv.index.levels[0].astype('datetime64[ns]'` actually returns a copy of the DatetimeIndex at level 0 with its type changed to `datetime64[ns]`. Then `.set_levels(..., level=0)` uses that modified index level object to replace level 0 in a copy of the index that was created by `dfgmv.index`. Then after all that the entire original index is overwritten with the new, modified index with `dfgmv.index = ...`. – jch Aug 01 '22 at 22:14

score 1 · Answer 2 · answered Aug 01 '22 at 20:22

1

You can change your groupby to include start of month for each date you have:

dfgmv = dfgift[['PRODUCT','PRICE']].groupby([dfgift.DATA.astype('datetime64[M]'), 'PRODUCT']).agg(['count','sum'])
dfgmv

                      PRICE
                      count     sum
DATA        PRODUCT         
2020-08-01  RIOT      1         20.0

answered Aug 01 '22 at 20:22

qaziqarta

1,782
1
4
11

Thanks. This way worked too! – FábioRB Aug 01 '22 at 21:23

Pandas multi index/groupby

2 Answers2