Get dates where category change happened for each user in dataframe

user_10970202 0
Viewed 82

I am using pandas==1.2.1

MRE:

x = pd.DataFrame({"date":["20201211", "20201211", "20201212", "20201222", "20201222","20201223",
                          "20201211", "20201211", "20201212", "20201222", "20201222"],
                  "userid":["A", "A", "A", "A", "A", "A","B", "B", "B", "B", "B"],
                  "category":[1,1,2,2,2,1, 33, 33, 33, 34, 34]})

which looks like this:

    date    userid  category
0   20201211    A   1
1   20201211    A   1
2   20201212    A   2
3   20201222    A   2
4   20201222    A   2
5   20201223    A   1
6   20201211    B   33
7   20201211    B   33
8   20201212    B   33
9   20201222    B   34
10  20201222    B   34

What I want to do is get dates for each user when their category changed

so desired dataframe should look like this:

user         cat_changed             changed_cat
  A      [20201212, 20201223]         [2, 1]
  B          [20201222]                [34]

I've tried grouping by userid, cate, date however stuck from there...

pandas python
2 Answers

You could first get the transition points using diff() on the category, then index those transitions and aggregate as list:

>>> transitions = x.groupby('userid').category.diff().fillna(0).ne(0)
>>> x[transitions].groupby('userid').agg(list)

                          date  category
userid
     A    [20201212, 20201223]    [2, 1]
     B              [20201222]      [34]
user_131383640

You can try with df.shift considering both userid and category , then create a grouper column to group:

u = x[['userid','category']]
c = u.ne(u.shift())
out = (x.groupby(['userid',c.any(1).mask(c.all(1)).replace(0,np.nan)])
       .agg(list).droplevel(1))

print(out)

                        date category
userid                               
A       [20201212, 20201223]   [2, 1]
B                 [20201222]     [34]
user_98406370
Related