How to count dates lower than other date group?

user_17378883 0
Viewed 130

I have a dataframe:

install       type   id       date
2021-11-01    main   a1        NA
2021-11-01    main   a2     2021-11-02
2021-11-01    main   a3     2021-11-02
2021-11-01    main   a3     2021-11-02
2021-11-02    down   b4     2021-11-05
2021-11-03    main   b7     2021-11-05
2021-11-04    main   a3     2021-11-05

I want to group this data by date and type and count unique id's with same type which have install lower than date. So desired result is:

    date       type      count    
2021-11-02     main       3
2021-11-05     down       1
2021-11-05     main       4

For 2021-11-02 main its 3 because there are 3 unique id's with same type and lower date (a1, a2, a3), for 2021-11-05 down its only b4, for 2021-11-05 main its a1, b7, a2, a3

How to do that? I know about groupby and nunique(), but I dint know how to write condition of install being lower than date.

P.S.

I need it to calculate retention value for each date and type group

pandas dataframe group-by python
1 Answers

It's not really a groupby, since you count some records more than once. I'm not sure how to avoid looping here, iterates over every pair of type/date and filters and takes nunique.

out = []
for index, group in df.groupby(['date','type']):
    d, t = index
    out.append({'date':d, 'type':t, 'count':df.loc[(df['install']<d) & (df['type'].eq(t))]['id'].nunique()})
pd.DataFrame(out)

         date  type  count
0 2021-11-02  main      3
1 2021-11-05  down      1
2 2021-11-05  main      4
user_47183500
Related