0

I have 3 data frames. I need to enrich the data from df with the data columns from df2 and df3 so that df ends up with the columns 'Code', 'Quantity', 'Payment', 'Date', 'Name', 'Size', 'Product','product_id', 'Sector'.

The codes that are in df and not in df2 OR df3, need to receive "unknown" for the string columns and "0" for the numeric dtype columns

import pandas as pd

data = {'Code': [356, 177, 395, 879, 952, 999],
        'Quantity': [20, 21, 19, 18, 15, 10],
        'Payment': [173.78, 253.79, 158.99, 400, 500, 500],
        'Date': ['2022-06-01', '2022-09-01','2022-08-01','2022-07-03', '2022-06-09', '2022-06-09']
       }

df = pd.DataFrame(data)
df['Date']= pd.to_datetime(df['Date'])

data2 = {'Code': [356, 177, 395, 893, 697, 689, 687],
        'Name': ['John', 'Mary', 'Ann', 'Mike', 'Bill', 'Joana', 'Linda'],
        'Product': ['RRR', 'RRT', 'NGF', 'TRA', 'FRT', 'RTW', 'POU'],
        'product_id': [189, 188, 16, 36, 59, 75, 55],
        'Size': [1, 1, 3, 4, 5, 4, 7],
       }
df2 = pd.DataFrame(data2)

data3 = {'Code': [879, 356, 389, 395, 893, 697, 689, 978],
        'Name': ['Mark', 'John', 'Marry', 'Ann', 'Mike', 'Bill', 'Joana', 'James'],
        'Product': ['TTT', 'RRR', 'RRT', 'NGF', 'TRA', 'FRT', 'RTW', 'DTS'],
        'product_id': [988, 189, 188, 16, 36, 59, 75, 66],
        'Sector': ['rt' , 'dx', 'sx', 'da', 'sa','sd','ld', 'pc'], 
       } 
df3 = pd.DataFrame(data3)

I was using the following code to obtain the unknown codes by comparing with df2, but now i have to compare with df3 also and also add the data from the columns ['Name', 'Size', 'Product','product_id', 'Sector'].

common = df2.merge(df,on=['Code'])
new_codes = df[(~df['Code'].isin(common['Code']))]
tailsrockc
  • 1
  • 1
  • Your question needs a minimal reproducible example consisting of sample input, expected output, actual output, and only the relevant code necessary to reproduce the problem. See [How to make good reproducible pandas examples](https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples) for best practices related to Pandas questions. – itprorh66 Nov 11 '22 at 18:08

0 Answers0