awk output to file based on filter

user_9582610 0
Viewed 105

I have a big CSV file that I need to cut into different pieces based on the value in one of the columns. My input file dataset.csv is something like this:

NOTE: edited to clarify that data is ,data, no spaces.

action,action_type, Result
up,1,stringA
down,1,strinB
left,2,stringC

So, to split by action_type I simply do (I need the whole matching line in the resulting file):

awk -F, '$2 ~ /^1$/ {print}' dataset.csv >> 1_dataset.csv
awk -F, '$2 ~ /^2$/ {print}' dataset.csv >> 2_dataset.csv

This works as expected but I am basicaly travesing my original dataset twice. My original dataset is about 5GB and I have 30 action_type categories. I need to do this everyday, so, I need to script the thing to run on its own efficiently.

I tried the following but it does not work:

# This is a file called myFilter.awk

{
action_type=$2;
if (action_type=="1") print $0 >> 1_dataset.csv;
else if (action_type=="2") print $0 >> 2_dataset.csv;
}

Then I run it as:

awk -f myFilter.awk dataset.csv

But I get nothing. Literally nothing, no even errors. Which sort of tell me that my code is simply not matching anything or my print / pipe statement is wrong.

if-statement filter csv awk bash
3 Answers

You may try this awk to do this in a single command:

awk -F, 'NR > 1{fn = $2 "_dataset.csv"; print >> fn; close(fn)}' file
user_5482250

With GNU awk to handle many concurrently open files and without replicating the header line in each output file:

awk -F',' '{print > ($2 "_dataset.csv")}' dataset.csv

or if you also want the header line to show up in each output file then with GNU awk:

awk -F',' '
    NR==1 { hdr = $0; next }
    !seen[$2]++ { print hdr > ($2 "_dataset.csv") }
    { print > ($2 "_dataset.csv") }
' dataset.csv

or the same with any awk:

awk -F',' '
    NR==1 { hdr = $0; next }
    { out = $2 "_dataset.csv" }
    !seen[$2]++ { print hdr > out }
    { print >> out; close(out) }
' dataset.csv
user_17450010

As currently coded the input field separator has not been defined.

Current:

$ cat myfilter.awk
{
action_type=$2;
if (action_type=="1") print $0 >> 1_dataset.csv;
else if (action_type=="2") print $0 >> 2_dataset.csv;
}

Invocation:

$ awk -f myfilter.awk dataset.csv

There are a couple ways to address this:

$ awk -v FS="," -f myfilter.awk dataset.csv

or

$ cat myfilter.awk
BEGIN {FS=","}
{
action_type=$2
if (action_type=="1") print $0 >> 1_dataset.csv;
else if (action_type=="2") print $0 >> 2_dataset.csv;
}

$ awk -f myfilter.awk dataset.csv
user_73661000
Related