CODEWITHSUNDEEP
pythonpandasisnullpandas-loc
Darko86
Darko86

Reputation: 65

Pandas How to filter on Null values and zero values in the same column

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?

Upvotes: 2

Views: 6192

Answers (2)

Kavin Dsouza
Kavin Dsouza

Reputation: 979

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'

Upvotes: 3

lokesh
lokesh

Reputation: 123

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.

Upvotes: 0

Related Questions