how to simultaneously print all the matched values in a new column in pandas

yan wang 0
Viewed 25

I am a newer in pandas. I would like to search for some targets in a particular column and print all the matched targets in a new column. Here is my code.

# coding=utf-8
import pandas as pd
import numpy as np

#########
classes = [('Carbon;Pyruvate;vitamins', 16.7),
         ('Pyruvate;Carbohydrate;Pentose and glucuronate', 30),
         ('Lipid;Carbon;Galactose', 40.5),
         ('Galactose;Pyruvate;Fatty acid', 57),
         ('Fatty acid;Lipid', 22)]
labels = ['Ko_class','FPKM']
alls = pd.DataFrame.from_records(classes, columns=labels)

target =['Carbon', 'Pyruvate','Galactose']  
    
#####
def match(x):
    for i in target:
        if i in x:   
            return i
    else:
        return np.nan
    
alls['Pathways'] = alls['Ko_class'].apply(match)

Its results are:

                                         Ko_class  FPKM  Pathways
0                       Carbon;Pyruvate;vitamins  16.7    Carbon
1  Pyruvate;Carbohydrate;Pentose and glucuronate  30.0  Pyruvate
2                         Lipid;Carbon;Galactose  40.5    Carbon
3                  Galactose;Pyruvate;Fatty acid  57.0  Pyruvate
4                               Fatty acid;Lipid  22.0       NaN

The expected results are:

                                         Ko_class  FPKM  Pathways
0                       Carbon;Pyruvate;vitamins  16.7   Carbon;Pyruvate
1  Pyruvate;Carbohydrate;Pentose and glucuronate  30.0   Pyruvate
2                         Lipid;Carbon;Galactose  40.5   Carbon;Galactose
3                  Galactose;Pyruvate;Fatty acid  57.0   Galactose;Pyruvate
4                               Fatty acid;Lipid  22.0       NaN

My question is： how to print all the matched targets in the new column like "Carbon;Pyruvate" not only "Carbon".

pandas python
1 Answers

Use str.extractall with aggregation as string instead of your custom function:

regex = '|'.join(target)

alls['Pathsways'] = (alls['Ko_class'].str.extractall(f'({regex})')[0]
                     .groupby(level=0).agg(';'.join))

Or with a list comprehension and a set:

S = set(target)
alls['Pathways'] = [';'.join(x for x in s.split(';') if x in S)
                    for s in alls['Ko_class']]

output:

                                        Ko_class  FPKM            Pathways
0                       Carbon;Pyruvate;vitamins  16.7     Carbon;Pyruvate
1  Pyruvate;Carbohydrate;Pentose and glucuronate  30.0            Pyruvate
2                         Lipid;Carbon;Galactose  40.5    Carbon;Galactose
3                  Galactose;Pyruvate;Fatty acid  57.0  Galactose;Pyruvate
4                               Fatty acid;Lipid  22.0                 NaN
mozway0
Related