How to convert GroupBy object in pandas to multiindexed dataframe

Question

I have a raw dataframe that looks like

        codcet  placa_encoded   date   time_seconds velocidade
5031 490191222  431.0      2021-03-11   70079.0      51
5032 490221211  431.0      2021-03-11   72219.0      55
7991 490361213  562.0      2021-03-11   28559.0      24
7992 490361232  562.0      2021-03-11   29102.0      29              
7993 490361221  562.0      2021-03-11   30183.0      33                 
...

Where the numbers on the far left are indexes from the original dataset. My goal is to convert this into a dataframe indexed by placa_encoded and by n, a counter within each group that then looks like

placa_encoded  n    time_seconds  velocidade            codcet    
431.0          0      70079.0           51              490191222   
431.0          1      72219.0           55              490221211   
562.0          0      28559.0           24              490361213   
562.0          1      29102.0           29              490361232
562.0          2      30183.0           33              490361221

That is, I aim to groupby('placa_encoded') then add another column n that counts the position within each group. The row should be indexed by both placa_encoded and n. I think I can use cumcount() to do this but it's unclear to me how to add it as a column since groupby doesn't product a dataframe I can assign to. I looked at this question but it seems they use .count() to convert it to a dataframe, and I want to preserve the data instead of getting any counts. I also tried to use pd.DataFrame(gbplaca) and pd.DataFrame(gbplaca.groups) to no avail.

Thank you so much!

ArchAngelPwn · Accepted Answer · 2022-06-10 16:12:41Z

1

I think this is what you are wanting

df['n'] = df.sort_values(['time_seconds'], ascending=True).groupby(['placa_encoded']).cumcount()
df = df.set_index(['placa_encoded', 'n'])
df

This is a multi-index with 'placa_encoded' and 'n'

edited Jun 10, 2022 at 16:12

answered Jun 10, 2022 at 16:06

ArchAngelPwn

3,0461 gold badge6 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

ArchAngelPwn Over a year ago

I updated my answer to make it a multi-index. Is that what you were looking for?

Tanishq Kumar Over a year ago

Yes, thanks! In general, should we always operate on the original dataframe and use groupby.agg() type methods to create new columns we can add to the original dataframe? What if I had wanted to make a dataframe where the rows were grouped by/clumped by, say, velocidade, instead?

ArchAngelPwn Over a year ago

Its difficult to say as that is kind of a general question, every situation is different so its outside the bounds of a SO comment (not trying to be evasive just a kind of big question) in this situation though if you wanted to do velocidade instead you would just replace the placa_encoded' from the code above...does that answer your question (or at least sort of)

Tanishq Kumar Over a year ago

Well, the original dataframe already has rows grouped by placa_encoded, but not by velocidad. That's what I'm asking -- how would we have done the same if the dataframe wasn't grouped by placa_encoded. We can't just run groupby('placa_encoded') then the same commands, because groupby doesn't return a dataframe we can run set_index on, but instead a DataFrameGroupBy object.

ArchAngelPwn Over a year ago

Oh so you got a dataframe doing a groupby on velocidad then from that dataframe you did another group by using placa_encoded?

aichao · Accepted Answer · 2022-06-10 16:20:58Z

All you have to do is to set the column in df (named 'n') with the result from df.groupby('placa_encoded').cumcount():

    df.loc[:, 'n'] = df.groupby('placa_encoded').cumcount()
    print(df)
           codcet  placa_encoded        date  time_seconds  velocidade  n
     0  490191222          431.0  2021-03-11       70079.0          51  0
     1  490221211          431.0  2021-03-11       72219.0          55  1
     2  490361213          562.0  2021-03-11       28559.0          24  0
     3  490361232          562.0  2021-03-11       29102.0          29  1
     4  490361221          562.0  2021-03-11       30183.0          33  2

Then, you can set the multi-index of df to ['placa_encoded', 'n'] if you want:

    df = df.set_index(['placa_encoded', 'index'])
    print(df)
                         codcet        date  time_seconds  velocidade
     placa_encoded n                                                 
     431.0         0  490191222  2021-03-11       70079.0          51
                   1  490221211  2021-03-11       72219.0          55
     562.0         0  490361213  2021-03-11       28559.0          24
                   1  490361232  2021-03-11       29102.0          29
                   2  490361221  2021-03-11       30183.0          33

Collectives™ on Stack Overflow

How to convert GroupBy object in pandas to multiindexed dataframe

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related