1

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:

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.
  • 1,896
  • 7
  • 25
  • 37

1 Answers1

3

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.

Eric D. Brown D.Sc.
  • 1,896
  • 7
  • 25
  • 37
Cameron Riddell
  • 10,942
  • 9
  • 19
  • 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` – Eric D. Brown D.Sc. Feb 23 '21 at 18:06
  • 1
    good catch on the code! I had originally named that variable something else and forgot to change it when I was done playing around. – Cameron Riddell Feb 23 '21 at 18:12
  • 1
    Thanks for the help. It always helps to have another set of eyes look at a problem. – Eric D. Brown D.Sc. Feb 23 '21 at 18:13