One to many, left, outer join with pandas (Python)

Question

I'm trying to join three tables together using Python 2.7 and pandas. My tables look like the ones below:

Table 1
ID  |  test
1   |  ss
2   |  sb
3   |  sc

Table 2
ID  |  tested  |  value1  |  Value2  |  ID2
1   |  a       |  e       |  o       |  1
1   |  axe     |  ee      |  e       |  1
1   |  bce     |  io      |  p       |  3
2   |  bee     |  kd      |  …       |  2
2   |  bdd     |  a       |  fff     |  3
3   |  db      |  f       |  yiueie  |  2

Table 3
ID2  |  type
1    |  i
1    |  d
1    |  h
3    |  e
1    |  o
2    |  ou
2    |  oui
3    |  op

The code I'm using is below:

import pandas as pd

xl = pd.ExcelFile(r'C:\Users\Joe\Desktop\Project1\xlFiles\test1.xlsx')
xl.sheet_names
df = xl.parse("Sheet1")
df.head()

xl2 = pd.ExcelFile(r'C:\Users\Joe\Desktop\Project1\xlFiles\test2.xlsx')
xl2.sheet_names
df2 = xl2.parse("Sheet1")
df2.head()

xl3 = pd.ExcelFile(r'C:\Users\Joe\Desktop\Project1\xlFiles\test3.xlsx')
xl3.sheet_names
df3 = xl3.parse("Sheet1")
df3.head()

df3 = df3.groupby('ID2')['type'].apply(','.join).reset_index()

s1 = pd.merge(df2, df3, how='left', on=['ID2'])

The code joins Table 3 to table Table 2 how I would like. But, I can't figure out how to group multiple columns to join s1 to Table 1. I need the information from every column in s1 to be added to Table 1, but I only want one row for each ID value (3 rows total). Does anyone know how I would do this?

My expected output, for reference, is below:

ID  |  test  |  type     |  tested     |  value1   |  ID2  
1   |  ss    |  i,d,h,o  |  a,axe,bce  |  e,ee,io  |  1,1,3
2   |  sb    |  ou,oui   |  bee,bdd    |  kd,a     |  2,3
3   |  sc    |  e,op     |  db         |  f        |  2

Thanks in advance for the help.

jezrael · Accepted Answer · 2017-09-10 12:50:28Z

1

You can use cumcount for count ID2 in both df2 and df3 for merge by unique pairs. Then groupby and aggregate join.

Last use join:

df2['g'] = df2.groupby('ID2').cumcount()
df3['g'] = df3.groupby('ID2').cumcount()
df23 = pd.merge(df2, df3, how='left', on=['g','ID2']).astype(str).groupby('ID').agg(','.join)
#for same dtype for match - int
df23.index = df23.index.astype(int)
print (df23)
       tested   value1   Value2    ID2      g   type
ID                                                  
1   a,axe,bce  e,ee,io    o,e,p  1,1,3  0,1,0  i,d,e
2     bee,bdd     kd,a  ...,fff    2,3    0,1  ou,op
3          db        f   yiueie      2      1    oui

df = df1.join(df23, on='ID')
#subset and desired order of output columns
cols = ['ID','test','type','tested','value1','ID2']
df = df[cols]
print (df)
   ID test   type     tested   value1    ID2
0   1   ss  i,d,e  a,axe,bce  e,ee,io  1,1,3
1   2   sb  ou,op    bee,bdd     kd,a    2,3
2   3  sci    oui         db        f      2

answered Sep 10, 2017 at 12:50

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jdavid05 Over a year ago

Hey jezrael - This is exactly what I was looking for. You are truly awesome. Thank you very much for the quick reply!

Collectives™ on Stack Overflow

One to many, left, outer join with pandas (Python)

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related