Im trying to merge two tables with all the same colums but one:
table 1:

Product Name product_Model 2022-04-19  
p1           m1            20$  
p2           m2            5$  

table 2:

Product Name product_Model 2022-04-20  
p1           m1            20$  
p2           m2            10$  
p3           m3            100$  

I wish to combine them so the final output will look like this:

Product Name product_Model 2022-04-19 2022-04-20    
p1           m1            20$        20$  
p2           m2            5$         10$  
p3           m3            null       100$  

Im using this code(after removing the model column):

table1.merge(table2, on='Product Name', how="outer")

This is the result:

Product Name product_Model 2022-04-19 2022-04-20  
p1           m1            20$        20$  
p2           m2            5$         10$  
p3           m3            100$    

notice how the 100$ moved to the '-19* column...
edit: im reading from and writing to a .csv file.

CodePudding user response：

You can pass more than one value in on. So you'd have:

left = pd.DataFrame(
    {
        "Name": ["P1", "P2", "P4"],
        "Model": ["M1", "M2", "M4"],
        "2022-04-19": ["20", "5", "8"]
    }
)
right = pd.DataFrame(
    {
        "Name": ["P1", "P2", "P3"],
        "Model": ["M1", "M2", "M3"],
        "2022-04-20": ["20", "5", "100"]
    }
)

result = pd.merge(left, right, on=["Name", "Model"], how="outer")
result = result.fillna('null')