CODEWITHSUNDEEP
python-3.xpandasdataframe
asspsss
asspsss

Reputation: 103

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

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

Upvotes: 0

Views: 27

Answers (1)

BENY
BENY

Reputation: 323266

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

Upvotes: 1

Related Questions