CODEWITHSUNDEEP
pythonpandaslambda
Annalix
Annalix

Reputation: 480

Element columns association in a dataframe

I have the following dataframe and I would like to write a lambda function able to recognise that if an element in col1 is in one of the group in col2 so it take the value col3 associated to that group as in the Result below for col1=(5,6) having the value col3=(0.3,0.2)

col1 col2 col3
1 .  1    0.7
2 .  2 .  0.9
3 .  3,5 .0.3
4 .  4,6 .0.2  
5 .  NaN .NaN
6 .  NaN .NaN

Result

col1 col2 col3
1 .  1    0.7
2 .  2 .  0.9
3 .  3,5 .0.3
4 .  4,6 .0.2  
5 .  NaN .0.3
6 .  NaN .0.2

I wrote the lambda function

f = lambda x: next(iter([y for y in df['col2'].dropna().tolist() if str(x) in y]), df['col3])
df['col1'].apply(f)

But I am not quite sure this the way

Upvotes: 0

Views: 177

Answers (2)

Dani Mesejo
Dani Mesejo

Reputation: 61930

Assuming the values of col2 are list, for example: 

   col1    col2  col3
0     1     [1]   0.1
1     2  [2, 4]   0.2
2     3  [3, 5]   0.3
3     4  [4, 6]   0.2
4     5     NaN   NaN
5     6     NaN   NaN

You could do the following:

# create lookup dictionary to replace for nan
pairs = df[['col2', 'col3']].explode('col2').dropna()
lookup = {int(key): value for key, value in pairs.itertuples(index=False, name=None)}

# replace the value only where isna
df['col3'] = np.where(df.col3.isna(), df.col1.map(lookup.get), df.col3)

print(df)

Output

   col1    col2  col3
0     1     [1]   0.1
1     2  [2, 4]   0.2
2     3  [3, 5]   0.3
3     4  [4, 6]   0.2
4     5     NaN   0.3
5     6     NaN   0.2

Notice that you must import numpy for this solution to work. If col2 is a string, you could try:

df['col2'] = df.col2.str.split(",")

Upvotes: 1

BENY
BENY

Reputation: 323366

IIUC method from explode and map 

s=df.assign(col2=df.col2.str.split(',')).explode('col2')
df['col3']=df.col1.astype(str).map(s.dropna().set_index('col2').col3).values
df
   col1 col2  col3
0     1    1   0.7
1     2    2   0.9
2     3  3,5   0.3
3     4  4,6   0.2
4     5  NaN   0.3
5     6  NaN   0.2

Upvotes: 1

Related Questions