0

I'm trying to automate billing for my boss. I have to choose the highest quantity for an invoice date and client, then print that quantity in a separate column and a 0 (or blank) for the second row associated with that client. I'm trying to recreate this example:

Billing Snippet

I'm having trouble using Pandas to do this. I used a pivot table to get the max quantity for each client, then merged that data with the original to get a "max" column. That looks like this:

Dataframe snippet

My plan is to use indexes to essentially say "if the Qty is not equal to Max, then change the value to 0"

Here's my code, but I get the error "A value is trying to be set on a copy of a slice from a DataFrame" :

    ad2[ad2['Qty'] != ad2['max']]['Qtrly Billing Count']=0

Any advice on how to tackle this?

Update: Tried turning off the setting that gives me the index error, but the column I want to update isn't changing. Help!

Angelina Carranza
  • 31
  • 3
  • Possible duplicate of [How to deal with SettingWithCopyWarning in Pandas?](https://stackoverflow.com/questions/20625582/how-to-deal-with-settingwithcopywarning-in-pandas) – Maria Jul 25 '18 at 18:53
  • Even when I change that setting, the column I want to change doesn't change. Still stuck – Angelina Carranza Jul 25 '18 at 18:57
  • In my answer you can see the Qtrly column changes, is that what you were going for? – Maria Jul 25 '18 at 19:39
  • Yes, that's what I was going for, and that's what I was having trouble with. I tried using the approach you did before, but I fudged up where 'Qtrly' went. Thank you! – Angelina Carranza Jul 26 '18 at 22:51

1 Answers1

0

Recreating you df:

ad2 = pd.DataFrame({'Qty':[33, 47],'max':[47,47], 'Qtrly':[47,47] })
    Qtrly   Qty max
0   47  33  47
1   47  47  47

using loc:

ad2.loc[ad2['Qty'] != ad2['max'], 'Qtrly']=0

result:

Qtrly   Qty max
0   0   33  47
1   47  47  47
Maria
  • 159
  • 10