CODEWITHSUNDEEP
pythonpandasdataframeconditional-statementsshift
Roman Lents
Roman Lents

Reputation: 131

Shift several parts inside one dataframe in pandas with condition

I have a dataframe as below. For simpicity, it has columns A, B, Correctly Shifted(what I want to get) and Incorrectly Shifted (what I have now). I want to shift values in column 'A' based on condition in column 'B'. If I will use pd.DataFrame.shift(1) then it shifts the whole column by 1. But I want to shift using a kind of moving window i.e. shift several parts inside one dataframe. For example, I want to shift all rows in column 'A' by 1 based on values in column 'B' i.e. shift all rows with condition 'aaa', then shift all rows with condition 'bbb' and all rows with condition 'ccc' etc. In column 'Correctly Shifted' is what I want and in column 'Incorrectly Shifted' is what I get if just use shift method in pandas. What is the best approach to solve it?

A B Correctly Shifted Incorrectly Shifted
100 aaa nan nan
110 aaa 100 100
120 aaa 110 110
100 bbb nan 120
110 bbb 100 100
120 bbb 110 110
100 ccc nan 120
110 ccc 100 100
120 ccc 110 110

Upvotes: 2

Views: 1224

Answers (2)

U13-Forward
U13-Forward

Reputation: 71590

Try using groupby with shift:

df['A'] = df.groupby('B')['A'].shift()
print(df)

Output:

       A    B
0    NaN  aaa
1  100.0  aaa
2  110.0  aaa
3    NaN  bbb
4  100.0  bbb
5  110.0  bbb
6    NaN  ccc
7  100.0  ccc
8  110.0  ccc

Upvotes: 1

Andrej Kesely
Andrej Kesely

Reputation: 195508

Try:

df["My Shift"] = df.groupby("B")["A"].shift()
print(df)

Prints:

     A    B  Correctly Shifted  Incorrectly Shifted  My Shift
0  100  aaa                NaN                  NaN       NaN
1  110  aaa              100.0                100.0     100.0
2  120  aaa              110.0                110.0     110.0
3  100  bbb                NaN                120.0       NaN
4  110  bbb              100.0                100.0     100.0
5  120  bbb              110.0                110.0     110.0
6  100  ccc                NaN                120.0       NaN
7  110  ccc              100.0                100.0     100.0
8  120  ccc              110.0                110.0     110.0

Upvotes: 1

Related Questions