sschade sschade - 1 year ago 88
Python Question

Replace missing values that result from a pandas merge


|Invoice # |Date |Amount
|12 |12/15/2015 |$10
|13 |12/16/2015 |$11
|14 |12/17/2015 |$12


|Invoice # |Date |Amount
|12 |1/16/2016 |$10
|14 |1/17/2016 |$12

Merged = df1.merge(df2,how = left, on = Invoice #)

|Invoice # |Date |Amount
|12 |12/15/2015 |$10
|NaN |NaN |NaN
|14 |1/17/2016 |$12

What I would like to do is take Invoice 13 that returned a NaN value in the merge and place it in a list. Any ideas?

Answer Source

Your merged result is not showing what actually happens with a left merge?

Here's what I get when I try to reproduce what I think you're trying to do (I'm using pandas version 0.19.0):

merged = df1.merge(df2, how='left', on='Invoice #')

enter image description here

Then you can mask by the missing values and get the invoice number columns where the second amount is missing:

merged[merged['Amount_y'].isnull()]['Invoice #']

Or just create a column with the boolean flag:

merged['missing_from_df2'] = merged['Amount_y'].isnull()
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download