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

Question

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

rename the column Visits to Inflow and then merge

anky
– anky

2020-06-26 13:15:11 +00:00
Commented Jun 26, 2020 at 13:15 — anky
– anky, Commented Jun 26, 2020 at 13:15

BENY · Accepted Answer · 2020-06-26 13:22:19Z

2

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

answered Jun 26, 2020 at 13:22

BENY

324k22 gold badges176 silver badges250 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dishin Goyani · Accepted Answer · 2020-06-26 13:30:20Z

0

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

answered Jun 26, 2020 at 13:30

Dishin Goyani

7,7533 gold badges33 silver badges42 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related