2

I have been trying to fix this syntax but with no luck. I would be grateful if someone could help me.

1    vat.loc[(vat['Sum of VAT'].isnull()) &
2            (vat['Sum of VAT'] == 0) &
3            (vat['Comment'] == 'Transactions 0DKK') &
4            (vat['Memo (Main)'] != '- None -'), 'Comment'] = 'Travel bill'
5    vat[vat["Comment"] == "Travel bill"]

The problem lies in the first and second lines. I get an empty data frame as output due to the filter for the null values and zero (0) values in the same column don't happen simultaneously. Is there a better way to combine line 1 and 2, so that I get the desired output. If I would remove the first or the second line I would get an output, but it would not be the desired one. I have tried to use the isin() method but it didn't work for me 100%. Otherwise, the whole code works perfectly but how do I concatenate the first and second lines?

Henry Ecker
  • 34,399
  • 18
  • 41
  • 57
Darko86
  • 65
  • 1
  • 4
  • Can you show your sample dataframe? – ashishmishra Apr 26 '20 at 19:18
  • Could you try this - `vat['Sum of VAT'].isin([np.nan, 0])` ?, not sure about this, haven't tested it. – tidakdiinginkan Apr 26 '20 at 19:19
  • Thanks @Cavin Dsouza I didn't consider numpy , yes it worked, do you think this is the optimal solution for this type of filter? – Darko86 Apr 26 '20 at 19:29
  • @Darko86 I just realized that your code is incorrect, since `Sum of VAT` cannot be null and 0 at the same time, it should have been an `|` instead of an `&`, just for those two conditions. The `isin` I told you to try operates similarly to an `|` – tidakdiinginkan Apr 26 '20 at 19:37

2 Answers2

3

The reason for an empty dataframe is indeed the first two conditions, the column Sum of VAT cannot be null and 0 at the same time, so the & condition should be replaced with an | as - 

vat.loc[((vat['Sum of VAT'].isnull()) | (vat['Sum of VAT'] == 0)) &
        (vat['Comment'] == 'Transactions 0DKK') &
        (vat['Memo (Main)'] != '- None -'), 'Comment'] = 'Travel bill'

OR use the isin as - 

vat.loc[(vat['Sum of VAT'].isin([np.nan, 0])) &
        (vat['Comment'] == 'Transactions 0DKK') &
        (vat['Memo (Main)'] != '- None -'), 'Comment'] = 'Travel bill'
tidakdiinginkan
  • 918
  • 9
  • 18
0

There are two approaches by which you can solve this issue:

a) You can replace zeros with NaN and then you can further filter on NULL values. So I mean to say, do something like

vat['Sum of VAT'] = vat['Sum of VAT'].replace(0, np.nan)
1 vat.loc[(vat['Sum of VAT'].isnull()) &
3            (vat['Comment'] == 'Transactions 0DKK') &
4            (vat['Memo (Main)'] != '- None -'), 'Comment'] = 'Travel bill'

b) Or, you can simple use isin method:

1 vat.loc[(vat['Sum of VAT'].isin(0, np.nan)) &
3            (vat['Comment'] == 'Transactions 0DKK') &
4            (vat['Memo (Main)'] != '- None -'), 'Comment'] = 'Travel bill'

By using any of the above two appraoches you can ignore L#2 from your code.

lokesh
  • 108
  • 3
  • 17