I have 2 tables with a column in common. I want to combine both tables but the common column doesn’t have the same exact values in both tables.

Table1:

Rate   |  Age
0-4    |  <6.0
5-6    |  6.0
7-8    |  6.3
9-10   |  <6.6
..     |  ..
21     |  8.0
..     |  ..
56-80  |  >18.0

Table2:

Rate    |  Grade
0-6     |  <1.0
7-8     |  1.0
9-11    |  1.2
12-13   |  1.4
..      |  ..
21      |  2.4
..      |  ..
56-80   |  13

The new generated table woule have 3 columns (Rate, Age, Grade).

I tried the folloiwng script:

import pandas as pd

# Read tables into DataFrames
table1 = pd.read_excel('table1.xlsx')
table2 = pd.read_excel('table2.xlsx')

# Merge tables on 'Rate' column with outer join
merged_table = pd.merge(table1, table2, on='Rate', how='inner')

# Print or save merged table
merged_table.to_csv('merged.csv', sep=',')

The output of this script is not what was expected:

Rate    |    Age    |    Grade
8-7     |    3.6    |    1
17-18   |    6.7    |    2
..      |    ..     |    ..

I tried how='outer' and didn’t get the correct results.

The desired output:

Rate    |    Age     |    Grade
0-4     |    <6.0    |    <1.0
5-6     |    6.0     |    <1.0
7-8     |    6.3     |    1
..      |    ..      |    ..

Is that possible with pandas or other module/library?