create durations for different items in a single dataframe

user_8941929 0
Viewed 45

How can I create durations for different items in a single dataframe

Example dataset

Item       Date           OnSale 
apple      2017-01-01     Yes
orange     2017-01-01     Yes
orange     2017-01-02     Yes
orange     2017-01-03     No
apple      2017-01-02     No
apple      2017-01-03     No
apple      2017-01-04     No
apple      2017-01-05     Yes

How do I calculate the number of days since an item has been on sale?

Desired output

Item       Date           OnSale      DaySinceSale
apple      2017-01-01     Yes         0
orange     2017-01-01     Yes         0
orange     2017-01-02     Yes         0
orange     2017-01-03     No          1
apple      2017-01-02     No          1
apple      2017-01-03     No          2
apple      2017-01-04     No          3
apple      2017-01-05     Yes         0
pandas lambda python
1 Answers

Try

df['DaySinceSale'] = df.groupby('Item')['OnSale'].apply(lambda x: (x == 'No') * (x == 'No').cumsum())

    Item    Date    OnSale  DaySinceSale
0   apple   2017-01-01  Yes 0
1   orange  2017-01-01  Yes 0
2   orange  2017-01-02  Yes 0
3   orange  2017-01-03  No  1
4   apple   2017-01-02  No  1
5   apple   2017-01-03  No  2
6   apple   2017-01-04  No  3
7   apple   2017-01-05  Yes 0

You can also use series.multiply()

df.groupby('Item')['OnSale'].apply(lambda x: (x == 'No').multiply((x == 'No').cumsum()))
user_62873080
Related