Merge two columns - Python

1

Hi,

I try to merge two columns from two different excel files.
I struggle a bit the merge of the two columns output.

import pandas as pd

# open xls file
data = pd.read_excel(r'/Users/Test_python.xlsx', sheet_name='Feuil1')
df = pd.DataFrame(data, columns=['Company', 'Selection'])
dt = df['Company'].value_counts()

print(dt)

# open xls file2
data1 = pd.read_excel(r'/Users/Test_python2.xlsx', sheet_name='Salut')
df1 = pd.DataFrame(data1, columns=['Company', 'Selection'])
dt1 = df1['Company'].value_counts()
print(dt1)
result = pd.merge(dt, dt1, on=['Company'])
print(result)

Current output:

MS               63
Swiss Re         63
Allianz          53
Liverpool        48
Mancherster      15
Conoco            4
Zurich            3
HSBC              1
Goldman Sachs     1
Name: Company, dtype: int64
Zurich       123
MS            72
Allianz       48
Liverpool      8

The idea would be to merge the data for the same company name. I think I simply need to add a header to the columns and then refer to this name in the merge, but I struggle to add one.
If somebody could help it would be really nice!

Expected output:

Zurich       126
MS            135
Allianz       101
Liverpool      56

Thank you

Best
T