Merging dataframes on two columns alternative solution

user_10500757 0
Viewed 37

I have been trying to find an alternative (possibly more elegant) solution for the following code but without any luck. Here is my code:

import os
import pandas as pd

os.chdir(os.getcwd())

df1 = pd.DataFrame({'Month': [1]*6 + [13]*6,
                   'Temp': [0, 1, 2, 3, 4, 5]*2,
                    'Place': [12, 53, 6, 11, 9, 10, 0, 0, 0, 0, 0, 0],
                    'Place2': [1, 0, 23, 14, 9, 8, 0, 0, 0, 0, 0, 0],
                    'Place3': [2, 64, 24, 66, 14, 21, 0, 0, 0, 0, 0, 0]}
                   )

df2 = pd.DataFrame({'Month': [13] * 6,
                   'Temp': [0, 1, 2, 3, 4, 5],
                    'Place': [1, 22, 333, 444, 55, 6]})

# Here it creates new columns "Place_y" and "Place_x".
# I want to avoid this if possible.
df_merge = pd.merge(df1, df2, how='left',
                  left_on=['Temp', 'Month'],
                  right_on=['Temp', 'Month'])

df_merge.fillna(0, inplace=True)

add_not_nan = lambda x: x['Place_x'] if pd.isnull(x['Place_y']) else x['Place_y']

df_merge['Place'] = df_merge.apply(add_not_nan, axis=1)

df_merge.drop(['Place_x', 'Place_y'], axis=1, inplace=True)

print(df_merge)

What I am trying to accomplish is to merge the two dataframes based on the "Month" and "Temp" columns, while keeping 0s for missing values. I would like to know if there is any way to merge the dataframes without creating the _x and _y columns (basically, a way to skip the creation and deletion of those columns).

Inputs:

  • First dataframe
    Month  Temp  Place  Place2  Place3
0       1     0     12       1       2
1       1     1     53       0      64
2       1     2      6      23      24
3       1     3     11      14      66
4       1     4      9       9      14
5       1     5     10       8      21
6      13     0      0       0       0
7      13     1      0       0       0
8      13     2      0       0       0
9      13     3      0       0       0
10     13     4      0       0       0
11     13     5      0       0       0
  • Second dataframe
   Month  Temp  Place
0     13     0      1
1     13     1     22
2     13     2    333
3     13     3    444
4     13     4     55
5     13     5      6

Outputs:

  • After merge
    Month  Temp  Place_x  Place2  Place3  Place_y
0       1     0       12       1       2      NaN
1       1     1       53       0      64      NaN
2       1     2        6      23      24      NaN
3       1     3       11      14      66      NaN
4       1     4        9       9      14      NaN
5       1     5       10       8      21      NaN
6      13     0        0       0       0      1.0
7      13     1        0       0       0     22.0
8      13     2        0       0       0    333.0
9      13     3        0       0       0    444.0
10     13     4        0       0       0     55.0
11     13     5        0       0       0      6.0
  • Final (desired)
    Month  Temp  Place2  Place3  Place
0       1     0       1       2    0.0
1       1     1       0      64    0.0
2       1     2      23      24    0.0
3       1     3      14      66    0.0
4       1     4       9      14    0.0
5       1     5       8      21    0.0
6      13     0       0       0    1.0
7      13     1       0       0   22.0
8      13     2       0       0  333.0
9      13     3       0       0  444.0
10     13     4       0       0   55.0
11     13     5       0       0    6.0
pandas dataframe python
2 Answers

It seems you don't need Place column from df1, you can just drop it before merging:

(df1.drop('Place', axis=1)
    .merge(df2, how='left', on=['Temp', 'Month'])
    .fillna({'Place': 0}))

#    Month  Temp  Place2  Place3  Place
#0       1     0       1       2    0.0
#1       1     1       0      64    0.0
#2       1     2      23      24    0.0
#3       1     3      14      66    0.0
#4       1     4       9      14    0.0
#5       1     5       8      21    0.0
#6      13     0       0       0    1.0
#7      13     1       0       0   22.0
#8      13     2       0       0  333.0
#9      13     3       0       0  444.0
#10     13     4       0       0   55.0
#11     13     5       0       0    6.0
user_49834500

If you don't know how many such columns are there, and if you always want to include the column from second dataframe for such overlapping column names which you are not using as key column, then you can mask those variables using suffix parameter of pd.merge then filter out the columns taking the masking characters using pandas.DataFrame.filter:

df1.merge(df2,
          how='left', 
          left_on=['Temp', 'Month'],
          right_on=['Temp', 'Month'],
          suffixes=('##@', '')).fillna(0).filter(regex='.*(?<!##@)$')

OUTPUT:

    Month  Temp  Place2  Place3  Place
0       1     0       1       2    0.0
1       1     1       0      64    0.0
2       1     2      23      24    0.0
3       1     3      14      66    0.0
4       1     4       9      14    0.0
5       1     5       8      21    0.0
6      13     0       0       0    1.0
7      13     1       0       0   22.0
8      13     2       0       0  333.0
9      13     3       0       0  444.0
10     13     4       0       0   55.0
11     13     5       0       0    6.0

Apparently, you can also filter out the columns at beginning before merger, by checking the existence of columns from second dataframe in the first dataframe:

cols=[col for col in df1.columns if col in ('Temp', 'Month') or col not in df2.columns ]
df1[cols].merge(df2, how='left', 
          left_on=['Temp', 'Month'],
          right_on=['Temp', 'Month']).fillna(0)

    Month  Temp  Place2  Place3  Place
0       1     0       1       2    0.0
1       1     1       0      64    0.0
2       1     2      23      24    0.0
3       1     3      14      66    0.0
4       1     4       9      14    0.0
5       1     5       8      21    0.0
6      13     0       0       0    1.0
7      13     1       0       0   22.0
8      13     2       0       0  333.0
9      13     3       0       0  444.0
10     13     4       0       0   55.0
11     13     5       0       0    6.0
user_91363480
Related