Raj Hassani Raj Hassani - 16 days ago 8
Python Question

Conditionally concat a dataframe in python using pandas

I have a data frame

df

A B
0 test1 1
1 test2 4
2 test3 1
3 test4 2


df1

C
0 test3
1 test5


I want to conditionally merge them to a new dataframe

df2

A B
0 test1 1
1 test2 4
2 test3 0
3 test4 2
4 test5 0


A new data frame where if the value in column A is equal to column C, then while merging just update its column B value to a default of 0 and if there there isn't a value that exists in column A which is equal to a value in column C just add it to the data frame as shown above with a default value of 0.

Answer

Here is a simple approach. You only take element from the second dataframe in col C which are not in col A on the first dataframe - and concatenate by setting missing values to 0. Finally you use a small hack in groupby in case there are several same values in col A, to select the one with 0:

pd.concat([df,df1.rename(columns={'C':'A'})]).fillna(0).groupby('A', as_index=False).last()

       A  B
0  test1  1
1  test2  4
2  test3  0
3  test4  2
4  test5  0