I have an enterprise tool that exports files from a system with relative paths. I want to use the exported data as an import in a Python script to rename files. The data export looks like this:

DOCID   FILENAME    FILE_PATH
SS-WS-000471    2019-01 Civic CM #20-14-01.pdf  .\VOL01\NATIVES\NATIVE001\WS-000471.pdf
SS-WS-000472    2019-01 Civic CM #20-14-02.pdf  .\VOL01\NATIVES\NATIVE001\WS-000472.pdf

I am trying to change the relative path in the FILE_PATH column to an absolute path. I've tried using replace to replace the .\ with the directory extracted from the imported file. I've searched extensively online and have tried many things, but can't quite get this to work.

The result I want is to change the .\ to X:\NYC\Schley\3223\ which is the directory structure containing the \VOL01\NATIVES...

My code is below.

import os
import pathlib
import pandas as pd

#input file
infile = r'X:\NYC\Schley\3223\20220107 Test_export.csv'

#get filepath from input file
p = os.path.dirname(infile)

#create dataframe from input file
xref = pd.read_csv(infile)

#replace .\ with path information
xref['FILE_PATH'] = xref['FILE_PATH'].apply(lambda x: x.replace('\.\\\\', p))

Any suggestions would be appreciated.

CodePudding user response：

You can use pathlib

file_dir = pathlib.Path("X:\\NYC\\Schley\\3223\\") # you can use pathlib.Path().cwd

df['FILE_PATH'] = df['FILE_PATH'].apply(lambda x: pathlib.Path(file_dir, x).absolute())

Advantages:

1. Works on both Windows and Unix.
2. Handles various forms of path prefixes.
3. Handles leading and trailing slashes.

CodePudding user response：

Use str.replace:

df['FILE_PATH'] = df['FILE_PATH'].str.replace(r'^\.', r'X:\\NYC\\Schley\\3223', regex=True)
print(df)

# Output
          DOCID                        FILENAME                                          FILE_PATH
0  SS-WS-000471  2019-01 Civic CM #20-14-01.pdf  X:\NYC\Schley\3223\VOL01\NATIVES\NATIVE001\WS-...
1  SS-WS-000472  2019-01 Civic CM #20-14-02.pdf  X:\NYC\Schley\3223\VOL01\NATIVES\NATIVE001\WS-...