Search CSV from subdirectory and add folder name as a column

user_14752392 0
Viewed 371

I want read csv's from different sub-directories in my working directory to create a combined csv file. The combined csv should have a column containing the sub-directory name from which that particular csv was read from.

This is what I tried.

import os
import glob
import pandas as pd

all_filenames = [i for i in glob.glob('*/*.csv'),recursive=True)]
list_subfolder = [f.name for f in os.scandir(ride_path) if f.is_dir()]

df_list = []

for i in range(len(all_filenames)):
    dir_name = list_subfolder[i]
    current_csv = all_filenames[i]
    data = pd.read_csv(current_csv)
    data["sub_folder"]= dir_name
    df_list.append(data)

combined_df = pd.concat(df_list)
combined_df.to_csv("combined_csv.csv", index=False)

The problem is that, it adds sub-directories that does not have csvs' in them, which is wrong and problematic. What is the best way to this right.

pandas glob dataframe csv python
4 Answers

You can do this via pathlib module:

from pathlib import Path

inp_path =  Path('.') # specify the inp path. Here, ('.') means current working dir
df_list= []

for csv_file in inp_path.glob('**/*.csv'): # glob here will return generator obj which will yield csv file one by one
    df = pd.read_csv(csv_file)
    df['file_name'] = csv_file.parent # possible to get parent dir via pathlib
    df_list.append(df_list)


combined_df = pd.concat(df_list)
combined_df.to_csv("combined_csv.csv", index=False)

Note.

1- use csv_file.parent.name if you just need the name.

2- use csv_file.parent.absolute() if you want the full path of parent dir.

user_154380330

Another option with glob and os:

import os
import glob
import pandas as pd

df_list = []

for csv in glob.glob('**/*.csv', recursive=True):
    parent_folder = os.path.split(os.path.dirname(csv))[-1]
    df = pd.read_csv(csv)
    df['subfolder'] = parent_folder
    df_list.append(df)

combined_df = pd.concat(df_list)
combined_df.to_csv("combined_csv.csv", index=False)
user_154978880

You can use us os.path.split():

import os
import glob
import pandas as pd

all_filenames = [i for i in glob.glob("**/*.csv", recursive=True)]

df_list = []
for f in all_filenames:
    current_csv = f
    data = pd.read_csv(current_csv)
    data["sub_folder"] = os.path.split(f)[0]    # <-- [0] is directory [1] is filename
    df_list.append(data)

combined_df = pd.concat(df_list)
print(combined_df)
combined_df.to_csv("combined_csv.csv", index=False)
user_100359850

One-line method (adapted from the @nk03 answer).

import pandas as pd
import pathlib as pth

pd.concat([pd.read_csv(csvfile).assign(file_name=csvfile.parent)
           for csvfile in pth.Path(".").glob("**/*.csv")]) \
  .to_csv("combined_csv.csv", index=False)
user_152399510
Related