Merging pandas dataframe on unique values in a column

Question

I have a df1 as:

There are a lot of duplicating values for SUBJECT_ID as shown in the picture. I have a df2 to merge from, but I want to merge it on unique SUBJECT_ID. For now I only know how to merge to entire SUBJECT_ID through this code:

df1 = pd.merge(df1,df2[['SUBJECT_ID', 'VALUE']], on='SUBJECT_ID', how='left' )

But this will merge on every SUBJECT_ID. I just need unique SUBJECT_ID. Please help me with this.

Add code, errors, and data as text, not screenshots because Stack Overflow Discourages Screenshots. It is likely the question will be down-voted. You are discouraging assistance because no one wants to retype your data or code, and screenshots are often illegible. edit the question and add text. — Trenton McKinney
– Trenton McKinney, Commented Jul 16, 2020 at 3:15
Please create a reproducible copy of the DataFrame with df.head(10).to_clipboard(sep=','), edit the question, and paste the clipboard into a code block or include synthetic data: How to make good reproducible pandas examples — Trenton McKinney
– Trenton McKinney, Commented Jul 16, 2020 at 3:16
If there are so many duplicates, .drop_duplicates or groupby. — Trenton McKinney
– Trenton McKinney, Commented Jul 16, 2020 at 3:18
In copy and pasteable format what is df1 and df1 (include that in oyur question). Then, what is your desired output?: stackoverflow.com/questions/20109391/… — David Erickson
– David Erickson, Commented Jul 16, 2020 at 4:12

a11 · Accepted Answer · 2020-07-16 04:20:02Z

2

I think you will find your answer with the merge documentation.

It's not fully clear what you want, but here are some examples that may contain the answer you are looking for:

import pandas as pd
df1 = pd.read_csv('temp.csv')
display(df1)

SUBJECT_ID = [31, 32, 33]
something_interesting = ['cat', 'dog', 'fish']
df2 = pd.DataFrame(list(zip(SUBJECT_ID, something_interesting)), 
                   columns =['SUBJECT_ID', 'something_interesting']) 
display(df2)

df_keep_all = df1.merge(df2, on='SUBJECT_ID', how='outer')
display(df_keep_all)

df_keep_df1 = df1.merge(df2, on='SUBJECT_ID', how='inner')
display(df_keep_df1)

df_thinned = pd.merge(df1.drop_duplicates(), df2, on='SUBJECT_ID', how='inner')
display(df_thinned)

answered Jul 16, 2020 at 4:20

a11

3,4768 gold badges42 silver badges91 bronze badges

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

Comments

tlentali · Accepted Answer · 2021-11-03 10:51:13Z

2

You can use pandas drop function for it using this function you can remove all duplicate values for column or columns.

df2 = df.drop_duplicates(subset=['SUBJECT_ID'])`

edited Nov 3, 2021 at 10:51

tlentali

3,4632 gold badges18 silver badges23 bronze badges

answered Jul 16, 2020 at 6:15

Jeel Gondaliya

1479 bronze badges

Collectives™ on Stack Overflow

Merging pandas dataframe on unique values in a column

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related