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_'