Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to add "order within group" column in pandas?

Tags:

python

pandas

Take the following dataframe:

import pandas as pd
df = pd.DataFrame({'group_name': ['A','A','A','B','B','B'],
                   'timestamp': [4,6,1000,5,8,100],
                   'condition': [True,True,False,True,False,True]})

I want to add two columns:

  1. The row's order within its group
  2. rolling sum of the condition column within each group

I know I can do it with a custom apply, but I'm wondering if anyone has any fun ideas? (Also this is slow when there are many groups.) Here's one solution:

def range_within_group(input_df):
    df_to_return = input_df.copy()
    df_to_return = df_to_return.sort('timestamp')
    df_to_return['order_within_group'] = range(len(df_to_return))
    df_to_return['rolling_sum_of_condition'] = df_to_return.condition.cumsum()
    return df_to_return

df.groupby('group_name').apply(range_within_group).reset_index(drop=True)
like image 517
exp1orer Avatar asked Jun 09 '15 23:06

exp1orer


People also ask

How do I set a column order in pandas?

Use double brackets to reorder columns in a DataFrame Use the syntax DataFrame[["column1", "column2", "column3"]] with the column names in the desired order to reorder the columns.

Does pandas Groupby keep order?

Groupby preserves the order of rows within each group. Thus, it is clear the "Groupby" does preserve the order of rows within each group.

How do I sort by group in pandas?

To group Pandas dataframe, we use groupby(). To sort grouped dataframe in ascending or descending order, use sort_values(). The size() method is used to get the dataframe size.


1 Answers

GroupBy.cumcount does:

Number each item in each group from 0 to the length of that group - 1.

so simply:

>>> gr = df.sort('timestamp').groupby('group_name')
>>> df['order_within_group'] = gr.cumcount()
>>> df['rolling_sum_of_condition'] = gr['condition'].cumsum()
like image 180
behzad.nouri Avatar answered Oct 13 '22 22:10

behzad.nouri