Merge row of two different dataframe using a column as pattern matching

user_6140391 0
Viewed 265

I have two dataframes.

df = pd.DataFrame([['klf1', 10], ['sp2', 3], ['klf3', 12], ['egr1', 5], ['klf11', 2]], columns=['tf','count'])
df2 = pd.DataFrame([['Homer-sp2', 0.01], ['Homer-klf1', 0.0001], ['Homer-klf3-chip', 0.05],  ['klf11',0.002], ['Homer-egr1', 0.01]], columns=['Motif_name','p_val'])

I'm trying to merge them using df['tf'] content.

For exemple 'klf1' row should be 'klf1' 10 0.0001

So I have to use the content of df['tf'] as pattern matching in df2[Motif_name]

I'm expecting to get this df

df_final=pd.DataFrame([['Klf1', 10,0.0001], ['sp2', 3, 0.01], ['klf3', 12,0.05], ['egr1', 5,0.01], ['klf11', 2,0.002]], columns=['tf','count','p_val'])

I tried to use .str.contains('pattern') but it works only for one pattern. Here I don't know how can I loop on the content of the column tf.

df2['Motif_name'].str.contains(df['tf'].str.lower())

Also .str.contains will look for pattern and I know that I'll have a problem on klf1 VS klf11 because klf1 is in klf11. How can I manage this problem?

pandas python
3 Answers

You can extract the numbers from klfXY to temporary columns and do pd.merge on this column:

df['key'] = df.tf.str.extract(r'klf(\d+)')
df2['key'] = df2.Motif_name.str.extract(r'klf(\d+)')

out = pd.merge(df, df2, on='key').drop('key', axis=1)
print(out)

Prints:

      tf  count       Motif_name   p_val
0   klf1     10       Homer-klf1  0.0001
1   klf2      3       Homer-klf2  0.0100
2   klf3     12  Homer-klf3-chip  0.0500
3   klf9      5       Homer-klf9  0.0100
4  klf11      2            klf11  0.0020

EDIT: Based on new data, you can use other regular expression:

df['key'] = df.tf.str.extract(r'(?:^|-)([a-zA-Z]+\d+)')
df2['key'] = df2.Motif_name.str.extract(r'(?:^|-)([a-zA-Z]+\d+)')

df['key'] = df['key'].str.lower()
df2['key'] = df2['key'].str.lower()

out = pd.merge(df, df2, on='key').drop('key', axis=1)
print(out)

Prints:

      tf  count       Motif_name   p_val
0   klf1     10       Homer-klf1  0.0001
1    sp2      3        Homer-sp2  0.0100
2   klf3     12  Homer-klf3-chip  0.0500
3   egr1      5       Homer-egr1  0.0100
4  klf11      2            klf11  0.0020
user_100359850

Just to fix your approach:

import re

res=df.assign(key=1).merge(df2.assign(key=1), on='key').drop('key', axis=1)

res=res.loc[map(lambda x: True if re.search(*x) else False, zip(res['tf'].str.lower()+r'($|[^\d])', res['Motif_name']))]

Outputs:

>>> res

       tf  count       Motif_name   p_val
1    klf1     10       Homer-klf1  0.0001
5    klf2      3       Homer-klf2  0.0100
12   klf3     12  Homer-klf3-chip  0.0500
19   klf9      5       Homer-klf9  0.0100
23  klf11      2            klf11  0.0020
user_116101860

The idea is simple, create a function create_col which takes in a Series as its parameter or in simple words, we are going to run this function for each row.

Steps:

  1. Iterate over each row of df (This can be done using in-built apply function).
  2. find a row in df2 where df2['Motif_name'] contains df['tf'] .
  3. fill df['p_val'] as df2['p_val'] from row obtained in step 2.
def create_col(row):
    row['p_val'] = df2[df2['Motif_name'].str.contains(row['tf'])].iloc[0, 1]
    return row

df = df.apply(create_col, axis = 1) # axis = 1 to apply function to each row
user_109172260
Related