How to shift several rows in a pandas DataFrame?

Question

I have the following pandas Dataframe:

import pandas as pd
data = {'one' : pd.Series([1.], index=['a']), 'two' : pd.Series([1., 2.], index=['a', 'b']), 'three' : pd.Series([1., 2., 3., 4.], index=['a', 'b', 'c', 'd'])}
df = pd.DataFrame(data)
df = df[["one", "two", "three"]]


   one  two  three
a  1.0  1.0    1.0
b  NaN  2.0    2.0
c  NaN  NaN    3.0
d  NaN  NaN    4.0

I know how to shift elements by column upwards/downwards, e.g.

df.two = df.two.shift(-1)

   one  two  three
a  1.0  2.0    1.0
b  NaN  NaN    2.0
c  NaN  NaN    3.0
d  NaN  NaN    4.0

However, I would like to shift all elements in row a over two columns and all elements in row b over one column. The final data frame would look like this:

   one  two  three
a  NaN  NaN    1.0
b  NaN  NaN    2.0
c  NaN  NaN    3.0
d  NaN  NaN    4.0

How does one do this in pandas?

We don't need to transpose the DF, just choose the axis parameter in the shift. Look my answer! — marcio
– marcio, Commented Feb 5, 2021 at 12:01

Nickil Maveli · Accepted Answer · 2017-03-11 07:44:53Z

7

You can transpose the initial DF so that you have a way to access the row labels as column names inorder to perform the shift operation.

Shift the contents of the respective columns downward by those amounts and re-transpose it back to get the desired result.

df_t = df.T
df_t.assign(a=df_t['a'].shift(2), b=df_t['b'].shift(1)).T

edited Mar 11, 2017 at 7:44

answered Mar 11, 2017 at 7:38

Nickil Maveli

29.8k10 gold badges86 silver badges88 bronze badges

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

6 Comments

MaxU - stand with Ukraine Over a year ago

Nice solution! I think we can even improve it a bit - .shift() accepts axis parameter: df.assign(one=df[['one']].shift(2, axis=1), two=df[['two']].shift(1, axis=1))

Nickil Maveli Over a year ago

Yeah, that seems to be a good suggestion. But then, won't the amount with which it gets shifted have no influence on the outcome as we're merely selecting a single columned DF and performing shift across columns?

Nickil Maveli Over a year ago

Try for this case - df = pd.DataFrame(np.arange(12).reshape(4,3), list('abcd'), ['one','two','three']), you'll understand what I mean. Moreover, the OP wants just the first two rows to be modifed :-)

MaxU - stand with Ukraine Over a year ago

yep, i got it now. Thank you for the explanation! I can't upvote your answer the second time though ;-)

ShanZhengYang Over a year ago

This is a new question, but I'm curious: Instead of just a=df_t['a'].shift(2), b=df_t['b'].shift(1), what if there were multiple columns in need of shifting? Like columns a though z in need of shifting 2, columns 20 through 100 in need of shifting 5, etc.?

|

marcio · Accepted Answer · 2021-01-25 17:54:33Z

3

IMHO, this is a more pythonic way:

df.loc['a'] = df.loc['a'].shift(periods=2,axis=0)

Note in this case .loc returns a Series, so axis=0 is optional.

answered Jan 25, 2021 at 17:54

marcio

68612 silver badges21 bronze badges

Collectives™ on Stack Overflow

How to shift several rows in a pandas DataFrame?

2 Answers 2

6 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related