Replicating results from pd.merge in SQL?

user_3608833 0
Viewed 205

I've been playing around with some datasets locally in Python, and am now trying to replicate the same results in a cloud environment with SQL.

I have 3 tables, each with multiple duplicate IDs. For example, table A will contain IDs a, b, c, d, ..., table B will contain IDs a, c, e, a1, a2 ..., and table C will contain IDs d, f, a2, b1, b2, ...

I am currently doing pd.merge for table A and table B on ID a, and table C with the resulting table from the first merge on ID a2. On using pd.merge, I noticed that it would add a _x or a _y to the duplicate IDs (by that, I mean in the first pd.merge of table A and table B, c from table A would become c_x, and c from table B would become c_y and so on for any other duplicate IDs. The same would apply to any other duplicate IDs for any joins.

How would I be able to replicate this process and bypass the issue with duplicate IDs in SQL?

pandas select join sql python
2 Answers

You can use multiple join and alias Value:

import sqlite3
import numpy as np
import pandas as pd

# Data
df1 = pd.DataFrame({'ID': list('abcde'),'Value': [1,2,3,4,4] })
df2 = pd.DataFrame({'ID': ' a b e a1 a2'.split(),'Value': [10,20,30,40,40] })
df3 = pd.DataFrame({'ID': 'd f a b b2'.split(),'Value': [100,200,300,400,400] })

# Merge using pandas
df = df1.merge(df2,on='ID').merge(df3,on='ID')
print('using pd.merge')
print(df)

# Using SQL
con = sqlite3.connect("mydb.db")
df1.to_sql("df1", con, if_exists='replace',index=False)
df2.to_sql("df2", con, if_exists='replace',index=False)
df3.to_sql("df3", con, if_exists='replace',index=False)

# sql query
q = """
select df1.Value as Value_df1, df2.Value as Value_df2, df3.Value as Value_df3
from df1
join df2
on df1.ID = df2.ID
join df3
on df1.ID = df3.ID
"""
out = pd.read_sql_query(q,con)
print()
print('-'*50)
print('using sql')
print(out)
con.close()

outputs

using pd.merge
  ID  Value_x  Value_y  Value
0  a        1       10    300
1  b        2       20    400

--------------------------------------------------
using sql
   Value_df1  Value_df2  Value_df3
0          1         10        300
1          2         20        400
user_52003290

I think you can rename in the join the rest of the joined columns with new names:

SELECT table_A.ID_a, table_A.c_ID as c_ID_from_A, table_B.c_ID as c_ID_from_B
FROM table_A
JOIN table_B ON(table_A.ID_a = table_B.ID_a)

Hope you find it usefull.

user_119214890
Related