CODEWITHSUNDEEP
pythonpython-3.xpandasdataframe
user3848207
user3848207

Reputation: 4917

Assign current row value to previous row of panda dataframe after comparison

I have the panda dataframe in python below.

full_name  serial  Date_YMD        prc1     prc2    volume
 bottle_a  AX80    20200922    12874.50  12927.75   61023.0 
 bottle_a  AX80    20200923    12878.50  12926.75   61023.0
 bottle_a  AX80    20200924    12872.50  12928.75   61023.0
 bottle_a  AX80    20200925    12885.50  12984.25   62295.0
 bottle_a  AX80    20200926    12880.00  13000.00   14224.0

First, I want to detect which row falls on a Saturday based on column Date_YMD. Then, find out the volume value on this Saturday and assign this value to the previous row's volume if the previous row value is larger than current row value.

The final panda frame will look like this for the table above;

full_name  serial  Date_YMD        prc1     prc2    volume
 bottle_a  AX80    20200922    12874.50  12927.75   61023.0 
 bottle_a  AX80    20200923    12878.50  12926.75   61023.0
 bottle_a  AX80    20200924    12872.50  12928.75   61023.0
 bottle_a  AX80    20200925    12885.50  12984.25   14224.0
 bottle_a  AX80    20200926    12880.00  13000.00   14224.0

Saturday volume can be obtained with the following code below;

df['Date_YMD'] = pd.to_datetime(df['Date_YMD'], format='%Y%m%d')
sat_volume = df.loc[df['Date_YMD'].dt.dayofweek.eq(5), 'volume']

The problem is how do I assign this sat_volume to the previous row's volume accordingly after comparing the values?

I am using python 3.8

Upvotes: 1

Views: 57

Answers (1)

Shubham Sharma
Shubham Sharma

Reputation: 71689

Let's create boolean masks m1 and m2 then use boolean indexing with these masks to fill the column volume where the condition specified by m1 and m2 is satisfied:

m1 = df['volume'] > df['volume'].shift(-1)
m2 = df['Date_YMD'].shift(-1).dt.weekday.eq(5)
df.loc[m1 & m2, 'volume'] = df['volume'].shift(-1)

  full_name serial   Date_YMD     prc1      prc2   volume
0  bottle_a   AX80 2020-09-22  12874.5  12927.75  61023.0
1  bottle_a   AX80 2020-09-23  12878.5  12926.75  61023.0
2  bottle_a   AX80 2020-09-24  12872.5  12928.75  61023.0
3  bottle_a   AX80 2020-09-25  12885.5  12984.25  14224.0
4  bottle_a   AX80 2020-09-26  12880.0  13000.00  14224.0

Upvotes: 2

Related Questions