Groupby two columns and comparison of rows of one column

user_7931463 0
Viewed 89

I am working with groupby but i dont want to lose other columns which are not included in groupby such as i have a df:

id     date     name    item    price    unit    store
1    1/1/2020   abc    apples    200    Fruits   BigB
1    1/2/2020   abc    apples    100    Fruits   BigB
1    1/3/2020   abc    apples    250    Fruits   BigB
1    1/1/2020   abc    mangoes   350    Fruits   BigB
1    1/2/2020   abc    mangoes   150    Fruits   BigB
1    1/3/2020   abc    mangoes   50     Fruits   BigB
2    1/1/2020   xyz    apples    50     Fruits   BigB
2    1/2/2020   xyz    apples    50     Fruits   BigB

I want to create two columns of flag and start on basis of id and name and if the value of price is greater than its previous row then flag is 1 or else its 0.The data must be based on id,name and items.start column is based on flag.Initially start value will be its price value for the first row.If flag is 0 the start has the previous value and when flag is changed to 1 the start also changes to its corresponding price value The output will be:

id     date     name    item    price    unit    store  Flag      start
1    1/1/2020   abc    apples    200    Fruits   BigB   0          200
1    1/2/2020   abc    apples    100    Fruits   BigB   0          200
1    1/3/2020   abc    apples    250    Fruits   BigB   1          250
1    1/1/2020   abc    mangoes   350    Fruits   BigB   0          350 
1    1/2/2020   abc    mangoes   150    Fruits   BigB   0          350
1    1/3/2020   abc    mangoes   50     Fruits   BigB   0          350
2    1/1/2020   xyz    apples    50     Fruits   BigB   0          50
2    1/2/2020   xyz    apples    50     Fruits   BigB   0          50

Groupby is done by id,name and item column.Thanks in advance.The df is sorted by id,name,item and date.

pandas-groupby pandas dataframe python
1 Answers

Approach

m = df[['id','name','item']].duplicated()

df['flag'] = df.eval('price > price.shift() and @m').astype(int)
df['start'] = df['price'].where(~m | df['flag']).ffill()

Explanation

Considering the columns id, name and item identify the duplicate rows in the dataframe in order to create a boolean mask m

>>> m

0    False
1     True
2     True
3    False
4     True
5     True
6    False
7     True
dtype: bool

Evaluate a boolean expression on the frame where we compare the adjacent rows in the price column and take the logical and with mask m in order to create flag column

>>> df['flag']

0    0
1    0
2    1
3    0
4    0
5    0
6    0
7    0
Name: flag, dtype: int64

Now mask the values in the price column which do not satisfy the condition ~m | df['flag'] and forward fill to propagate the values

>>> df['start']

0    200.0
1    200.0
2    250.0
3    350.0
4    350.0
5    350.0
6     50.0
7     50.0
Name: start, dtype: float64

>>> df

   id      date name     item  price    unit store  start  flag
0   1  1/1/2020  abc   apples    200  Fruits  BigB  200.0     0
1   1  1/2/2020  abc   apples    100  Fruits  BigB  200.0     0
2   1  1/3/2020  abc   apples    250  Fruits  BigB  250.0     1
3   1  1/1/2020  abc  mangoes    350  Fruits  BigB  350.0     0
4   1  1/2/2020  abc  mangoes    150  Fruits  BigB  350.0     0
5   1  1/3/2020  abc  mangoes     50  Fruits  BigB  350.0     0
6   2  1/1/2020  xyz   apples     50  Fruits  BigB   50.0     0
7   2  1/2/2020  xyz   apples     50  Fruits  BigB   50.0     0
user_128331660
Related