Shift values in pandas dataframe vertically by one row

user_15376262 0
Viewed 129

I have a pandas dataframe that looks like this

import pandas as pd
d = {'value': [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23], 
     'user': ['A', 'A', 'A', 'A', 'A', 'A', 'A', 'A', 'B', 'B', 'B', 'B', 'C', 'C', 'C', 'C', 'C', 'C', 'C', 'C', 'A', 'A', 'A'],
     'date': ['2010-03-02', '2010-03-02', '2010-03-02', '2010-03-02', '2010-03-02', '2010-03-02', '2010-03-02', '2010-03-02', '2010-03-02', '2010-03-02', '2010-03-02', '2010-03-02', '2010-03-02', '2010-03-02', '2010-03-02', '2010-03-02', '2010-03-02', '2010-03-02', '2010-03-02', '2010-03-02', '2010-03-03', '2010-03-03', '2010-03-03']}
df = pd.DataFrame(data=d)
df

value   user    date
1       A       2010-03-02
2       A       2010-03-02
3       A       2010-03-02
4       A       2010-03-02
5       A       2010-03-02
6       A       2010-03-02
7       A       2010-03-02
8       A       2010-03-02
9       B       2010-03-02
10      B       2010-03-02
11      B       2010-03-02
12      B       2010-03-02
13      C       2010-03-02
14      C       2010-03-02
15      C       2010-03-02
16      C       2010-03-02
17      C       2010-03-02
18      C       2010-03-02
19      C       2010-03-02
20      C       2010-03-02
21      A       2010-03-03 
22      A       2010-03-03
23      A       2010-03-03

I would like to create a new column called value2 to this dataframe, populate it with the same values from the value column, but with the rows shifted upwards by one row. The last row of each user and date group group should be removed too. In the example below you can see that e.g. value pairs 8-9, 12-13 and 20-21 are missing.

value   user    date        value2
1       A      2010-03-02   2
2       A      2010-03-02   3
3       A      2010-03-02   4
4       A      2010-03-02   5
5       A      2010-03-02   6
6       A      2010-03-02   7
7       A      2010-03-02   8
9       B      2010-03-02   10
10      B      2010-03-02   11
11      B      2010-03-02   12
13      C      2010-03-02   14
14      C      2010-03-02   15
15      C      2010-03-02   16
16      C      2010-03-02   17
17      C      2010-03-02   18
18      C      2010-03-02   19
19      C      2010-03-02   20
21      A      2010-03-02   22
22      A      2010-03-02   23

With the code below I am almost able to get to the desired result, except for the fact that it also generates the following row:

df['value2'] = df.groupby(['user', 'date'])['value'].shift(-1, fill_value=-1)
df = df[df['value2'].ne(-1)]
df

value    user   date         value2
8       A      2010-03-02   21

Also, when running this code on a larger dataset, it returns a ValueError: cannot reindex from a duplicate axis. Any ideas how to fix both issues?

pandas dataframe python
3 Answers

You can try the below , note that your output has A repeated later hoeever your input(code) doesnot, hence it might create confusion.

out = (df.assign(next_node=
      df.groupby([df['user'].ne(df['user'].shift()).cumsum(),'date'])['node'].shift(-1))
     .dropna(subset=['next_node']))

print(out.reset_index(drop=True))

    node user        date  next_node
0      1    A  2010-03-02        2.0
1      2    A  2010-03-02        3.0
2      3    A  2010-03-02        4.0
3      4    A  2010-03-02        5.0
4      5    A  2010-03-02        6.0
5      6    A  2010-03-02        7.0
6      7    A  2010-03-02        8.0
7      9    B  2010-03-02       10.0
8     10    B  2010-03-02       11.0
9     11    B  2010-03-02       12.0
10    13    C  2010-03-02       14.0
11    14    C  2010-03-02       15.0
12    15    C  2010-03-02       16.0
13    16    C  2010-03-02       17.0
14    17    C  2010-03-02       18.0
15    18    C  2010-03-02       19.0
16    19    C  2010-03-02       20.0
17    21    A  2010-03-02       22.0
18    22    A  2010-03-02       23.0
user_98406370

Something like this should be the easiest way:

df['next_node'] = df['node'] + 1
df = df.loc[df['user'] == df['user'].shift(-1)]

However, if the df['node'] values are not consecutive integers as in the example then you would have to do it this way:

df['next_node'] = df['node'].shift(-1)
df = df.loc[df['user'] == df['user'].shift(-1)]
df = df.astype({'next_node': int})

The reason for the extra step is that the shift method converts the type of the series from int to float because it has to add a np.nan at the end which is a float value.

user_16095140

I think your mental model is misleading you. Instead of thinking of this as "shifting" rows, think of it as calculating a value based on the values in the row below.

user_14405650
Related