How to add name of csv files as values in a column while merging 1000+ files?
Solution 1:
If memory is the constraint, then one pandas
-based solution is to iterate over chunks of rows:
import os
import pandas as pd
print(pd.__version__)
# works with this version: '1.3.4'
# gen sample files
all_files = [f"{_}.csv" for _ in range(3)]
for filename in all_files:
df = pd.DataFrame(range(3))
df.to_csv(filename, index=False)
# combine into one
mode = "w"
header = True
for filename in all_files:
with pd.read_csv(
filename,
engine="python",
iterator=True,
chunksize=10_000,
) as reader:
for df in reader:
filename = os.path.basename(filename)
df["file_name"] = filename
df.to_csv("some_file.csv", index=False, mode=mode, header=header)
mode = "a"
header = False
Solution 2:
Another solution is to use dask
:
# pip install dask
import dask.dataframe as dd
# dd.read_csv is mostly compatible with pd.read_csv options
# so can specify reading specific columns, etc.
ddf = dd.read_csv("some_path/*.csv")
ddf.to_csv('merged_file.csv', index=False, single_file=True)
Solution 3:
The good old csv
module can process one row at a time, so memory will not be an issue. The following code will concatenate the csv files keeping only the first header, and adding a filename column populated with the filename.
path = r'path_to_files/'
all_files = glob.glob(path + "/*.csv")
import csv
with open('updated_thirteen_jan.csv','w', newline='') as wfd:
wr = csv.writer(wfd)
first = True
for f in all_files:
with open(f) as fd:
rd = csv.reader(fd)
# skip header line, except for the first file
row = next(rd)
if first:
row.append('filename')
wr.writerow(row)
first = False
for row in rd:
row.append(f)
wr.writerow(row)
Solution 4:
Read one file at a time into pandas dataframe, add new column to it and write it to a new file.
import os
import glob
import pathlib
path = 'path_to_files/'
out_file = 'updated_thirteen_jan.csv'
all_files = glob.glob(path + '*.csv')
all_files = sorted([pathlib.Path(i) for i in all_files])
keep_cols = ['list', 'of', 'columns', 'to', 'keep']
skip_row = 2 # number of rows to skip
for fn in all_files:
temp = pd.read_csv(fn, usecols=keep_cols, skiprows=skip_row)
temp['filename'] = fn.stem
temp.to_csv(out_file, mode='a', index=False, header=not os.path.isfile(out_file))
If reading entire csv into memory isn't feasible, then use chunksize. Modify this value per your machine capacity.
for fn in all_files:
reader = pd.read_csv(fn, usecols=keep_cols, skiprows=skip_row, chunksize=5000)
for idx, df in enumerate(reader):
df['filename'] = fn.stem
df.to_csv(out_file, mode='a', index=False, header=not os.path.isfile(out_file))