compare values in a column with another dataframe and create a new column

Question

I have dataframe like df

Name	cost	ID
john	300.0	A1
ram	506.0	B2
sam	300.0	C4
Adam	289.0	1

I need to print output as below

Name	cost	ID	Keyword
john	300	A1	RF
ram	506	B2	DD
sam	300	C4	RF
Adam	289	1	Shared

i want to create a new column with condition like if ID has integer values then in Keywords it should be Shared. If ID=A1 the Keyword=RF...etc

Corralien · Accepted Answer · 2023-05-31T12:42:23.700

You need merge and some minor transformations:

out = (df.astype({'cost': int})
         .merge(df1.rename(columns={'Num_ID': 'ID', 'Operator': 'Keyword'}),
                on='ID', how='left')[df.columns.tolist() + ['Keyword']])
print(out)

# Output
   Name  cost  ID Keyword
0  john   300  A1      RF
1   ram   506  B2      DD
2   sam   300  C4      RF

Read Pandas Merging 101

Another solution:

out = df.assign(cost=df['cost'].astype(int), 
                Keyword=df['ID'].map(df1.set_index('Num_ID')['Operator']))
print(out)

# Output
   Name  cost  ID Keyword
0  john   300  A1      RF
1   ram   506  B2      DD
2   sam   300  C4      RF

compare values in a column with another dataframe and create a new column

1 Answers1