Python add values from multiple columns from one dataframe to another dataframe if it doesn't exists

Question

I have two dataframes df1 and df2, I need to check if the values in df1 column x1 and column x2 exist in df2 column x. If the value doesn't exists, then add it to df2 column x and NaN to df2 column y.

The following is the what I have, it works but takes too long for large datasets and I feel it could be improved and simplified using Pandas methods.

df1 = pd.DataFrame({'x1':['a', 'b', 'e'], 'x2':['c', 'd', 'b']})

df2 = pd.DataFrame({'x':['d', 'e', 'f'], 'y':['a1', 'b2', 'c3']})

diff = set([*df1[~df1['x1'].isin(df2['x'])]['x1'], *df1[~df1['x2'].isin(df2['x'])]['x2']])

for x in diff:
    df2 = df2.append({"x":x}, ignore_index=True)

df1:

df2:

Results should be:

x   y
0   d   a1
1   e   b2
2   f   c3
3   c   NaN
4   b   NaN
5   a   NaN

sushanth · Accepted Answer · 2021-05-02 14:42:25Z

2

Here is another way to get it done, using melt + append

melt_ = pd.melt(df1, value_name='x')[["x"]]

df2.append(melt_, ignore_index=True).drop_duplicates('x')

   x    y
0  d   a1
1  e   b2
2  f   c3
3  a  NaN
4  b  NaN
6  c  NaN

edited May 2, 2021 at 14:42

answered May 2, 2021 at 14:35

sushanth

8,2923 gold badges20 silver badges31 bronze badges

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

1 Comment

Inthu Over a year ago

This seems the best way to do it with just utilising pandas, only correction would be: melt_ = pd.melt(df1, value_name='x')[["x"]] df2.append(melt_, ignore_index=True).drop_duplicates('x'). Thanks!

Nk03 · Accepted Answer · 2021-05-02 15:01:21Z

2

Do you want this -

from itertools import chain
value_to_add = set(chain(*df1.values)).difference(df2.x.values)
df2 = pd.concat([df2,pd.DataFrame({'x':list(value_to_add)})]).reset_index(drop=True)

Alternative without itertools chain :

value_to_add = set(df1.values.flatten()).difference(df2.x.values)
df2 = pd.concat([df2,pd.DataFrame({'x':list(value_to_add)})]).reset_index(drop=True)

edited May 2, 2021 at 15:01

answered May 2, 2021 at 14:32

Nk03

15k2 gold badges11 silver badges24 bronze badges

Comments

Andrej Kesely · Accepted Answer · 2021-05-02 14:32:15Z

1

df2 = pd.concat(
    [
        df2,
        pd.DataFrame({"x": np.setdiff1d(df1, df2["x"])}),
    ]
)
print(df2)  # add .reset_index(drop=True) if you want clean index

Prints:

   x    y
0  d   a1
1  e   b2
2  f   c3
0  a  NaN
1  b  NaN
2  c  NaN

answered May 2, 2021 at 14:32

Andrej Kesely

196k15 gold badges60 silver badges105 bronze badges

Collectives™ on Stack Overflow

Python add values from multiple columns from one dataframe to another dataframe if it doesn't exists

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related