How update one dataframe's column by matching columns in two different dataframes in Pandas

user_10722752 0
Viewed 36

I have two dataframes. I need to generate report by matching columns in two dataframes and updating a column in the first dataframe:

Sample Data

input_file = pd.DataFrame({'Branch' : ['GGN','MDU','PDR','VLR','AMB'],
                         'Inflow' : [0, 0, 0, 0, 0]})
                            
month_inflow = pd.DataFrame({'Branch' : ['AMB','GGN','MDU','PDR','VLR'],
                           'Visits' : [124, 130, 150, 100, 112]})

input_file
    Branch  Inflow
0   GGN     0
1   MDU     0
2   PDR     0
3   VLR     0
4   AMB     0

month_inflow

    Branch  Visits
0   AMB     124
1   GGN     130
2   MDU     150
3   PDR     100
4   VLR     112

Expected Output:

input_file

  Branch Inflow
1    GGN    130
2    MDU    150
3    PDR    100
4    VLR    112
5    AMB    124

I tried using merge option, but I get the 'Inflow' column which is not required, I know I can drop it, but could someone let me know if there's a better way to get the desired output.

pd.merge(input_file, month_inflow, on = 'Branch')

    Branch  Inflow  Visits
0   GGN     0       130
1   MDU     0       150
2   PDR     0       100
3   VLR     0       112
4   AMB     0       124
pandas python-3.x
2 Answers

You can try

input_file.Inflow=input_file.Branch.map(month_inflow.set_index('Branch').Visits)
input_file
Out[145]: 
  Branch  Inflow
0    GGN     130
1    MDU     150
2    PDR     100
3    VLR     112
4    AMB     124
user_79645270

Merge on "Branch" column and then drop "Inflow" from input file.

input_file = input_file.merge(month_inflow, on="Branch").drop('Inflow',1)
input_file
  Branch  Visits
0    GGN     130
1    MDU     150
2    PDR     100
3    VLR     112
4    AMB     124
user_60756990
Related