Split file on column values

From raju

Main

Objective

split the file below into multiple files based on the values in second column

    $cat pv.txt
    date|scen|val
    20181225|50|-138.4
    20181226|50|182.4
    20181227|51|809.2
    20181228|51|794.1
    20181229|53|-212.3
    20181230|53|-333.2
    20181231|53|-101.3
    20190101||512.3
    

Other requirements:

  1. Splitted files should
    • have the header
    • be named as scen_<foo>.txt where foo is the value in the second column. ex:- scen_50.txt, scen_53.txt
  2. For consistency, put all the rows with a missing second column into scen_.txt

Solution

Use awk

    awk -F\| '
    NR==1 {hdr=$0; next}
    {fn="scen_" $2 ".txt"}
    !seen[$2]++{print hdr > fn}
    {print > fn}' pv.txt
    

The command will produce

    $ls scen*
    scen_.txt  scen_50.txt  scen_51.txt  scen_53.txt
    

Contents of each scenario file

    $for i in scen_*.txt; do echo $i; cat $i; echo; done
    scen_.txt
    date|scen|val
    20190101||512.3
    
    scen_50.txt
    date|scen|val
    20181225|50|-138.4
    20181226|50|182.4
    
    scen_51.txt
    date|scen|val
    20181227|51|809.2
    20181228|51|794.1
    
    scen_53.txt
    date|scen|val
    20181229|53|-212.3
    20181230|53|-333.2
    20181231|53|-101.3
    

related links

This question was asked multiple times on stackoverflow.

Appendix

split without headers

The awk command simplifies a lot if the header is not required in the splitted files.

    awk -F\| '{fn="scen_" $2 ".txt"; print > fn}' pv.txt
    

Sample usage:

Delete scenario files from previous runs (if any)

    $rm -f scen_*
    removed 'scen_.txt'
    removed 'scen_50.txt'
    removed 'scen_51.txt'
    removed 'scen_53.txt'
    

Run it

    $awk -F\| '{fn="scen_" $2 ".txt"; print > fn}' pv.txt
    

Output

    $ls scen_*
    scen_.txt  scen_50.txt  scen_51.txt  scen_53.txt  scen_scen.txt
    
    $for i in scen_*.txt; do echo $i; cat $i; echo; done
    scen_.txt
    20190101||512.3
    
    scen_50.txt
    20181225|50|-138.4
    20181226|50|182.4
    
    scen_51.txt
    20181227|51|809.2
    20181228|51|794.1
    
    scen_53.txt
    20181229|53|-212.3
    20181231|53|-101.3
    20181230|53|-333.2
    
    scen_scen.txt
    date|scen|val