CODEWITHSUNDEEP
pythonpandasdataframe
Matt-pow
Matt-pow

Reputation: 986

Merge a column based on condition

I have two tables and I want to map one table to another with an id column. These two tables don't have equal size of ids. If the ids are not found, then the column result with n/a.

Table A

id   type
2231  C
1213  A
1241  C

Table B

id     other columns
2232       X
1213       Y
1233       I

The goal is to map id values from Table A and add an addition column in Table B.

Ideal Table

id     other columns    type
2232       X             n/a
1213       Y             A
1233       I             n/a

I tired to find all the Type A from Table A.

typeA = df_A.id.loc[df_A.type == 'A']).tolist()
df_B.type = df_B.loc[df_B.isin(typeA)]
    ...

I thought about merging the two tables by ids, but they have different id size and maybe there are different ids too. Is there a more efficient way to do this? Thank you for your time.

Upvotes: 2

Views: 618

Answers (2)

Vaishali
Vaishali

Reputation: 38415

You can use map

dfB['type'] = dfB.id.map(dfA.set_index('id').type)

    id   other columns  type
0   2232    X           NaN
1   1213    Y           A
2   1233    I           NaN

Upvotes: 5

Space Impact
Space Impact

Reputation: 13255

You can use merge on id using parameter how='left' and fillna with n/a as:

df_B.merge(df_A,on='id',how='left').fillna('n/a')

     id other columns type
0  2232             X  n/a
1  1213             Y    A
2  1233             I  n/a

Upvotes: 2

Related Questions