efficient way to join 65,000 .csv files

user_7331417 0
Viewed 136

I have say 65,000 .csv files that I need to work with in julia language.

The goal is to perform basic statistics on the data set.

I had some ways of joining all the data sets

#1 - set a common index and leftjoin() - perform statistics row wise 
#2 - vcat() the dataframes on top of each other - vertically stacked use group by

Eitherway the final data frames are very large ! and become slow in processing

Is there an efficient way of doing this ?

I thought of performing either #1 or #2 and splitting the joining operations in thirds, lets say after 20,000 joins save to .csv and operate in chunks then at the end join all 3 in one last operation.

Well not sure how to replicate making 65k .csv files but basically below I loop through the files in the directory, load the csv then vcat() to one df. Question more relating to if there is a better way to manage the size of the operation. vcat() makes something grow. Ahead of time maybe I can cycle through the .csv files, obtain file dimensions per .csv, initialize the full dataframe to final output size, then cycle through each .csv row by row and populate the initialized df.

using CSV
using DataFrames

# read all files in directory
csv_dir_tmax = cd(readdir, "C:/Users/andrew.bannerman/Desktop/Julia/scripts/GHCN data/ghcnd_all_csv/tmax")

# initialize outputs
tmax_all = DataFrame(Date = [], TMAX = [])
c=1
for c = 1:length(csv_dir_tmax)
    print("Starting csv file ", csv_dir_tmax[c]," - Iteration ",c,"\n")
        if c <= length(csv_dir_tmax)
    csv_tmax = CSV.read(join(["C:/Users/andrew.bannerman/Desktop/Julia/scripts/GHCN data/ghcnd_all_csv/tmax/", csv_dir_tmax[c]]), DataFrame, header=true)
        tmax_all = vcat(tmax_all, csv_tmax)
    end
end
julia dataframe csv
2 Answers

The following approach should be relatively efficient (assuming that data fits into memory):

tmax_all = reduce(vcat, [CSV.read("YOUR_DIR$x", DataFrame) for x in csv_dir_tmax])
user_12695670

initializing the final output to the total size of final output (like vcat() would finally build). Then populate it element wise seems to be working way better:

# get the dimensions of each .csv files
tmax_all_total_output_size = fill(0, size(csv_dir_tmax,1))
tmin_all_total_output_size = fill(0, size(csv_dir_tmin,1))
tavg_all_total_output_size = fill(0, size(csv_dir_tavg,1))
tmax_dim = Int64[]
tmin_dim = Int64[]
tavg_dim = Int64[]
c=1
for c = 1:length(csv_dir_tmin) # 47484 - last point
    print("Starting csv file ", csv_dir_tmin[c]," - Iteration ",c,"\n")
        if c <= length(csv_dir_tmax)
    tmax_csv = CSV.read(join(["C:/Users/andrew.bannerman/Desktop/Julia/scripts/GHCN data/ghcnd_all_csv/tmax/", csv_dir_tmax[c] ]), DataFrame, header=true)
    global tmax_dim = size(tmax_csv,1)
        tmax_all_total_output_size[c] = tmax_dim
    end
    if c <= length(csv_dir_tmin)
    tmin_csv = CSV.read(join(["C:/Users/andrew.bannerman/Desktop/Julia/scripts/GHCN data/ghcnd_all_csv/tmin/", csv_dir_tmin[c]]), DataFrame, header=true)
    global tmin_dim = size(tmin_csv,1)
        tmin_all_total_output_size[c] = tmin_dim
    end
    if c <= length(csv_dir_tavg)
    tavg_csv = CSV.read(join(["C:/Users/andrew.bannerman/Desktop/Julia/scripts/GHCN data/ghcnd_all_csv/tavg/", csv_dir_tavg[c]]), DataFrame, header=true)
    global tavg_dim = size(tavg_csv,1)
        tavg_all_total_output_size[c] = tavg_dim
    end
end

# sum total dimension of all .csv files
tmax_sum = sum(tmax_all_total_output_size)
tmin_sum = sum(tmin_all_total_output_size)
tavg_sum = sum(tavg_all_total_output_size)

# initialize final output to total final dimension
tmax_date_array = fill(Date("13000101", "yyyymmdd"),tmax_sum)
tmax_array = zeros(tmax_sum)
tmin_date_array = fill(Date("13000101", "yyyymmdd"),tmin_sum)
tmin_array = zeros(tmin_sum)
tavg_date_array = fill(Date("13000101", "yyyymmdd"),tavg_sum)
tavg_array = zeros(tavg_sum)

# initialize outputs
tmax_all = DataFrame(Date = tmax_date_array, TMAX = tmax_array)
tmin_all = DataFrame(Date = tmin_date_array, TMIN = tmin_array)
tavg_all = DataFrame(Date = tavg_date_array, TAVG = tavg_array)
tmax_count = 0
tmin_count = 0
tavg_count = 0

Then begin filling the initialized df.

user_73314170
Related