CODEWITHSUNDEEP
pythonregexpandas
Nicholas Stevens
Nicholas Stevens

Reputation: 635

How to dynamically match rows from two pandas dataframes

I have a large dataframe of urls and a smaller 2nd dataframe that contains columns of strings which I want to use to merge the two dataframes together. Data from the 2nd df will be used to populate the larger 1st df.

The matching strings can contain * wildcards (and more then one) but the order of the grouping still matters; so "path/*path2" would match with "exsample.com/eg_path/extrapath2.html but not exsample.com/eg_path2/path/test.html. How can I use the strings in the 2nd dataframe to merge the two dataframes together. There can be more then one matching string in the 2nd dataframe.

import pandas as pd

urls = {'url':['https://stackoverflow.com/questions/56318782/','https://www.google.com/','https://en.wikipedia.org/wiki/Python_(programming_language)','https://stackoverflow.com/questions/'],
        'hits':[1000,500,300,7]}
metadata = {'group':['group1','group2'],
            'matching_string_1':['google','wikipedia*Python_'],
            'matching_string_2':['stackoverflow*questions*56318782','']}
result = {'url':['https://stackoverflow.com/questions/56318782/','https://www.google.com/','https://en.wikipedia.org/wiki/Python_(programming_language)','https://stackoverflow.com/questions/'],
        'hits':[1000,500,300,7],
        'group':['group2','group1','group1','']}

df1 = pd.DataFrame(urls)
df2 = pd.DataFrame(metadata)

what_I_am_after = pd.DataFrame(result)

Upvotes: 1

Views: 209

Answers (1)

Nicholas Stevens
Nicholas Stevens

Reputation: 635

Not very robust but gives the correct answer for my example.

import pandas as pd

urls = {'url':['https://stackoverflow.com/questions/56318782/','https://www.google.com/','https://en.wikipedia.org/wiki/Python_(programming_language)','https://stackoverflow.com/questions/'],
        'hits':[1000,500,300,7]}
metadata = {'group':['group1','group2'],
            'matching_string_1':['google','wikipedia*Python_'],
            'matching_string_2':['stackoverflow*questions*56318782','']}
result = {'url':['https://stackoverflow.com/questions/56318782/','https://www.google.com/','https://en.wikipedia.org/wiki/Python_(programming_language)','https://stackoverflow.com/questions/'],
        'hits':[1000,500,300,7],
        'group':['group2','group1','group1','']}

df1 = pd.DataFrame(urls)
df2 = pd.DataFrame(metadata)
results = pd.DataFrame(columns=['url','hits','group'])

for index,row in df2.iterrows():
    for x in row[1:]:
        group = x.split('*')
        rx = "".join([str(x)+".*"  if len(x) > 0 else '' for x in group])
        if rx == "":
            continue
        filter = df1['url'].str.contains(rx,na=False, regex=True)
        if filter.any():
            temp = df1[filter]
            temp['group'] = row[0]
            results = results.append(temp)

d3 = df1.merge(results,how='outer',on=['url','hits'])

Upvotes: 1

Related Questions