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)
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!
Zurich 126 MS 135 Allianz 101 Liverpool 56