-1

first post / total Python novice so be patient with my slow understanding!

I have a dataframe containing a list of transactions by order of transaction date.

I've appended an additional new field/column called ["DB/CR"], that dependant on the presence of "-" in the ["Amount"] field populates 'Debit', else 'Credit' in the absence of "-".

Noting the transactions are in date order, I've included another new field/column called [Top x]. The output of which is I want to populate and incremental independent number (starting at 1) for both debits and credits on a segregated basis.

As such, I have created a simple loop with a associated 'if' / 'elif' (prob could use else as it's binary) statement that loops through the data sent row 0 to the last row in the df and using an if statement 1) "Debit" or 2) "Credit" increments the number for each independently by "Debit" 'i' integer, and "Credit" 'ii' integer.

The code works as expected in terms of output of the 'Top x'; however, I always receive a warning "A value is trying to be set on a copy of a slice from a DataFrame".

Trying to perfect my script, without any warnings I've been trying to understand what I'm doing incorrect but not getting it in terms of my use case scenario.

Appreciate if someone can kindly shed light on / propose how the code needs to be refactored to avoid receiving this error.

Code (the df source data is an imported csv):

#top x debits/credits
i = 0
ii = 0
for ind in df.index:
    if df["DB/CR"][ind] == "Debit":
        i = i+1
        df["Top x"][ind] = i
    elif df["DB/CR"][ind] == "Credit":
        ii = ii+1
        df["Top x"][ind] = ii

Interpreter

  df["Top x"][ind] = i
G:\Finances Backup\venv\Statementsv.03.py:173: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  df["Top x"][ind] = ii

Many thanks :)

LMG_Python_Novice
  • 3
  • 2
  • Have you tried searching the internet for the error message? I know for a fact there is a well-traveled SO post on this very matter. Have a look. – S3DEV Jul 02 '22 at 08:49
  • Does this help you ? https://stackoverflow.com/questions/20625582/how-to-deal-with-settingwithcopywarning-in-pandas – Shodai Thox Jul 02 '22 at 08:50
  • thank you @ShodaiThox - also pd.options.mode.chained_assignment = None # default='warn' suppressing the warnings works – LMG_Python_Novice Jul 02 '22 at 09:36
  • Does this answer your question? [How to deal with SettingWithCopyWarning in Pandas](https://stackoverflow.com/questions/20625582/how-to-deal-with-settingwithcopywarning-in-pandas). Did you follow the [link to the docs](https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy) written in the traceback? It explains precisely the problem. Btw Rene's answer is the solution to your problem, not Naveed's... – Rodalm Jul 02 '22 at 10:56
  • Setting `pd.options.mode.chained_assignment=None` is ignoring the problem, not solving it – Rodalm Jul 02 '22 at 11:01

2 Answers2

0

You should use df.loc["DB/CR", ind] = "Debit"

René
  • 4,594
  • 5
  • 23
  • 52
0

Use iterrows() to iterate over the DF. However, updating DF while iterating is not preferable

see documentation here

Refer to the documentation here Iterrows()

You should never modify something you are iterating over. This is not guaranteed to work in all cases. Depending on the data types, the iterator returns a copy and not a view, and writing to it will have no effect.

Naveed
  • 11,495
  • 2
  • 14
  • 21
  • thank you - I used a different approach in other instances of my code where I would store the index's in a list and then affect the df post interrogating the column values, e.g. s = df.index[df['Original Description'].str.contains("PFS", na=False)].tolist() for i in s: df.loc[i, ["Category"]] = "Fuel" – LMG_Python_Novice Jul 02 '22 at 09:15