Shifting a pandas dataframe column by a variable value in another column

Question

I have a pandas dataframe that looks like this:

    
Date        Value   shift_col
2021-02-11  50.12       0
2021-02-12  72.30       0
2021-02-15  81.87       1
2021-02-16  90.12       2
2021-02-17  91.31       3
2021-02-18  81.23       4
2021-02-19  73.45       6
2021-02-22  87.17       2

I want to shift the "Value" column by the value in the "shift_col" column. The shift column can be any integer, including zero and can be sequential or not.

I've tried lots of different approaches. Just using "df.Value.shift(df.shift_col) doesn't work (it gives me an error of ValueError: The truth value of a Series is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all().

I've tried a few options from these posts:

Variable shift in Pandas. - doesn't work for date based indexes
Pandas: Shift one column by other column value - seems to work but I really want to keep this pure pandas if possible with no other libraries.

This should be straightforward but it has me running around in circles chasing my own tail.

Any help would be appreciated.

Eric D. Brown D.Sc. · Accepted Answer · 2021-02-23 18:11:10Z

5

Doesn't seem like you're actually trying to shift the array. You simply want to offset each value by a different positional amount right? Does this accomplish what you want:

# Create integer positions of each row
idx_positions = np.arange(len(df))

# "shift" those integer positions by the amount in shift col
shifted_idx_positions = idx_positions - df["shift_col"]

# get the label based index from our DatetimeIndex
shifted_loc_index = df.index[shifted_idx_positions]

# Retrieve the "shifted" values and assign them as a new column
df["shifted_Value"] = df.loc[shifted_loc_index, "Value"].values

print(df)
            Value  shift_col  shifted_Value
Date                                       
2021-02-11  50.12          0          50.12
2021-02-12  72.30          0          72.30
2021-02-15  81.87          1          72.30
2021-02-16  90.12          2          72.30
2021-02-17  91.31          3          72.30
2021-02-18  81.23          4          72.30
2021-02-19  73.45          6          50.12
2021-02-22  87.17          2          81.23

There is a corner case where the shifted_idx_positions might be out of bounds depending on your data. But, let me know if this is on the right track of what you wanted before I go any deeper into this.

edited Feb 23, 2021 at 18:11

Eric D. Brown D.Sc.

1,9767 gold badges27 silver badges38 bronze badges

answered Feb 23, 2021 at 18:03

Cameron Riddell

13.8k14 silver badges21 bronze badges

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

3 Comments

Eric D. Brown D.Sc. Over a year ago

You are correct. I got so stuck on 'shift' that I didn't even look at other approaches. This does exactly what I need. Note: I think it should be df["shifted_Value"] = df.loc[shifted_loc_index, "Value"].values

Cameron Riddell Over a year ago

good catch on the code! I had originally named that variable something else and forgot to change it when I was done playing around.

Eric D. Brown D.Sc. Over a year ago

Thanks for the help. It always helps to have another set of eyes look at a problem.

Collectives™ on Stack Overflow

Shifting a pandas dataframe column by a variable value in another column

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related