how to merge two data frames based on particular column in pandas python

Question

I want to  merge two data frames:

df1

company,standard
tata,A1
cts,A2
dell,A3

df2

company,return
tata,71
dell,78
cts,27
hcl,23

I want the output to be like:

company,standard,return
tata,A1,71
cts,A2,27
dell,A3,78

Can someone help me do this?

Answer 1

import openpyxl

sample_data = openpyxl.load_workbook('sample.xlsx')
master_data = openpyxl.load_workbook('School-Abstract.xlsx')

sample_sheet = sample_data['Sheet2']
master_sheet = master_data['school']

for i in sample_sheet.iter_rows():
    id = i[1].value
    row_number = i[1].row
    for j in master_sheet.iter_rows():
        if j[0].value == id:
            sample_sheet.cell(row=row_number, column=1).value = j[0].value
            sample_sheet.cell(row=row_number, column=2).value = j[1].value
            sample_sheet.cell(row=row_number, column=3).value = j[2].value
            sample_sheet.cell(row=row_number, column=4).value = j[3].value
            sample_sheet.cell(row=row_number, column=5).value = j[5].value
            
sample_data.save('output.xlsx')

this is vlookup in python , analyse the above programe and give sample datas to work on it , it will give you an idea

Answer 2

import openpyxl

sample_data = openpyxl.load_workbook('sample.xlsx')
master_data = openpyxl.load_workbook('School-Abstract.xlsx')

sample_sheet = sample_data['Sheet2']
master_sheet = master_data['school']

for i in sample_sheet.iter_rows():
    id = i[1].value
    row_number = i[1].row
    for j in master_sheet.iter_rows():
        if j[0].value == id:
            sample_sheet.cell(row=row_number, column=1).value = j[0].value
            sample_sheet.cell(row=row_number, column=2).value = j[1].value
            sample_sheet.cell(row=row_number, column=3).value = j[2].value
            sample_sheet.cell(row=row_number, column=4).value = j[3].value
            sample_sheet.cell(row=row_number, column=5).value = j[5].value
            
sample_data.save('output.xlsx')

This is an example programme for concat two dataframes it will concat based on the row value you trying to merge