Best way to handle path with pandas

Question

When I have a pd.DataFrame with paths, I end up doing a lot of .map(lambda path: Path(path).{method_name}, or apply(axis=1) e.g:

(
    pd.DataFrame({'base_dir': ['dir_A', 'dir_B'], 'file_name': ['file_0', 'file_1']})
    .assign(full_path=lambda df: df.apply(lambda row: Path(row.base_dir) / row.file_name, axis=1))
)
  base_dir file_name     full_path
0    dir_A    file_0  dir_A/file_0
1    dir_B    file_1  dir_B/file_1

It seems odd to me especially because pathlib does implement / so that something like df.base_dir / df.file_name would be more pythonic and natural.

I have not found any path type implemented in pandas, is there something I am missing?

EDIT

I have found it may be better to once for all do sort of a astype(path) then at least for path concatenation with pathlib it is vectorized:

(
    pd.DataFrame({'base_dir': ['dir_A', 'dir_B'], 'file_name': ['file_0', 'file_1']})
    # this is where I would expect `astype({'base_dir': Path})`
    .assign(**{col_name:lambda df: df[col_name].map(Path) for col_name in ["base_dir", "file_name"]})
    .assign(full_path=lambda df: df.base_dir / df.file_name)
)

how about ```df['full_path'] = df['base_dir']+'/'+df['file_name']``` ? — Dennis Lyubyvy, May 06 '20 at 21:47
that's strange why w'd you not want to use apply. That's actually contrary to how one w'd think it. But otherwise, what's the problem with apply? Even in what I have done you can do apply(lambda x:os.path.join([x[i] for i in df.columns]) kind of stuff. — Vishesh Mangla, Jul 26 '20 at 15:32

score 1 · Answer 1 · answered May 11 '20 at 09:18

It seems like the easiest way would be:

df.base_dir.map(Path) / df.file_name.map(Path)

It saves the need for a lambda function, but you still need to map to 'Path'.

Alternatively, just do:

df.base_dir.str.cat(df.file_name, sep="/")

The latter won't work on Windows (who cares, right? :) but will probably run faster.

Vishesh Mangla · Answer 2 · 2020-07-26T17:36:53.040

0

import pandas as pd
import os
   
df = pd.DataFrame({"p1":["path1"],"p2":["path2"]})
df.apply(lambda x:os.path.join(x.p1, x.p2), axis=1)

Output:

0    path1\path2
dtype: object

Edit:

After being told to not use assign you can try this

See .to_json() docs

import os
import pandas as pd       
df = pd.DataFrame({"p1":["path1", "path3"],"p2":["path2", "path4"]})
print(df.to_json(orient="values"))

Output

[["path1","path2"],["path3","path4"]]

From here it's simple, just use map(lambda x:os.path.join(*x), ...) and you get a list of paths.

edited Jul 26 '20 at 17:36

answered Jun 20 '20 at 15:53

Vishesh Mangla

664
9
20

My previous answer was bad according to the docs. Never modify dataframe while iterating with iterrows. – Vishesh Mangla Jul 15 '20 at 08:22

score 0 · Answer 3 · answered Mar 07 '21 at 02:11

Using `pandas-path`

The pandas-path package the functionality you need and more. Just by importing, it adds a .path accessor to pd.Series and pd.Index to make pathlib methods available.

import pandas as pd
import pandas_path

df =  pd.DataFrame({'base_dir': ['dir_A', 'dir_B'], 'file_name': ['file_0', 'file_1']})

# .path accessor added by importing pandas_path
df.base_dir.path / df.file_name.path

#> 0    dir_A/file_0
#> 1    dir_B/file_1
#> dtype: object

^{Created at 2021-03-06 18:09:44 PST by reprexlite v0.4.2}

Best way to handle path with pandas

EDIT

3 Answers3

Using `pandas-path`

Linked

Best way to handle path with pandas

EDIT

3 Answers3

Using pandas-path

Linked

Using `pandas-path`