sql - join two dataframes on common column

Question

I want to join two data sources, orders and customers:

orders is an SQL Server table:

orderid| customerid | orderdate | ordercost
------ | -----------| --------- | --------
12000  | 1500       |2008-08-09 |  38610

and customers is a csv file:

customerid,first_name,last_name,starting_date,ending_date,country
1500,Sian,Read,2008-01-07,2010-01-07,Greenland

I want to join these two tables in my Python application, so I wrote the following code:

# Connect to SQL Sever with Pyodbc library

connection = pypyodbc.connect("connection string here")
cursor=connection.cursor();
cursor.execute("SELECT * from order)
result= cursor.fetchall()

# convert the result to pandas Dataframe
df1 = pd.DataFrame(result, columns= ['orderid','customerid','orderdate','ordercost'])

# Read CSV File
df2=pd.read_csv(customer_csv)

# Merge two dataframes
merged= pd.merge( df1, df2, on= 'customerid', how='inner')
print(merged[['first_name', 'country']])

I expect

first_name | country
-----------|--------
Sian       | Greenland

But I get empty result.

When I perform this code for two data frames that are both from CSV files, it works fine. Any help?

Thanks.

Answer 1

I think problem is columns customerid has different dtypes in both DataFrames so no match.

So need convert both columns to int or both to str.

df1['customerid'] = df1['customerid'].astype(int)
df2['customerid'] = df2['customerid'].astype(int)

Or:

df1['customerid'] = df1['customerid'].astype(str)
df2['customerid'] = df2['customerid'].astype(str)

Also is possible omit how='inner', because default value of merge:

merged= pd.merge( df1, df2, on= 'customerid')