Concatenate multiple csv files from different folders into one csv file in python

Question

I am trying to concatenate multiple csv files into one file(about 30 files). All csv files are located in different folders.

However, I have encountered an error while appending all files together: OSError: Initializing from file failed

Here is my code:

import pandas
import glob
 
path = 'xxx'
target_folders=['Apples', 'Oranges', 'Bananas','Raspberry','Strawberry', 'Blackberry','Gooseberry','Liche']
output ='yyy'
path_list = []
for idx in target_folders:
    lst_of_files = glob.glob(path + idx +'\\*.csv')
    latest_files = max(lst_of_files, key=os.path.getmtime)
    path_list.append(latest_files)
    df_list = [] 
    for file in path_list: 
        df = pd.read_csv(file) 
        df_list.append(df) 
    final_df = df.append(df for df in df_list) 
    combined_csv = pd.concat([pd.read_csv(f) for f in latest_files])

    combined_csv.to_csv(output + "combined_csv.csv", index=False)

    OSError                                   Traceback (most recent call last)
    <ipython-input-126-677d09511b64> in <module>
  1 df_list = []
  2 for file in latest_files:
  ----> 3     df = pd.read_csv(file)
  4     df_list.append(df)
  5 final_df = df.append(df for df in df_list)

    OSError: Initializing from file failed

Whoa. 1. What OS are you running? 2. What is the path being passed to read_csv? 3. What version of Python are you running? 4. What version of Pandas are you using? — user17242583
– user17242583, Commented Dec 16, 2021 at 22:10

Thomas · Accepted Answer · 2022-06-29 09:07:58Z

1

This solution should work as a charm to you:

import pandas as pd
import pathlib

data_dir = '/Users/thomasbryan/projetos/blocklist/files/'
out_dir = '.'

list_files = []
for filename in pathlib.Path(data_dir).glob('**/*.csv'):
    list_files.append(filename)

df = pd.concat(map(pd.read_csv, list_files), ignore_index=True)
df.to_csv(pathlib.Path(out_dir) / 'combined_csv.csv', index=False)

answered Jun 29, 2022 at 9:07

Thomas

2,2766 gold badges32 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Corralien · Accepted Answer · 2021-12-16 22:44:13Z

0

Try to simplify your code:

import pandas as pd
import pathlib

data_dir = 'xxx'
out_dir = 'yyy'

data = []
for filename in pathlib.Path(data_dir).glob('**/*.csv'):
    df = pd.read_csv(filename)
    data.append(df)

df = pd.concat(df, ignore_index=True)
df.to_csv(pathlib.Path('out_dir') / 'combined_csv.csv', index=False)

answered Dec 16, 2021 at 22:44

Corralien

121k8 gold badges44 silver badges69 bronze badges

Comments

DaveB · Accepted Answer · 2021-12-16 23:25:57Z

0

Without seeing your CSV file it's hard to be sure, but I've come across this problem before with unusually formatted CSVs. The CSV parser may be having difficulty in determine the structure of the CSV files, separators etc.

Try df = pd.read_csv(file, engine = 'python')

From the docs: "The C engine is faster while the python engine is currently more feature-complete."

Try passing the engine = 'python' argument on reading a single CSV file and see if you get a successful read. That way you can narrow down the problem to either file reads or traversing the files.

edited Dec 16, 2021 at 23:25

answered Dec 16, 2021 at 22:12

DaveB

4522 silver badges7 bronze badges

2 Comments

Camilla Over a year ago

the files has just 2 columns, first column named ['Fruit'], second column ['Harvest'], the headers off all files in different folder is the same, but they all in different locations

DaveB Over a year ago

Hi Camilla, I've edited my answer - try the above. It's difficult to narrow down the problem without seeing your files but from your error, it's most likely due to CSV parsing.

Collectives™ on Stack Overflow

Concatenate multiple csv files from different folders into one csv file in python

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related