How to shift row values with condition in a Pandas dataFrame

Question

How can I shift dataFrame value with key matching? For example, I have a dataFrame with 'date', 'id' and 'num0' columns. When I shift this dataFrame, I want to consider 'date' and 'id' value.

By this code, I get 'current result'. However I want to set null value to row3's shifted value, because there is no record for ID='A' and date='2015-10-05'.

df_temp1 = pd.DataFrame({'id' : ['A', 'A', 'A', 'A', 'B', 'B', 'B', 'B'],
'date' : ['2015-10-1', '2015-10-2', '2015-10-3', '2015-10-4','2015-10-1', '2015-10-2', '2015-10-3', '2015-10-4'],
'num0' : ['1', '2', '3', '4', '1', '2', '3', '4']})

df_temp1['num0_shifted'] = df_temp1['num0'].shift(-1)
df_temp1.head(10)

Current result:

    date    id  num0    num0_shifted
0   2015-10-1   A   1   2
1   2015-10-2   A   2   3
2   2015-10-3   A   3   4
3   2015-10-4   A   4   1
4   2015-10-1   B   1   2
5   2015-10-2   B   2   3
6   2015-10-3   B   3   4
7   2015-10-4   B   4   NaN

Result I want to get:

    date    id  num0    num0_shifted
0   2015-10-1   A   1   2
1   2015-10-2   A   2   3
2   2015-10-3   A   3   4
3   2015-10-4   A   4   NaN
4   2015-10-1   B   1   2
5   2015-10-2   B   2   3
6   2015-10-3   B   3   4
7   2015-10-4   B   4   NaN

jezrael · Accepted Answer · 2017-10-25 13:37:15Z

5

Use DataFrameGroupBy.shift:

df_temp1['num0_shifted'] = df_temp1.groupby('id')['num0'].shift(-1)

print (df_temp1)
        date id num0 num0_shifted
0  2015-10-1  A    1            2
1  2015-10-2  A    2            3
2  2015-10-3  A    3            4
3  2015-10-4  A    4          NaN
4  2015-10-1  B    1            2
5  2015-10-2  B    2            3
6  2015-10-3  B    3            4
7  2015-10-4  B    4          NaN

answered Oct 25, 2017 at 13:37

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to shift row values with condition in a Pandas dataFrame

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related