How can I create new dataframe column with values based on condition of 2 columns in python?

Question

I have 2 dataframes, one containing 2 columns (date and key) and other containing the same 2 columns (date and key). I would like to create a new column in one dataframe with '1' value if the date and the key exists in the other dataframe, and '0' if it does not exist. Here is an example:

df1:
+---------+--------+
|  date   |  key   |
+---------+--------+
|  date1  |    A   |
+---------+--------+
|  date2  |    A   |
+---------+--------+
|  date3  |    B   |
+---------+--------+


df2:
+---------+--------+
|  date   |  key   |
+---------+--------+
|  date1  |    A   |
+---------+--------+
|  date4  |    C   |
+---------+--------+
|  date5  |    B   |
+---------+--------+


resulting df1:

+---------+--------+--------+
|  date   |  key   |  col3  |
+---------+--------+--------+
|  date1  |    A   |   1    |
+---------+--------+--------+
|  date2  |    A   |   0    |
+---------+--------+--------+
|  date3  |    B   |   0    |
+---------+--------+--------+

In this example, as the first row of df1 (date1, A) exists in df2, the value of col3 is 1, and the other rows are 0.

How can I do it?

jezrael · Accepted Answer · 2021-04-12 07:56:30Z

2

Use indicator parameter for new columns and then convert to 1,0 by compare both string:

df = df1.merge(df2, how='left', indicator='col3', on=['date','key'])
df['col3'] = df['col3'].eq('both').astype(int)

Or:

df['col3'] = np.where(df['col3'].eq('both'), 1, 0)

answered Apr 12, 2021 at 7:56

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

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

2 Comments

jezrael Over a year ago

@MustafaAydın - Thank you, I think isin is cannot used here, because it compare by rows, so first row in df1 with first row in df2..., also failed if different numebr of rows.

Mustafa Aydın Over a year ago

Actually for each row, it looks if it exists in the whole data frame, not only the corresponding row. Also, works if different number of rows. However, the index should be exactly the same so even though the values are the same it won't work. So it is not suitable I agree.

Collectives™ on Stack Overflow

How can I create new dataframe column with values based on condition of 2 columns in python?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related