Pandas left join with different dimensions

Question

I am still new to pandas and have the following problem. I want to merge two tables together but they have different dimensions.

I have 2 data frames.

Date:         Order Num.:      More Stuff:     Even more ... 
01.01.2021    123              asdf            ...
01.01.2021    124              fdsa            ...
02.01.2021    127              foo             ...
...           ...              ...             ...

The second one contains more information on the Order Numbers and have additional shipping numbers.

Order No.:    Shipping Num.:
123           111112
123           111113
123           111114
124           111171
124           ...

The result should be a left join on order number with the final table looking something like:

Date:         Order Num.:      Shipping Num.:      More Stuff:     Even more ... 
01.01.2021    123              111112              asdf            ...
01.01.2021    123              111113              asdf            ...
01.01.2021    123              111114              asdf            ...
02.01.2021    124              111171              fdsa            ...
...           ...              ...                 ...             ...

I tried using df_result = pd.merge(df1, df2, left_on='Order No.:', right_on='Order Num.:', how='left')and a few variations of that but the extra columns get lost in the process. How do I go about this?

What is your pandas version? Can you provide reproducible input constructors? I don't reproduce it — mozway, Apr 22 '22 at 08:41
@mozway I had a typo and I didn't notice it up until now. It works as intented now. — Squary94, Apr 22 '22 at 09:50

score 0 · Accepted Answer · answered Apr 22 '22 at 08:40

Aside the fact that you seem to have inverted the column names, I don't reproduce your issue:

pd.merge(df1, df2, left_on='Order Num.:', right_on='Order No.:', how='left')

output:

        Date:  Order Num.: More Stuff: Even more ...  Order No.:  Shipping Num.:
0  01.01.2021          123        asdf           ...       123.0        111112.0
1  01.01.2021          123        asdf           ...       123.0        111113.0
2  01.01.2021          123        asdf           ...       123.0        111114.0
3  01.01.2021          124        fdsa           ...       124.0        111171.0
4  02.01.2021          127         foo           ...         NaN             NaN

score 0 · Answer 2 · answered Apr 22 '22 at 09:14

0

Have you tried to merge it like:

df1.merge(df2, left_on='Order No.:', right_on='Order Num.:', how='left')

Note that it does not use pd.merge but uses the merge method of df1.

answered Apr 22 '22 at 09:14

dpendi

329
2
9

Pandas left join with different dimensions

2 Answers2