CODEWITHSUNDEEP
pythonpandas
cyrus24
cyrus24

Reputation: 363

Create column based on the comparative match between two dataframes

I have a dataframe A which has a column called A['Income'] and another dataframe B which has columns - B['Income'] and B['category']. I need to compare A['Income'] with B['Income'] and create A['category'] such that, when A['Income'] <= B['Income'] then A['category'] takes corresponding value of B['category']. and if A['Income'] > 1000 then A['category'] = 0.1

A:
Income
1000
1234
3007
4569
7065
1456
2980
8990
900
489

B:
Income   category
1000      1.1
2500      1.2
4000      1.3
5500      1.4
7000      2.1
8500      2.2

Desired output: 
A:
    Income   category
    1000      1.1
    1234      1.1
    3007      1.2
    4569      1.4
    7065      2.2
    1456      1.1
    2980      1.3
    6450      2.1    
    900       0.1
    489       0.1

Below is what I am attempting but I just am not able to develop the logic of assigning the corresponding value to a new column. It is a dictionary like mapping, but without perfect equality and a range needs to be defined.

for e in A.Income:
print(e)
l=[]    
for j,k in zip(B.Income, B.category):
    if e<=j:
        l.append(k)
    else:
        pass
p.append(B[B['Income']==l[0]].category.values)

brack=list(chain.from_iterable(p))
A['category']=brack

Upvotes: 1

Views: 39

Answers (1)

BENY
BENY

Reputation: 323226

Try with merge_asof

df=pd.merge_asof(A.sort_values('Income'),B,on='Income').fillna(0.1)
   Income  category
0     489       0.1
1     900       0.1
2    1000       1.1
3    1234       1.1
4    1456       1.1
5    2980       1.2
6    3007       1.2
7    4569       1.3
8    7065       2.1
9    8990       2.2

Update to match the output

s=pd.merge_asof(A.reset_index().sort_values('Income'),B,on='Income',direction='forward').\
   dropna().set_index('index').sort_index()
s.loc[s.Income<1000,'category']=0.1
s
       Income  category
index                  
0        1000       1.1
1        1234       1.2
2        3007       1.3
3        4569       1.4
4        7065       2.2
5        1456       1.2
6        2980       1.3
8         900       0.1
9         489       0.1

Upvotes: 4

Related Questions