Categories
Mastering Development

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

Leave a Reply

Your email address will not be published. Required fields are marked *