.loc[] = value returns SettingWithCopyWarning in Pandas

Question

Problem

I get the following error message with my code. Supposedly, the issue is that I am slicing the dataframe first with .loc and then attempting to assign values to that slice. From my understanding, Pandas isn't 100% sure if I want to assign values to just the slice, or have it propagate all the way back up to the original df. I'm not sure how to fix this.

Error Message

C:\blp\BQuant\environments\bqnt-1.25.2\lib\site-packages\pandas\core\indexing.py:140: 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

self._setitem_with_indexer(indexer, value)

Full Code

import numpy as np
import pandas as pd
import datetime as dt
import time

csv1 = pd.read_csv('stock_price.csv', delimiter = ',')
df = pd.DataFrame(csv1)

df['delta'] = df.PX_LAST.pct_change()
df.loc[df.index[0], 'avg_gain'] = 0

for x in range(1,len(df.index)):
    if df["delta"].iloc[x] > 0:
        df["avg_gain"].iloc[x] = ((df["avg_gain"].iloc[x - 1] * 13) + df["delta"].iloc[x]) / 14
    else:
        df["avg_gain"].iloc[x] = ((df["avg_gain"].iloc[x - 1] * 13) + 0) / 14   

df

Input

Dates,PX_LAST
03/09/2018,157.512
04/09/2018,155.393
05/09/2018,154.069
06/09/2018,155.109
07/09/2018,156.301
10/09/2018,156.717
11/09/2018,157.19
12/09/2018,157.549
13/09/2018,159.157
14/09/2018,158.363
17/09/2018,158.968

Output

Dates,PX_LAST,delta,avg_gain
03/09/2018,157.512,NaN,0
04/09/2018,155.393,-0.013453,0
05/09/2018,154.069,-0.00852,0
06/09/2018,155.109,0.00675,0.000482
07/09/2018,156.301,0.007685,0.000997
10/09/2018,156.717,0.002662,0.001116
11/09/2018,157.19,0.003018,0.001251
12/09/2018,157.549,0.002284,0.001325
13/09/2018,159.157,0.010206,0.00196
14/09/2018,158.363,-0.004989,0.00182
17/09/2018,158.968,0.00382,0.001963

Line of Code that is the Issue

for x in range(1,len(df.index)):
    if df["delta"].iloc[x] > 0:
        df["avg_gain"].iloc[x] = ((df["avg_gain"].iloc[x - 1] * 13) + df["delta"].iloc[x]) / 14
    else:
        df["avg_gain"].iloc[x] = ((df["avg_gain"].iloc[x - 1] * 13) + 0) / 14

Solution

I tried to use .copy() but I still get the same error message

for x in range(1,len(df.index)):
    if df["delta"].iloc[x] > 0:
        df["avg_gain"].iloc[x] = ((df["avg_gain"].iloc[x - 1].copy() * 13) + df["delta"].iloc[x].copy()) / 14
    else:
        df["avg_gain"].iloc[x] = ((df["avg_gain"].iloc[x - 1].copy() * 13) + 0) / 14

Thanks

The df is create by the subset of other df , when you slice it , adding .copy() — BENY, Sep 18 '19 at 14:04
Because you assign `df['avg_gain'].iloc[x]`, see this part [Why does assignment fail when using chained indexing?](https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html]. — Quang Hoang, Sep 18 '19 at 14:07
I went through but I cant find a solution for this particular case — pythonlearner13, Sep 18 '19 at 14:16
I'm not sure exactly what you are trying to do here but I feel like this could benefit from using [df.shift()](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.shift.html) and also [df.where()](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.where.html) — Kevin Glasson, Sep 18 '19 at 14:28

Quang Hoang · Accepted Answer · 2019-09-18T15:49:58.313

1

The issue code can be replaced with

for x in range(1,len(df.index)):
    if df["delta"].iloc[x] > 0:
        df.iloc[x, -1] = ((df["avg_gain"].iloc[x - 1] * 13) + df["delta"].iloc[x]) / 14
    else:
        df.iloc[x,-1] = ((df["avg_gain"].iloc[x - 1].copy() * 13) + 0) / 14

this because you added avg_gain last, so you can use iloc[:,-1] to access that column.

Update using ewm:

arg = df["delta"].clip(lower=0)
arg.iloc[0] = 0

df['avg_gain'] = arg.ewm(alpha=1/14, adjust=False).mean()

Output:

0     0.000000
1     0.000000
2     0.000000
3     0.000482
4     0.000997
5     0.001116
6     0.001251
7     0.001325
8     0.001960
9     0.001820
10    0.001962
Name: delta, dtype: float64

edited Sep 18 '19 at 15:49

answered Sep 18 '19 at 14:09

Quang Hoang

146,074
10
56
74

Thank you for your answer. However, the output is just NaN when i use this code – pythonlearner13 Sep 18 '19 at 14:12
@pythonlearner13 see my edit. This is a quick dirty fix. There should be a better vectorized way. – Quang Hoang Sep 18 '19 at 14:17
Any chance you would be able to help me on this as well? https://stackoverflow.com/questions/57870648/how-to-create-a-column-using-a-function-based-of-previous-values-in-the-column-i/57870840?noredirect=1#comment102167091_57870840 – pythonlearner13 Sep 18 '19 at 14:25
isn't it the same problem? change `df["var"].iloc[x]` on the left of `=` to `df.iloc[x,-1]`? – Quang Hoang Sep 18 '19 at 14:27
Slightly different. I am trying to get rid of the loop because it is slow and df.iloc[x, -1] would still mean i need a loop – pythonlearner13 Sep 18 '19 at 14:31
That is so helpful. I have been trying for a long time to figure that out. Thanks a lot. – pythonlearner13 Sep 19 '19 at 12:29

.loc[] = value returns SettingWithCopyWarning in Pandas

1 Answers1