CODEWITHSUNDEEP
pythonpandaslistclosest
mo29
mo29

Reputation: 25

Find closest element of a pandas dataframe column in another column's list

I have the following dataframe:

A = [3,38,124]
B = [[0,0,1,7,34,76,4,15,28,8,7,8,200,108,7],[0,0,1,7,34], 
    [4,109,71,257,3,3,7,1,0,0,7,8,100,148,54,3,134,90,23,43,17]]

df = pd.DataFrame({'A':A,
   'B':B})
df

The column B has lists as elements. I want to create a new column with the closest element to column A contained in the corresponding list of B.

Desired output :

A = [3,38,124]
B = [[0,0,1,7,34,76,4,15,28,8,7,8,200,108,7],[0,0,1,7,34], 
[4,109,71,257,3,3,7,1,0,0,7,8,100,148,54,3,134,90,23,43,17]]
Desired_output=[4,34,134]
df_out = pd.DataFrame({'A':A,
   'B':B,
              'Desired_output':Desired_output})
df_out=df_out [['A','B','Desired_output']]
df_out

Upvotes: 2

Views: 553

Answers (2)

tombarti
tombarti

Reputation: 426

To complete the previous answer, if you want to do this after you put your data in a DataFrame, use the DataFrame.apply function as follows:

import pandas as pd
import numpy as np

A = [3, 38, 124]
B = [[0, 0, 1, 7, 34, 76, 4, 15, 28, 8, 7, 8, 200, 108, 7],
     [0, 0, 1, 7, 34],
     [4, 109, 71, 257, 3, 3, 7, 1, 0, 0, 7, 8, 100, 148, 54, 3, 134, 90, 23, 43, 17]]
df = pd.DataFrame({'A': A, 'B': B})

def find_nearest(row):
    return row["B"][np.argmin([abs(candidate-row["A"]) for candidate in row["B"]])]

df["desired_output"] = df.apply(find_nearest, axis=1)

print(df)

Upvotes: 0

Rocky Li
Rocky Li

Reputation: 5958

Try doing that before you put it into a DataFrame, like this:

C = [B[i][np.argmin(np.abs(np.array(B[i]) - A[i]))] for i in range(len(A))]
df = pd.DataFrame({'A':A,
   'B':B, 'Closest':C})

The output is:

     A                                                B    Closest
0    3  [0, 0, 1, 7, 34, 76, 4, 15, 28, 8, 7, 8, 200, ...    4
1   38                                   [0, 0, 1, 7, 34]   34
2  124  [4, 109, 71, 257, 3, 3, 7, 1, 0, 0, 7, 8, 100,...  134

Upvotes: 1

Related Questions