I have this dataframe
df = pd.DataFrame( {'R': {0: '01', 1: '02', 2: '03', 3: '04', 4: '05', 5: '06', 6: '07'}, 'name': {0: 'b', 1: 'm', 2: '', 3: '', 4: 'b', 5: 'mi,b,m,c', 6: 'mi,e,w,c'}, 'value': {0: ['5.01e-13'], 1: ['9.74e-32'], 2: np.nan, 3: np.nan, 4: ['8.58e-09'], 5: ['1.04e-01', '1.18e-01', '7.19e-08', '1.06e-01'], 6: ['2.64e-01', '3.05e-01', '1.77e-01', '2.28e-01']}, } )
which yields to:
R name value
0 01 b [5.01e-13]
1 02 m [9.74e-32]
2 03 NaN
3 04 NaN
4 05 b [8.58e-09]
5 06 mi,b,m,c [1.04e-01, 1.18e-01, 7.19e-08, 1.06e-01]
6 07 mi,e,w,c [2.64e-01, 3.05e-01, 1.77e-01, 2.28e-01]
I need 2 new columns
df['name2']= displays name from df['name'] that has df['value'] < 0.05
df['value2']= displays value from df['value'] that is < 0.05
The following is the desired output:
R name value name2 value2
0 01 b [5.01e-13] b [5.01e-13]
1 02 m [9.74e-32] m [9.74e-32]
2 03 NaN
3 04 NaN
4 05 b [8.58e-09] b [8.58e-09]
5 06 mi,b,m,c [1.04e-01, 1.18e-01, 7.19e-08, 1.06e-01] m [7.19e-08]
6 07 mi,e,w,c [2.64e-01, 3.05e-01, 1.77e-01, 2.28e-01]
I tried several options such as
df['name2']=np.where[(df['value']<0.05), df['name'],'']
or code resulting from this answer, but unfortuantely it did not work.
Split, explode then filter the rows where value is < .05, group the filtered rows by level=0 and aggregate using join.
cols = ['name', 'value']
df1 = df[cols].assign(name=df['name'].str.split(',')).dropna().explode(cols)
df.join(df1[pd.to_numeric(df1['value']) < 0.05].groupby(level=0).agg(','.join).add_suffix('2'))
R name value name2 value2
0 01 b [5.01e-13] b 5.01e-13
1 02 m [9.74e-32] m 9.74e-32
2 03 NaN NaN NaN
3 04 NaN NaN NaN
4 05 b [8.58e-09] b 8.58e-09
5 06 mi,b,m,c [1.04e-01, 1.18e-01, 7.19e-08, 1.06e-01] m 7.19e-08
6 07 mi,e,w,c [2.64e-01, 3.05e-01, 1.77e-01, 2.28e-01] NaN NaN
Note: It is generally not advisable to store complex datatypes (like lists, dicts) in dataframes unless you have a very strong reason. This will affect the performance terribly.