Pandas Dataframe Search Comparing Data Frames of Different Sizes

Question

I have two pandas df that look like this:

df1
   Amount   Price
0    5       50
1    10      53 
2    15      55
3    30      50
4    45      61

df2 
     Used amount
 0      4.5
 1      1.2
 2      6.2
 3      4.1 
 4      25.6
 5      31
 6      19
 7      15  

I am trying to insert a new column on df2 which will give the price of df1, df1 and df2 to have different sizes, df1 is smaller

I expect something like this

df3 
     Used amount price
 0      4.5       50
 1      1.2       50
 2      6.2       53
 3      4.1       50
 4      25.6      50
 5      31        61
 6      19        50
 7      15        55

I am going to solve this using something like this function

def price_function(key, table):
    used_amount_df2 = (row[0] for row in df1)
    price = filter(lambda x: x < key, used_amount_df1)

Answer 1

Here is my own decision

1st approach:

from itertools import product
import pandas as pd
df2=df2.reset_index()
DF=pd.DataFrame(list(product(df2.Usedamount, df1.Amount)), columns=['l1', 'l2'])
DF['DIFF']=(DF.l1-DF.l2)
DF=DF.loc[DF.DIFF<=0,]
DF=DF.sort_values(['l1','DIFF'],ascending=[True,False]).drop_duplicates(['l1'],keep='first')
df1.merge(DF,left_on='Amount',right_on='l2',how='left').merge(df2,left_on='l1',right_on='Usedamount',how='right').loc[:,['index','Usedamount','Price']].set_index('index').sort_index()


Out[185]: 
       Usedamount  Price
index                   
0             4.5     50
1             1.2     50
2             6.2     53
3             4.1     50
4            25.6     50
5            31.0     61
6            19.0     50
7            15.0     55

---

2nd using pd.merge_asof I recommend this

df2=df2.rename({'Used amount':Amount}).sort_values('Amount')
df2=df2.reset_index()
  pd.merge_asof(df2,df1,on='Amount',allow_exact_matches=True,direction='forward')\
   .set_index('index').sort_index()

Out[206]: 
       Amount  Price
index               
0         4.5     50
1         1.2     50
2         6.2     53
3         4.1     50
4        25.6     50
5        31.0     61
6        19.0     50
7        15.0     55

Answer 2

You can use cutor searchsortedto create baskets.

Note. The index in df1should be the default 0,1,2....

#create default index if necessary
df1 = df1.reset_index(drop=True)

#create bins
bins = [0] + df1['Amount'].tolist()
#get index values of df1 by values of Used amount
a = pd.cut(df2['Used amount'], bins=bins, labels=df1.index)
#assign output
df2['price'] = df1['Price'].values[a]
print (df2)
   Used amount  price
0          4.5     50
1          1.2     50
2          6.2     53
3          4.1     50
4         25.6     50
5         31.0     61
6         19.0     50
7         15.0     55

---

a = df1['Amount'].searchsorted(df2['Used amount'])
df2['price'] = df1['Price'].values[a]
print (df2)
   Used amount  price
0          4.5     50
1          1.2     50
2          6.2     53
3          4.1     50
4         25.6     50
5         31.0     61
6         19.0     50
7         15.0     55

Answer 3

pd.DataFrame.reindex method=bfill

df1.set_index('Amount').reindex(df2['Used amount'], method='bfill')

             Price
Used amount       
4.5             50
1.2             50
6.2             53
4.1             50
25.6            50
31.0            61
19.0            50
15.0            55

---

,

join

df2.join(
    df1.set_index('Amount').reindex(df2['Used amount'], method='bfill'),
    on='Used amount'
)

   Used amount  Price
0          4.5     50
1          1.2     50
2          6.2     53
3          4.1     50
4         25.6     50
5         31.0     61
6         19.0     50
7         15.0     55

assign

df2.assign(
    Price=df1.set_index('Amount').reindex(df2['Used amount'], method='bfill').values)

   Used amount  Price
0          4.5     50
1          1.2     50
2          6.2     53
3          4.1     50
4         25.6     50
5         31.0     61
6         19.0     50
7         15.0     55

Answer 4

pd.IntervalIndex,

In [468]: df1.index = pd.IntervalIndex.from_arrays(df1.Amount.shift().fillna(0),df1.Amount)

In [469]: df1
Out[469]:
              Amount  Price
(0.0, 5.0]         5     50
(5.0, 10.0]       10     53
(10.0, 15.0]      15     55
(15.0, 30.0]      30     50
(30.0, 45.0]      45     61

In [470]: df2['price'] = df2['Used amount'].map(df1.Price)

In [471]: df2
Out[471]:
   Used amount  price
0          4.5     50
1          1.2     50
2          6.2     53
3          4.1     50
4         25.6     50
5         31.0     61
6         19.0     50
7         15.0     55