Bulk read multiple files & combine vertically (DataFrame)

0: Introduction

Do you handle ** multiple files ** when analyzing business data?

For example, consider the case where 12 files are prepared with ** 1 year's worth of data ** and ** (2019_01.csv, 2019_02.csv, .. .., 2019_12.csv) **. ..

It's a hassle to read and combine one by one. ..

Here's some useful code. (** This time, it is assumed that there are multiple files in the current directory. ** If they are in other directories, apply the specified directory to each argument.)

1: Get the current directory

import os

#Get current directory
current_dir = os.getcwd()
current_dir

#You can get the current directory in the following form
>>> /Users/user_name/Date_science
#Get the folder inside the current directory
os.listdir(current_dir)

#You can get all the files in the current directory in the following form
>>>["2019_01.csv","2019_02.csv","2019_03.csv","2019_04.csv",....,"2019_12.csv"]

2: Combine path and file name

tbl_order_file = os.path.join(current_dir,"2019_*.csv")
tbl_order_file

#"Wildcard" in the part of the file name corresponding to "~ month"*By specifying ", only the specified file can be displayed.
#See 3 for details.
>>>/Users/user_name/Date_science/2019_*.csv

3: Extract the specified file

#In the glob module, you can get the file pathname that matches the pattern specified in the argument.
import glob

tbl_order_files = glob.glob(tbl_order_file)
tbl_order_files

#I was able to collectively operate the specified files in the following form.
>>>['/Users/user_name/Date_science/2019_01.csv',
 '/Users/user_name/Date_science/2019_02.csv',
 '/Users/user_name/Date_science/2019_03.csv',
..................,
..................,
'/Users/user_name/Date_science/2019_12.csv']

4: Data frame join

all_df = pd.DataFrame()

for file in tbl_order_files:
    all_df = pd.read_csv(file)
    print(f"{file}:{len(order_data)}")
    all_df = pd.concat([all_df, order_data],ignore_index = True)

#Data frames can be combined vertically while outputting "which file" and "number of data" in the following form.
>>>
2019_01.csv:233301
2019_02.csv:233260
2019_03.csv:241139
.................,
.................,
2019_12.csv:241135

5: Summary

import os
import glob

current_dir = os.getcwd()
tbl_order_file = os.path.join(current_dir,"2019_*.csv")
tbl_order_files = glob.glob(tbl_order_file)
all_df = pd.DataFrame()

for file in tbl_order_files:
    all_df = pd.read_csv(file)
    print(f"{file}:{len(order_data)}")
    all_df = pd.concat([all_df, order_data],ignore_index = True)

Recommended Posts

Bulk read multiple files & combine vertically (DataFrame)
Combine multiple python files into one python file
[Python & Unix] Combine multiple PDF files into one.
Combine multiple Excel files loaded using pandas into one