Performing operations with AWK

Shell Programming and Scripting
1

Hi guys, I'm new at awk and I'm learning but I'm really lost at the below.

I have the following dataset:

"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,279.966
1,Tuesday,TUL,ORD,1027,0.00,0.00,0.00,941.265
7,Monday,AUS,MSY,0838,0.00,0.00,0.00,714.396
17,Thursday,AUS,SFO,0.00,0.00,0.00,2419.94

It continues with more registers.

Description of the attributes

  • DAY_OF_MONTH: Day of the month
  • DAY_OF_WEEK: Name of the day of week
  • ORIGIN: Departure airport
  • DEST: Destination airport
  • DEP_TIME: Departure time in hhmm format
  • DEP_DEL15: flight delay (1 there's delay, 0= no delay)
  • CANCELLED: cancelled flight (1 cancelled, 0= not cancelled)
  • DIVERTED: diverted flight (1 diversion 0= no diversion)
  • DISTANCE: distance between airports in km.

Problem
I need to compute the delays per day. I want to create a script that given the dataset, for each day of the week, it computes the percentage(%) of flights with delay, the total number of flights with delay and the total of flights.

Expected Output

Weekday, %delayed, delayed, total_flights
Tuesday,9.8751,9559,96799
Moday,9.86118,7558,76644
Friday,19.0201,12462,65520
Sunday,13.5649,11176,82389
Thursday,15.2873,15870,103812
Saturday,14.4269,11326,78506
Wednesday,13.439,13933,103676

Because I'm new at awk I'm having some problems to perform group by operations and I don't really know how to do it. Furthermore, I see the need of storing variables that will be used afterwards to perform more computations. For example, %delayed will be
computed like (delayed/total_flights)*100

Thank you very much!

2

I can't see how you get to your Expected Output from your given dataset. Where do you get the total_flights from?
Please show what code you've got so far, mayhap partly pseudo code to reveal your thoughts.

3

If a flight is either Cancelled or Diverted, does that also render it Delayed?

I'm not clear why your fields DEP_DEL15, CANCELLED and DIVERTED are formatted as real numbers with two decimal digits, if they can only take values 0 and 1.

Seems to me (from your Expected Output totals), that your file has over 600,000 records.

Your output only seems to depend on two of the fields. (Your first two questions for any problem should usually be: Do I have all the data I need to provide the output ? In that case, what is the rest of the junk there for ?)

(a) The values are grouped by $2 -- DAY_OF_WEEK.

(b) The "delayed" total is a total of $6 -- DEP_DEL15: you don't even need to test the value.

(c) The "total_flights" is a total count of the records for the day.

(d) The "%delayed" is 100.0 * delayed / total_flights for the day.

It happens in awk that you can index an array by a string (not just integers like most languages). So your grouping by DayOfWeek can just be:

{ Del15[$2] += $6; Total[$2]++; }

As your output is in no particular order, you can just iterate over the array indexes to retrieve the result at the end. It might be neater if you actually put the days in natural order (which I leave up to you).

END {
    .. print your new header here ..
    for (d in Total)
        .. print result line with formatting, using the values
               d, 100.0 * Del15[d] / Total[d], Del15[d] and Total[d] ..
}

You need to skip over the header line, by testing FNR. If you don't, your iteration will include an entry for "DAY_OF_WEEK".

4 
echo ""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,279.966
1,Tuesday,TUL,ORD,1027,0.00,0.00,0.00,941.265
7,Monday,AUS,MSY,0838,0.00,0.00,0.00,714.396
17,Thursday,AUS,SFO,0.00,0.00,0.00,2419.94" | awk -F , '

function printStats(day, total, delayed)
{
        if (total != 0)
                printf("%s,%f,%d,%d\n", day, delayed / total * 100.0, delayed, total);
        else
                printf("%s,%f,%d,%d\n", day, 0.0, delayed, total);
}

$1 ~ /[0-9]+/ {
        weekday = $2
        delay = $6
        can = $7
        if (can == 0.0)
        {
                if (delay != 0.0)
                {
                        delayCount[weekday]++
                }
                totalF[weekday]++
        }
}

END {
        for (day in totalF)
        {
                printStats(day, totalF[day],  delayCount[day])
        }

}'
5

total_flights is obtained from summing up all flights belonging to a day. In the dataset given, for Tuesday it should be 2. For Monday 1 and for Thursday 1.

6

No, cancelled or diverted flights do not influence at delayed flights.

7

This sounds very much homework/classwork, furthermore asking help using specific commands are typical students request, and so you are to post inadequate category and show or effort in resolving please read again the new rules
Closing thread till you give us an answer on what to do with this one ( closing permanently or moving it, moving it means also you show your code done so far ...)

8

I thought I could post this since no documentation is given to solve this. I wrote I'm new at awk, therefore, I was hoping to get some help here. Thanks.