Combining two dataframes

user_9015463 0
Viewed 64

I've tried merging two dataframes, but I can't seem to get it to work. Each time I merge, the rows where I expect values are all 0. Dataframe df1 already as some data in it, with some left blank. Dataframe df2 will populate those blank rows in df1 where column names match at each value in "TempBin" and each value in "Month" in df1.

EDIT: Both dataframes are in a for loop. df1 acts as my "storage", df2 changes for each location iteration. So if df2 contained the results for LocationZP, I would also want that data inserted in the matching df1 rows. If I use df1 = df1.append(df2) in the for loop, all of the rows from df2 keep inserting at the very end of df1 for each iteration.

df1:

Month  TempBin  LocationAA   LocationXA   LocationZP
 1      0       7            1            2
 1      1       98           0            89
 1      2       12           23           38
 1      3       3            14           17
 1      4       7            9            14
 1      5       1            8            99
 13     0       0            0            0
 13     1       0            0            0
 13     2       0            0            0
 13     3       0            0            0
 13     4       0            0            0
 13     5       0            0            0

df2:

Month  TempBin  LocationAA
 13     0       11
 13     1       22
 13     2       33
 13     3       44
 13     4       55
 13     5       66

desired output in df1:

Month  TempBin  LocationAA   LocationXA   LocationZP
 1      0       7            1            2
 1      1       98           0            89
 1      2       12           23           38
 1      3       3            14           17
 1      4       7            9            14
 1      5       1            8            99
 13     0       11           0            0
 13     1       22           0            0
 13     2       33           0            0
 13     3       44           0            0
 13     4       55           0            0
 13     5       66           0            0

import pandas as pd

df1 = pd.DataFrame({'Month': [1]*6 + [13]*6,
                   'TempBin': [0,1,2,3,4,5]*2,
                   'LocationAA': [7,98,12,3,7,1,0,0,0,0,0,0],
                   'LocationXA': [1,0,23,14,9,8,0,0,0,0,0,0],
                   'LocationZP': [2,89,38,17,14,99,0,0,0,0,0,0]}
                   )

df2 = pd.DataFrame({'Month': [13]*6,
                   'TempBin': [0,1,2,3,4,5],
                   'LocationAA': [11,22,33,44,55,66]}
                   )

df1 = pd.merge(df1, df2, on=["Month","TempBin","LocationAA"], how="left")

result:

Month  TempBin  LocationAA  LocationXA  LocationZP
1      0        7.0         1.0         2.0
1      1        98.0        0.0         89.0
1      2        12.0        23.0        38.0
1      3        3.0         14.0        17.0
1      4        7.0         9.0         14.0
1      5        1.0         8.0         99.0
13     0        NaN         NaN         NaN
13     1        NaN         NaN         NaN
13     2        NaN         NaN         NaN
13     3        NaN         NaN         NaN
13     4        NaN         NaN         NaN
13     5        NaN         NaN         NaN
pandas dataframe python
3 Answers

Here's some code that worked for me:

# Merge two df into one dataframe on the columns "TempBin" and "Month" filling nan values with 0.
import pandas as pd

df1 = pd.DataFrame({'Month': [1]*6 + [13]*6,
                   'TempBin': [0,1,2,3,4,5]*2,
                   'LocationAA': [7,98,12,3,7,1,0,0,0,0,0,0],
                   'LocationXA': [1,0,23,14,9,8,0,0,0,0,0,0],
                   'LocationZP': [2,89,38,17,14,99,0,0,0,0,0,0]}
                   )

df2 = pd.DataFrame({'Month': [13]*6,
                   'TempBin': [0,1,2,3,4,5],
                   'LocationAA': [11,22,33,44,55,66]})

df_merge = pd.merge(df1, df2, how='left', 
            left_on=['TempBin', 'Month'], 
            right_on=['TempBin', 'Month'])

df_merge.fillna(0, inplace=True)

# add column LocationAA and fill it with the not null value from column LocationAA_x and LocationAA_y
df_merge['LocationAA'] = df_merge.apply(lambda x: x['LocationAA_x'] if pd.isnull(x['LocationAA_y']) else x['LocationAA_y'], axis=1)

# remove column LocationAA_x and LocationAA_y
df_merge.drop(['LocationAA_x', 'LocationAA_y'], axis=1, inplace=True)

print(df_merge)

Output:

    Month  TempBin  LocationXA  LocationZP  LocationAA
0       1        0         1.0         2.0         0.0
1       1        1         0.0        89.0         0.0
2       1        2        23.0        38.0         0.0
3       1        3        14.0        17.0         0.0
4       1        4         9.0        14.0         0.0
5       1        5         8.0        99.0         0.0
6      13        0         0.0         0.0        11.0
7      13        1         0.0         0.0        22.0
8      13        2         0.0         0.0        33.0
9      13        3         0.0         0.0        44.0
10     13        4         0.0         0.0        55.0
11     13        5         0.0         0.0        66.0

Let me know if there's something you don't understand in the comments :)

PS: Sorry for the extra comments. But I left them there for some more explanations.

user_105007570

You need to use append to get the desired output:

df1 = df1.append(df2)

and if you want to replace the Nulls to zeros add:

df1 = df1.fillna(0)
user_108363090

Here is another way using combine_first()

i = ['Month','TempBin']
df2.set_index(i).combine_first(df1.set_index(i)).reset_index()
user_138021150
Related