Python: pick elements with different indexes and store them in a new Dataframe column in Pandas

Question

I have a DataFrame that looks like this:

What I want is to compute the following operation, described in a loop:

for ii in range(0,len(df['Avg'])):
    if ii==0:
        df['Return'][ii]=0
    else:
        df['Return'][ii] = (df['Avg'][ii])/(df['Avg'][ii-1])-1

I want to use 'present' and 'previous' element from 'Avg' column to make that operation, but I'm getting this error:

A value is trying to be set on a copy of a slice from a DataFrame

See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy df['Return'][ii]=0 C:/Users/julio/Desktop/Python Scripts/Euclid Capital Scripts/DukasCopy/duka_test:34: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame

See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy df['Return'][ii] = (df['Avg'][ii])/(df['Avg'][ii-1])-1

Is there another way to achieve what I want and store those values in the 'Return' column?

Brad Solomon · Accepted Answer · 2017-10-13T17:55:20.573

You can use .pct_change:

df.loc[:, 'Return'] = df['Avg'].pct_change()

Or, to start off with a leading zero instead of an NaN, chain .fillna:

df.loc[:, 'Return'] = df['Avg'].pct_change().fillna(0.)

As for the warning you're seeing, there's a lot of info out there about what's happening here already:

In your case, using df['Return'][ii] is what leads to this. You are using chained indexing where it would be safer to use .loc or .iloc.

Example:

df = pd.DataFrame({'Avg' : np.random.rand(10) + 1},
                  index=pd.date_range('2017-02', periods=10))
df.loc[:, 'Return'] = df['Avg'].pct_change().fillna(0.)
print(df)
                Avg   Return
2017-02-01  1.86389  0.00000
2017-02-02  1.19992 -0.35623
2017-02-03  1.08414 -0.09649
2017-02-04  1.17281  0.08179
2017-02-05  1.30422  0.11204
2017-02-06  1.59484  0.22284
2017-02-07  1.63399  0.02455
2017-02-08  1.99155  0.21883
2017-02-09  1.54733 -0.22305
2017-02-10  1.50584 -0.02682

Python: pick elements with different indexes and store them in a new Dataframe column in Pandas

1 Answers1