How to print matched rows while making unmatched rows 0 in pandas?

Question

Here is my data. I would like to print alls['Name'] rows matched specific targets while making unmatched rows "0" or "undected" in a new pd.

classes = [('Carbon', 16.7, 1),
         ('Pentose and glucuronate', 30, 7),
         ('Galactose', 40.5, 9),
         ('Fatty acid', 57, 10),
         ('Carbohydrate', 22, 4)]
labels = ['Name','FPKM', 'count']
alls = pd.DataFrame.from_records(classes, columns=labels)

target = [['Carbon'],['Carbohydrate'], ['Pyruvate'],['Galactose'], ['Lipid']]
targets = pd.DataFrame.from_records(target,columns=['target']))

I have tried using the code as follows, but it doesn't work.

target1 = sum(target, [])
target2 = '|'.join(target1)        

def aggregation(dataframe,target2):
        for i in target1:
            ll=alls.loc[alls['Name'].str.contains(i),:].copy()
            target1 = target1.append(ll, ignore_index=True)  
        return target1

df_result = aggregation(alls, targets)

The results I want is :

                   target   FPKM  count
                   Carbon   16.7      1
             Carbohydrate     22      4
                 Pyruvate      0      0
                Galactose   40.5      9
                    Lipid      0      0

Here, I could print 'Carbon', 'Carbohydrate', 'Galactose' rows matched targets pd while making other unmatched 'Pyruvate', 'Lipid' rows "0" or "undected" in a new pd.

Could anyone help me? Thanks a lot.

score 0 · Accepted Answer · answered Sep 19 '22 at 10:01

You can left-merge:

targets.merge(alls.rename(columns={'Name': 'target'}),
              on='target', how='left').fillna(0, downcast='infer')

output:

         target  FPKM  count
0        Carbon  16.7      1
1  Carbohydrate  22.0      4
2      Pyruvate   0.0      0
3     Galactose  40.5      9
4         Lipid   0.0      0

How to print matched rows while making unmatched rows 0 in pandas?

1 Answers1