How to extract base path from DataFrame column of path strings

Question

There are several questions about string manipulation, but I can't find an answer which allows me to do the following—I thought it should have been simple...

I have a DataFrame which includes a column containing a filename and path

The following produces a representative example DataFrame:

df = pd.DataFrame({
    'root': {'1': 'C:\\folder1\\folder2\\folder3\\folder4\\filename.csv'}
})

                                              root
1  C:\folder1\folder2\folder3\folder4\filename.csv

I want to end up with just the 'filename' part of the string. There is a large number of rows and the path is not constant, so I can't use str.replace

I can strip out the rightmost '.csv' part like this:

df['root'] = df['root'].str.rstrip('.csv') 

                                          root
1  C:\folder1\folder2\folder3\folder4\filename

But I cannot make any of the methods I have read about work to remove the path part in the left side of the string.

How can I return just the 'filename' part of this path (string), given that the preceding elements of the path can change from record to record?

Answer 1

You can use the utilities in os.path to make this easier, namely splitext and basename:

>>> import os
>>> df["root"].apply(lambda x: os.path.splitext(os.path.basename(x))[0])
0    filename
Name: root, dtype: object

---

PS: rstrip doesn't work the way you think it does-- it removes those characters, not that substring. For example:

>>> "a11_vsc.csv".rstrip(".csv")
'a11_'

Answer 2

For recent Python, pathlib is recommended. The basename can be obtained by applying .stem as follows. In general, DataFrames often have multiple rows, so the examples below also use pandas .apply.

from pathlib import Path 

df['root'].apply(lambda x: Path(x).stem)
# Out[1]:
# 1    filename
# Name: root, dtype: object

If you want to include the extension, you can get it by applying .name.

df['root'].apply(lambda x: Path(x).name)
# Out[2]:
# 1    filename.csv
# Name: root, dtype: object

Answer 3

Presuming there is always at least a single depth in the path, we can split on the slashes, take the last element and then call rstrip on it:

In [9]:

df.root.str.split('\\').str[-1].str.rstrip('.csv')
Out[9]:
1    filename
Name: root, dtype: object

EDIT in light of what DSM has pointed out about rstrip, you could call split twice:

In [11]:

df.root.str.split('\\').str[-1].str.split('.').str[0]
Out[11]:
1    filename
Name: root, dtype: object

Answer 4

There is nothing whatsoever pandas-specific about this, it is basic path handling with os.path.

Second, Windows/DOS has been accepting / as a path separator for at least 10-15 years now. So you can and should write mypath = 'C:/folder1/folder2/folder3/folder4/filename.csv' As you noticed, using backslash makes your string-handling life difficult because it has to be escaped, and results in nastier code. Defining os.sep = r'\\' doesn't seem to work.

import os
os.path.basename(r'C:/folder1/folder2/folder3/folder4/filename.csv')
'filename.csv'

Now if you really want to insist on writing OS-specific code in your Python (though there's no reason at all to do this), you can use the little-known platform-specficic versions of os.path:

import ntpath  # Windows/DOS-specific versions of os.path
ntpath.basename(r'C:\folder1\folder2\folder3\folder4\filename.csv')
'filename.csv'