CODEWITHSUNDEEP
pythonpandasdataframe
user96564
user96564

Reputation: 1607

How do I stop Shifting column in Pandas if index is different

I have data like this.

enter image description here .

I want to use pandas shift and subtract values between a column. This is the code which I am using. 

df['Difference'] = (df['A'] - df['A'].shift(-1))

This is the output I got ( as expected).

enter image description here

How do I prevent pandas from subtracting between columns if the index ( Id) is different. I want to subtract only if the index is same. My desire output is something like this. Using of df.shift(-1, axis = 0) didn't solve either. enter image description here

Any Suggestions?

Upvotes: 1

Views: 570

Answers (2)

Aaron Brock
Aaron Brock

Reputation: 4536

You could do this quick & dirty with np.where

import pandas as pd
import numpy as np

# Create Example Data
df = pd.DataFrame({
    'Id':[1, 1, 1, 2, 2, 2],
    'A': [6, 4, 11, 7, 9, 12]
})

# Where
df['Difference'] = np.where(
    # The Id's are the same
    df['Id'] == df['Id'].shift(-1), 
    # Take the difference
    df['A'] - df['A'].shift(-1), 
    # Else, np.NaN
    np.NaN
)

Output:

    A  Id  Difference
0   6   1         2.0
1   4   1        -7.0
2  11   1         NaN
3   7   2        -2.0
4   9   2        -3.0
5  12   2         NaN

Upvotes: 3

llllllllll
llllllllll

Reputation: 16404

You can first groupby 'id':

In [156]: df.assign(
     ...:   new_col=df.groupby('id').diff(-1)
     ...: )
Out[156]: 
    A  id  new_col
0   6   1      2.0
1   4   1     -7.0
2  11   1      NaN
3   7   2     -2.0
4   9   2     -4.0
5  13   2      NaN

Upvotes: 6

Related Questions