Argthtjtr

Data frame with 2 columns: old_path and new_path. Data frame can contain hundreds of rows.

The script iterates over a list of files.

For each file in the list, check if any part of its folder path matches a value in the old_path column. If there is a match, replace the file's matched old_path with the corresponding new_path value.

I achieved this with for index, row in df.iterrows(): or for row in df.itertuples():, but I'm thinking there should be a more efficient way to do it without having to use the second for loop.

Any help is appreciated. Sample below uses df.iterrows()

import pandas as pd

import os



df = pd.read_csv('path_lookup.csv')

# df:

#                                         old_path                      new_path

# 0               F:BusinessBudget & Forecasting  M:BusinessFinanceForecast

# 1                    F:BusinessTreasury Shared  M:BusinessFinanceTreasury

# 2                                        C:Temp                    C:NewTemp



excel_link_analysis_list = [

    {'excel_filename': 'C:\Temp\12345\Distribution Adjusted Claim.xlsx',

     'file_read': 'OK'},

    {'excel_filename': 'C:\Temp\SubFolder\cost estimates.xlsx',

     'file_read': 'OK'}

]



for i in excel_link_analysis_list:

    for index, row in df.iterrows():

        if row['old_path'].lower() in i['excel_filename'].lower():

            dest_path_and_file = i['excel_filename'].lower().replace(row['old_path'].lower(), 

                                                                     row['new_path'].lower())

            print(dest_path_and_file)

prints:

c:newtemp12345distribution adjusted claim.xlsx

c:newtempsubfoldercost estimates.xlsx

Yes, pandas has nice built in string comparison functions, see here: https://pandas.pydata.org/pandas-docs/stable/generated/pandas.Series.str.contains.html#pandas.Series.str.contains

This is how you could use Series.str.contains to get the index of the matching value (i.e. from the column old_path). You could then use that index to go back and get the value of new_path

Edit: updated to handle the case where new_path_matches has one value.

import pandas as pd



old_path = df['old_path']

new_path = df['new_path']



for filename in filenames:

    b = old_path.str.contains(filename)



    # Get the index of matches from `old_path` column

    indeces_of_matches = b[b].index.values    



    # use the index of matches to get the corresponding `new_path' values 

    new_path_matches = old_path.loc[indeces_of_matches]



    if (new_path_matches.value.shape[0]>0):

        print new_path_matches.values[0]   # print the new_path value