CODEWITHSUNDEEP
pythonpandas
Yulia Kentieva
Yulia Kentieva

Reputation: 720

How to refer to a table in python?

I have 2 tables:

  1. relations['entry1', 'entry2', 'relation']

  2. entries['id', 'name', 'code']

I need to use the entries table as a "vocabulary" (entry1 and entry2 are ids), so I can refer to entries from relations to get names of entries saving relations at the same time.

My goal is to have a table like this:

final['name1','name2', 'relation']

I tried to merge the tables like this:

merge1=pd.merge(left=entries, right=relation, left_on='id', right_on='entry1')
merge2=pd.merge(left=entries, right=relation, left_on='id', right_on='entry2')
merge=pd.merge(left=merge1, right=merge2, left_on='entry1', right_on='entry1')

but it did not work

Upvotes: 0

Views: 203

Answers (2)

Yulia Kentieva
Yulia Kentieva

Reputation: 720

merge = pd.merge(left=relations, right=entries, left_on='entry1', right_on='id')
merge = pd.merge(left=merge, right=entries, left_on='entry2', right_on='id')

this worked for me

Upvotes: 1

xxMrPHDxx
xxMrPHDxx

Reputation: 667

You can do it using two merge call as such

# Merge using entries & relations on entry1 
merge = pd.merge(left=entries, right=relations, left_on='id', right_on='entry1')
# Removed the unused column 'entry1'
merge = merge[[col for col in merge.columns if col != 'entry1']]
# Merge using entries & merge on entry2
merge = pd.merge(left=entries, right=merge, left_on='id', right_on='entry2')
# Removed the unused column 'entry2'
merge = merge[[col for col in merge.columns if col != 'entry2']]
print('Merged table')
print(merge)

The output will look like

  id_x name_x  code_x id_y name_y  code_y relation
0    a     e1       1    a     e1       1       r1
1    b     e2       2    b     e2       2       r2
2    c     e3       3    c     e3       3       r3

Hope this helps

Upvotes: 0

Related Questions