grab values and column names based on row values - columns with ranges-CodePudding

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.

CodePudding user response：

Pandas approach:

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.

CodePudding user response：

Firstly you need to convert the name column from a string to array of string by splitting on the , character.

df['name'] = df['name'].apply(lambda x: x.split(','))

Now you can simply apply another lambda function to get the desired output for name2 column.

def calc(x):
    if x['value'] is np.nan:
        return []

    res = []
    for i,v in enumerate(x['value']):
        v = float(v)
        if v < 0.05:
            res.append(x['name'][i])
    return res


df['name2'] = df.apply(lambda x: calc(x), axis=1)
print(df)

Output

    R           name                                     value name2
0  01            [b]                                [5.01e-13]   [b]
1  02            [m]                                [9.74e-32]   [m]
2  03             []                                       NaN    []
3  04             []                                       NaN    []
4  05            [b]                                [8.58e-09]   [b]
5  06  [mi, b, m, c]  [1.04e-01, 1.18e-01, 7.19e-08, 1.06e-01]   [m]
6  07  [mi, e, w, c]  [2.64e-01, 3.05e-01, 1.77e-01, 2.28e-01]    []