Pandas most efficient way to set value of df when searching nested dictionary for value

I have a dataframe with millions of rows and I'm searching for the column values of the dataframe inside the dictionary of lists to retrieve the key and use this key to get a value from a metadata table and then set that value as a new column in the df.

map_dict = {'AP017903.1': "['BAX03457', 'BAX03456', 'BAX03455', 'BAX03454']",
       'BK013208': "['BK013208', 'BK013208', 'BK013208', 'BK013208']"}

metadata = pd.DataFrame({'ID':['AP017903.1','BK013208'], 'length':[99517,102321]})

df = pd.DataFrame({'qseqid':['BAX03457.1','BAX03457.1','BAX03456.1','BAX03455.1'],
                   'sseqid':['BK013208_1','BK013208_2','BK013208_3','BK013208_4']})

My code is working extremely slowly as I'm iterating through the dataframe and setting the value for each row in place. I'm wondering if anyone has any suggestions on how to speed up the code or if I'm doing this in a really inefficient way. The dictionary is reduced to scale and each key can have 100's of values in reality.

for idx, row in df.iterrows():
    # regex to match everything up until first occurrence of '.' or '_'
    qseqid_pattern = re.search(r'(?:(?![\.|\_]).)*', row['qseqid']).group(0)
    sseqid_pattern = re.search(r'(?:(?![\.|\_]).)*', row['sseqid']).group(0)
    qseqid_id = [key for key, value in map_dict.items() if qseqid_pattern in value][0]
    sseqid_id = [key for key, value in map_dict.items() if sseqid_pattern in value][0]
    if qseqid_id:
          df.loc[idx,'qseqid_length'] = metadata[metadata['ID']==qseqid_id ]['length'].values[0]
    else:
        pass
    if sseqid_id: 
          df.loc[idx,'sseqid_length'] = metadata[metadata['ID']==sseqid_id]['length'].values[0]
    else:
        pass

Would it be faster to just append all the values to a list memory permitting? Any thoughts or insight greatly appreciated! I'm considering trying awk since this is taking so long.

It seems you just want to do a map? Just construct your mapping series in multiple steps.

I've done a quick and dirty map from your map_dict, as follows. I would not run this code in production because it uses eval. However your mapping currently works, parse your output that looks like str(your_list) into a real list then explode.

map1 = pd.DataFrame(map_dict, index=[0, 1]).unstack().reset_index() \
    .assign(l=lambda d: d[0].apply(eval)).explode('l')[['level_0', 'l']]

Then just merge the mapping key against your metadata frame:

>>> more_metadata = metadata.merge(map1, left_on='ID', right_on='level_0', how='outer')
           ID  length     level_0         l
0   AP017903.1   99517  AP017903.1  BAX03457
1   AP017903.1   99517  AP017903.1  BAX03456
2   AP017903.1   99517  AP017903.1  BAX03455
3   AP017903.1   99517  AP017903.1  BAX03454
4   AP017903.1   99517  AP017903.1  BAX03457
5   AP017903.1   99517  AP017903.1  BAX03456
6   AP017903.1   99517  AP017903.1  BAX03455
7   AP017903.1   99517  AP017903.1  BAX03454
8     BK013208  102321    BK013208  BK013208
9     BK013208  102321    BK013208  BK013208
10    BK013208  102321    BK013208  BK013208
11    BK013208  102321    BK013208  BK013208
12    BK013208  102321    BK013208  BK013208
13    BK013208  102321    BK013208  BK013208
14    BK013208  102321    BK013208  BK013208
15    BK013208  102321    BK013208  BK013208

You can definitely improve on the intermediate column names by renaming them to things that are more valid for you. I don't know what these specific IDs represent, so I left them basically with defaults and placeholders.

Then you can get your lengths like so:

other_metadata = more_metadata[['length', 'l']] \
    .drop_duplicates().set_index('l')['length']
df['qseqid_len'] = df['qseqid'].str.replace('[\._].*$', '', regex=True) \
    .map(other_metadata)
df['sseqid_len'] = df['sseqid'].str.replace('[\._].*$', '', regex=True) \
    .map(other_metadata)

Thus:

       qseqid      sseqid  qseqid_length  sseqid_length  qseqid_len  sseqid_len
0  BAX03457.1  BK013208_1        99517.0       102321.0       99517      102321
1  BAX03457.1  BK013208_2        99517.0       102321.0       99517      102321
2  BAX03456.1  BK013208_3        99517.0       102321.0       99517      102321
3  BAX03455.1  BK013208_4        99517.0       102321.0       99517      102321

I added code to identify whether your values are strings or lists and then build reversed dict accordingly. So it should work either way.

First you need to extract the parts that are before ., _ using str.extract and a regex and then series.map.

Code:

reversed_dict = {}
is_value_str = isinstance(next(iter(map_dict.values())), str)
if is_value_str:
    from ast import literal_eval
    reversed_dict = {e: k for k, v in map_dict.items() for e in literal_eval(v)}
else:
    reversed_dict = {e: k for k, v in map_dict.items() for e in v}
get_length = (
    lambda s: s.str.extract(r"(.*?)[\._]", expand=False)
    .map(reversed_dict)
    .map(metadata.set_index("ID")["length"])
)
cols = df.filter(regex="seqid").columns
length_cols = cols.map("{}_length".format)
df[length_cols] = df[cols].apply(get_length)


print(df)

       qseqid      sseqid  qseqid_length  sseqid_length
0  BAX03457.1  BK013208_1          99517         102321
1  BAX03457.1  BK013208_2          99517         102321
2  BAX03456.1  BK013208_3          99517         102321
3  BAX03455.1  BK013208_4          99517         102321