Error in variable shift in pandas

Question

In the example below, the first apply works. The second throws "TypeError: ("Cannot cast array data from dtype('float64') to dtype('int64') according to the rule 'safe'", u'occurred at index 0')"

df = pd.DataFrame({'lag':[ 3, 5, 3, 4, 2, 3, 2, 3, 4, 3, 2, 2, 2, 3],
                   'A':[10,20,30,40,20,30,40,10,20,30,15,60,20,15],
                   'B':[11,21,31,41,21,31,41,11,21,31,15,61,21,25]})
df['C'] = df.apply(lambda x: df['A'].shift(x['lag'])[x.name], axis=1)
print df
df['D'] = df.apply(lambda x: df['B'].shift(x['lag'])[x.name], axis=1)
print df

Please tell me why this happens and how to fix it. Thanks,

(Note: I do not have enough "points" to post a comment in Variable shift in Pandas)

piRSquared · Accepted Answer · 2017-03-13 21:18:50Z

2

This is actually a tricky thing going on. I'll try to be succinct.

When you are using apply with axis=1 you are iterating row by row. For each row, pandas handles it as a pd.Series. After your initial assignment, you put NaN values in the df When that row is accessed, the entire row gets interpreted as float

work around # 1
Ensure lag value is int

df['D'] = df.apply(lambda x: df['B'].shift(int(x['lag']))[x.name], axis=1)

work around # 2
Do assignments at same time

df = df.assign(
    C=df.apply(lambda x: df['A'].shift(x['lag'])[x.name], axis=1),
    D=df.apply(lambda x: df['B'].shift(int(x['lag']))[x.name], axis=1)
)

better solution
However, I'd use numpy to help with this

Those lags are just the current position values less the lag value

l = (np.arange(len(df)) - df.lag.values)

then

df['C'] = np.where(l >= 0, df.A.values[l], np.nan)
df['D'] = np.where(l >= 0, df.B.values[l], np.nan)

edited Mar 13, 2017 at 21:18

answered Mar 13, 2017 at 21:07

piRSquared

296k68 gold badges509 silver badges654 bronze badges

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

4 Comments

Bill Over a year ago

Thanks! Works like a champ!

piRSquared Over a year ago

@Bill I've got better in an update coming in a few minutes

Bill Over a year ago

That's crafty. I assume this works a lot faster since it operates "column-wise" instead of 'row-wise')?

piRSquared Over a year ago

@Bill I added that answer to the question you referenced. Go there and look at the time differences. The speed up comes from not iterating over every row and instead using a vectorized approach of slicing.

Collectives™ on Stack Overflow

Error in variable shift in pandas

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related