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