7

I'm stuck creating an awk script that prepares a csv file before analysis. I need to create an output file with columns 1-2, 10, 13-15, 19-21. Also I need to replace the numbers on column 2 to the days of the week (so, 1 = Monday, 2 = Tuesday...) and convert the 21th column from nautical miles to km; and delete ""of columns 10, 13 and 14.

Input:

"DAY_OF_MONTH","DAY_OF_WEEK","OP_UNIQUE_CARRIER","OP_CARRIER_AIRLINE_ID","OP_CARRIER","TAIL_NUM","OP_CARRIER_FL_NUM","ORIGIN_AIRPORT_ID","ORIGIN_AIRPORT_SEQ_ID","ORIGIN","DEST_AIRPORT_ID","DEST_AIRPORT_SEQ_ID","DEST","DEP_TIME","DEP_DEL15","DEP_TIME_BLK","ARR_TIME","ARR_DEL15","CANCELLED","DIVERTED","DISTANCE",
1,2,"EV",20366,"EV","N48901","4397",13930,1393007,"ORD",11977,1197705,"GRB","1003",0.00,"1000-1059","1117",0.00,0.00,0.00,174.00,
1,2,"EV",20366,"EV","N16976","4401",15370,1537002,"TUL",13930,1393007,"ORD","1027",0.00,"1000-1059","1216",0.00,0.00,0.00,585.00,
1,2,"EV",20366,"EV","N12167","4404",11618,1161802,"EWR",15412,1541205,"TYS","1848",0.00,"1800-1859","2120",0.00,0.00,0.00,631.00,

Output:

"DAY_OF_MONTH","DAY_OF_WEEK","ORIGIN","DEST","DEP_TIME","DEP_DEL15","CANCELLED","DIVERTED","DISTANCE"
1,Tuesday,ORD,GRB,1003,0.00,0.00,0.00,322.248
1,Tuesday,TUL,ORD,1027,0.00,0.00,0.00,1083.42
1,Tuesday,EWR,TYS,1848,0.00,0.00,0.00,1168.61

So far, I've got the command to take the columns needed:

cut -d "," -f1-2,10,13-15,19-21 'Jan_2020_ontime.csv' > 'flights_jan_20.csv'

And also the code to replace the numbers in column 2 with their respective days of the week:

awk 'BEGIN {FS = OFS = ","} 
     $2 == 1 {$2 = "Monday"} 
     $2 == 2 {$2 = "Tuesday"} 
     $2 == 3 {$2 = "Wednesday"} 
     $2 == 4 {$2 = "Thursday"} 
     $2 == 5 {$2 = "Friday"} 
     $2 == 6 {$2 = "Saturday"} 
     $2 == 7 {$2 = "Sunday"} 
     {print}' 
file.csv

I am also missing a way to wrap all the code into the script to execute it later.

4
  • 1
    Does your data contain embedded commas, newlines or double quotes?
    – Kusalananda
    May 24 at 20:50
  • Are your formula for Km->Miles conversion right? Or you should divide KM/1.852? May 24 at 23:15
  • 1
    @YetAnotherUser it's converting from nautical miles to kilometres, not the other way around.
    – terdon
    May 25 at 9:41
  • Much of this seems much better to do in whatever you're using to do the analysis, why did you choose to use awk? May 25 at 20:03
14
awk '
    BEGIN {
        split("Monday Tuesday Wednesday Thursday Friday Saturday Sunday",days)
        FS=OFS=","
    }
    NR > 1 {
        gsub(/"/,"")
        $2 = days[$2]
        $21 *= 1.852
    }
    { print $1, $2, $10, $13, $14, $15, $19, $20, $21 }
' file
"DAY_OF_MONTH","DAY_OF_WEEK","ORIGIN","DEST","DEP_TIME","DEP_DEL15","CANCELLED","DIVERTED","DISTANCE"
1,Tuesday,ORD,GRB,1003,0.00,0.00,0.00,322.248
1,Tuesday,TUL,ORD,1027,0.00,0.00,0.00,1083.42
1,Tuesday,EWR,TYS,1848,0.00,0.00,0.00,1168.61
10
#!/bin/awk -f
BEGIN {
    dow[1] = "Monday"
    dow[2] = "Tuesday"
    dow[3] = "Wednesday"
    dow[4] = "Thursday"
    dow[5] = "Friday"
    dow[6] = "Saturday"
    dow[7] = "Sunday"

    FS=OFS=","
}

NR == 1 {print $1, $2, $10, $13, $14, $15, $19, $20, $21}

NR != 1 {
    $2 = dow[$2]
    $21 *= 1.852
    gsub(/"/, "", $10)
    gsub(/"/, "", $13)
    gsub(/"/, "", $14)
    print $1, $2, $10, $13, $14, $15, $19, $20, $21
}

Save this in a file, say: sample.awk. Make it executable: chmod +x sample.awk and run as ./sample.awk data.

To save the output in another file add the output redirection operator as follow : ./sample.awk data > out.csv

3
  • how would I save the output to a file named: flights_jan_20.csv ??
    – Llem Colop
    May 25 at 16:11
  • 1
    @LlemColop see the edit May 25 at 16:56
  • Thanks, worked like a charm!
    – Llem Colop
    May 25 at 17:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.