aggregate dataframe values up to each date per all dates

user_18079528 0
Viewed 40

suppose I have the following df:

df = pd.DataFrame({'date': ['2019-01-21', '2019-01-21', '2019-01-22', '2019-01-21', '2019-01-22', '2019-01-22'], 
                   'user_id': [1, 1, 1, 2, 2, 2],
                   'value': ['a', 'b', 'c', 'a', 'a', 'd']})

         date  user_id value
0  2019-01-21        1     a
1  2019-01-21        1     b
2  2019-01-22        1     c
3  2019-01-21        2     a
4  2019-01-22        2     a
5  2019-01-22        2     d

I would like add new column that represent the count of unique values per each user_id and date up to that date.

i.e: for each user and date, count history of unique values: user_id=1 has 2 unique values (a, b) at date 2019-01-21 and 3 unique values at date 2019-01-22 (a and b from previous dates and c from current date)

I would like to get the following df as result:

         date  user_id value  unique_values_count
0  2019-01-21        1     a                    1
1  2019-01-21        1     b                    2
2  2019-01-22        1     c                    3
3  2019-01-21        2     a                    1
4  2019-01-22        2     a                    1
5  2019-01-22        2     d                    2
pandas-groupby dataframe python
1 Answers

You can do it like this:

df['unique_values_count']=df.groupby('user_id')['value'].apply(lambda x: (~pd.Series(x).duplicated()).cumsum())

Result:

         date  user_id value  unique_values_count
0  2019-01-21        1     a                    1
1  2019-01-21        1     b                    2
2  2019-01-22        1     c                    3
3  2019-01-21        2     a                    1
4  2019-01-22        2     a                    1
5  2019-01-22        2     d                    2
user_113673050
Related