How do I compare a dataframe column with another dataframe and create a column

Question

I have two dataframes df1 and df2. Here is a small sample

Days 
4
6
9
1
4

My df2 is

Day1  Day2 Alphabets
2       5    abc
4       7    bcd
8       10   ghi
10      12   abc

I want to change my df1 such that it has new column Alphabets from df2 if the days in df1 is between day1 and day2. Something like:

if df1['Days'] in between df2['Day1'] and df2['Day2']:
    df1['Alphabets']=df2['Alphabets']

Result is:

Days Alphabets
4     abc
6     bcd
9     ghi

etc.

I tried for loop and its taking a lot of time even to run. Is there any other elegant way to do?

Thanks in advance

BENY · Accepted Answer · 2019-11-15 20:44:49Z

1

I will use numpy broadcast

s1=df2.Day1.values
s2=df2.Day2.values
s=df1.Days.values[:,None]
df1['V']=((s-s1>0)&(s-s2<0)).dot(df2.Alphabets)
df1
Out[277]: 
   Days    V
0     4  abc
1     6  bcd
2     9  ghi
3     1     
4     4  abc

answered Nov 15, 2019 at 20:44

BENY

324k22 gold badges176 silver badges250 bronze badges

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

Collectives™ on Stack Overflow

How do I compare a dataframe column with another dataframe and create a column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related