CODEWITHSUNDEEP
pythonpandas
KLI
KLI

Reputation: 1782

pandas compare and select the smallest number from another dataframe

I have two dataframes.

df1
Out[162]:
     a   b   c
0    0   0   0
1    1   1   1
2    2   2   2
3    3   3   3
4    4   4   4
5    5   5   5
6    6   6   6
7    7   7   7
8    8   8   8
9    9   9   9
10  10  10  10
11  11  11  11

df2  
Out[194]:
   A  B
0  a  3
1  b  4
2  c  5

I wish to create a 3rd column in df2 that maps df2['A'] to df1 and find the smallest number in df1 that's greater than the number in df2['B']. For example, for df2['C'].ix[0], it should go to df1['a'] and search for the smallest number that's greater than df2['B'].ix[0], which should be 4.

I had something like df2['C'] = df2['A'].map( df1[df1 > df2['B']].min() ). But this doesn't work as it won't go to df2['B'] search for corresponding rows. Thanks.

Upvotes: 1

Views: 1016

Answers (1)

EdChum
EdChum

Reputation: 394041

Use apply for row-wise methods:

In [54]:
# create our data
import pandas as pd

df1 = pd.DataFrame({'a':list(range(12)), 'b':list(range(12)), 'c':list(range(12))})
df1
Out[54]:
     a   b   c
0    0   0   0
1    1   1   1
2    2   2   2
3    3   3   3
4    4   4   4
5    5   5   5
6    6   6   6
7    7   7   7
8    8   8   8
9    9   9   9
10  10  10  10
11  11  11  11

[12 rows x 3 columns]

In [68]:
# create our 2nd dataframe, note I have deliberately used alternate values for column 'B'
df2 = pd.DataFrame({'A':list('abc'), 'B':[3,5,7]})
df2
Out[68]:
   A  B
0  a  3
1  b  5
2  c  7

[3 rows x 2 columns]
In [69]:

# apply row-wise function, must use axis=1 for row-wise
df2['C'] = df2.apply(lambda row: df1[row['A']].ix[df1[row.A] > row.B].min(), axis=1)
df2
Out[69]:
   A  B  C
0  a  3  4
1  b  5  6
2  c  7  8

[3 rows x 3 columns]

There is some example usage in the pandas docs

Upvotes: 2

Related Questions