Create column in pandas dataframe based on condition

Question

I have a dataframe, want to create third column say col3 based on the condition if col2 value is present in col1 then 'Yes' else 'No'

data = [[[('330420', 0.9322496056556702), ('76546', 0.9322003126144409)],76546],[[('330420', 0.9322496056556702), ('500826', 0.9322003126144409)],876546]]
test = pd.DataFrame(data, columns=['col1','col2'])

                                                col1    col2
0  [(330420, 0.9322496056556702), (76546, 0.93220...   76546
1  [(330420, 0.9322496056556702), (500826, 0.9322...  876546

Desired result:

data = [[[('330420', 0.9322496056556702), ('76546', 0.9322003126

    144409)],76546, 'Yes'],[[('330420', 0.9322496056556702), ('500826', 0.9322003126144409)],876546,'No']]
    test = pd.DataFrame(data, columns=['col1','col2', 'col3'])

                                                    col1    col2 col3
    0  [(330420, 0.9322496056556702), (76546, 0.93220...   76546  Yes
    1  [(330420, 0.9322496056556702), (500826, 0.9322...  876546   No

My Solution:

test['col3'] = [entry for tag in test['col2'] for entry in test['col1'] if tag in entry]

Getting error: ValueError: Length of values does not match length of index

score 4 · Answer 1 · answered Oct 23 '18 at 01:46

4

Using any with zip

[any([int(z[0])==y for z in x]) for x, y in zip (test.col1,test.col2)]
Out[227]: [True, False]

answered Oct 23 '18 at 01:46

BENY

317,841
20
164
234

Small comment: You have a pair of unnecessary brackets around any. – Anton vBR Oct 23 '18 at 02:31

score 1 · Answer 2 · answered Oct 23 '18 at 02:19

You should avoid lists in series. Let's try a vectorised solution:

# extract array of values and reshape
arr = np.array(df.pop('col1').values.tolist()).reshape(-1, 4)

# join to dataframe and replace list of tuples
df = df.join(pd.DataFrame(arr, dtype=float))

# apply test via isin
df['test'] = df.drop('col2', 1).isin(df['col2']).any(1)

print(df)

     col2         0        1         2       3   test
0   76546  330420.0  0.93225   76546.0  0.9322   True
1  876546  330420.0  0.93225  500826.0  0.9322  False

Mankind_008 · Answer 3 · 2018-10-23T02:32:00.847

0

Using numpy where:

test['col3'] = test.apply(lambda x: np.where(str(x.col2) in [i[0] for i in x.col1],"yes", "no"), axis =1)
test['col3']
0    yes
1     no

edited Oct 23 '18 at 02:32

answered Oct 23 '18 at 02:16

Mankind_008

2,158
2
9
15

score 0 · Accepted Answer · answered Oct 23 '18 at 02:22

You can do this using .apply()

def sublist_checker(row):
    check_both = ['Yes' if str(row['col2']) in sublist else 'No' for sublist in row['col1']]
    check_any = 'Yes' if 'Yes' in check_both else 'No'
    return check_any

test['col3'] = test.apply(sublist_checker, axis=1)
print(test)

                                                   col1    col2 col3
0   [(330420, 0.932249605656), (76546, 0.932200312614)]   76546  Yes
1  [(330420, 0.932249605656), (500826, 0.932200312614)]  876546   No

The function sublist_checker performs a row-wise check of each element in test['col2'] against each sub-list found in test['col1'] and returns Yes or No based on presence or absence of that element in any of the sub-lists.

@user15051990 if you check the running time, you will figure out apply method is the less efficient one ., — BENY, Oct 23 '18 at 14:07

Create column in pandas dataframe based on condition

4 Answers4