Pandas: How to add a grouping variable based upon another column?

April 21, 2022

I have a dataframe with some id’s and some dates. I want to be able to group the id’s by their change in date to create a generalized "grouping_variable". In r I would do it like this:

df <- tibble(id = c(rep("1", 4), rep("2", 4), rep("3", 4)),
             dates = as_date(c('2022-02-07', '2022-02-07', '2022-02-08', '2022-02-08',
                         '2022-02-09', '2022-02-09', '2022-02-10', '2022-02-10', 
                         '2022-02-11', '2022-02-11', '2022-02-11', '2022-02-11')))

df <- df %>% group_by(id) %>% mutate(grouping_var = match(dates, unique(dates)))

basically, this code groups by the id, and then within the groups, each unique date is assigned a value, and then value is then joined with the actual date, which results in a column with these values: 1 1 2 2 1 1 2 2 1 1 1 1

In Python/ pandas I can’t find an equivalent to the match function. Does anyone know how to do that?

Here is some sample data in Python:

d = {'user' : ["1", "1", "1", "1", "2", "2", "2", "2", "3", "3", "3", "3"],
    'dates' : ['2022-02-07', '2022-02-07', '2022-02-08', '2022-02-08',
                         '2022-02-09', '2022-02-09', '2022-02-10', '2022-02-10', 
                         '2022-02-11', '2022-02-11', '2022-02-11', '2022-02-11'],
    'hoped_for_output' : [1, 1, 2, 2, 1, 1, 2, 2, 1, 1, 1, 1]}

example_df = pd.DataFrame(data = d)

Many thanks!

>Solution :

We may use factorize after grouping by ‘user’

d['hoped_for_output'] = d.groupby(['user'])['dates'].transform(lambda x: pd.factorize(x)[0]) + 1

-output

d
 user   dates   hoped_for_output
0   1   2022-02-07  1
1   1   2022-02-07  1
2   1   2022-02-08  2
3   1   2022-02-08  2
4   2   2022-02-09  1
5   2   2022-02-09  1
6   2   2022-02-10  2
7   2   2022-02-10  2
8   3   2022-02-11  1
9   3   2022-02-11  1
10  3   2022-02-11  1
11  3   2022-02-11  1