Merge daily csv files into a single csv file
From raju
Objective
The idea here is to merge files such as foo_YYYYMMDD.csv to a single file foo_all.csv
Requirements
- Add a date column in the final data set to distinguish data between different files. The date is to be obtained by parsing the filename foo_YYYYMMDD.csv
Solution
>cat ~/x/merge_csv.zsh #! /bin/env zsh set -eu # set -x idir=/path/to/input/directory ifiles=($idir/foo_????????.csv) # This is useful to test the script on smaller dataset. Change the dates # accordingly. # ifiles=($idir/foo_2017011[789].csv) odir=/path/to/output/directory ofile="${odir}/foo_all.csv" printf "output wll be stored in $ofile\n" # get the header hdr_file=`ls ${ifiles[@]} | sort | head -n1` printf "getting headers from $hdr_file\n" # we will be adding a date column to the end of each line to distinguish # between data on different dates. So modify the header accordingly. head -n 1 $hdr_file | sed -e 's/$/,date/' > $ofile for i in `ls ${ifiles[@]} | sort` do fname=`basename $i` date=`echo $fname | cut -c 5-12` printf "merging file $i\n" tail -n +2 $i | sed -e "s/$/,$date/" >> $ofile done
keywords | append a string to each line of output from tail, shell script to aggregate daily data by adding a date column, shell script to add an additional column, store glob as a variable, specify file list order in the for loop of a shell script