pandas shift does not work for subset of columns and rows

user_10465165 0
Viewed 565

I have the following data Frame:

df = pd.DataFrame({"a":[1,2,3,4,5], "b":[3,2,1,2,2], "c": [2,1,0,2,1]})

    a   b   c
0   1   3   2
1   2   2   1
2   3   1   0
3   4   2   2
4   5   2   1

and I want to shift columns a and b at indexes 0 to 2. I.e. my desired result is

    a   b   c
0  NaN NaN  2
1   1   3   1
2   2   2   0
3   4   1   2
4   5   2   1

If I do

df[["a", "b"]][0:3] = df[["a", "b"]][0:3].shift(1)

and look at df, it appears to not have changed.

However, if a select only the rows or the columns, it works:

Single Column, select subset of rows:

df["a"][0:3] = df["a"][0:3].shift(1)

Output:


     a  b   c
0   NaN 3   2
1   1.0 2   1
2   2.0 1   0
3   4.0 2   2
4   5.0 2   1

Likewise, if i select a list of columns, but all rows, it works as expected, too:

df[["a", "b"]] = df[["a", "b"]].shift(1)

output:

    a   b   c
0   NaN NaN 2
1   1.0 3.0 1
2   2.0 2.0 0
3   3.0 1.0 2
4   4.0 2.0 1

Why does df[["a", "b"]][0:3] = df[["a", "b"]][0:3].shift(1) not work as expected? am I missing something?

pandas dataframe python
1 Answers

Problem is there are double selecting - first columns and then rows, so updating copy. Check also evaluation order matters.

Possible solution with one selecting DataFrame.loc for index labels and columns names:

df.loc[0:2, ["a", "b"]] = df.loc[0:2, ["a", "b"]].shift(1)
print (df)
     a    b  c
0  NaN  NaN  2
1  1.0  3.0  1
2  2.0  2.0  0
3  4.0  2.0  2
4  5.0  2.0  1

If not default index and is necessary select first 2 rows:

df = pd.DataFrame({"a":[1,2,3,4,5], "b":[3,2,1,2,2], "c": [2,1,0,2,1]},
                   index=list('abcde'))

df.loc[df.index[0:2], ["a", "b"]] = df.loc[df.index[0:2], ["a", "b"]].shift(1)
print (df)
     a    b  c
a  NaN  NaN  2
b  1.0  3.0  1
c  3.0  1.0  0
d  4.0  2.0  2
e  5.0  2.0  1
user_29010020
Related