1

(Note: this question is heavily shortened because the detailed question was blocked with "This looks like spam.")

I'm having a hard time adding missing lines to a series of data in a semicolon separated csv-file with awk/gawk.

Sample data (csv)

date;city;gender;status;value
2019-10;New York City;women;AL;5
2019-10;New York City;women;SC;2
2019-10;New York City;men;AL;3
2019-10;New York City;men;SC;1
2019-12;New York City;men;AL;5
2019-12;New York City;men;SC;3
2020-01;New York City;women;AL;8
2020-01;New York City;men;SC;2

Goal
For every year (beginning with the first year that appears in the file (here "2019") and ending with the last year that appears in the file (here "2020")) and month (beginning with the first month that appears in the file (here "10") and ending with the last month that appears in the file (here "01")) there should be 4 lines.

The example data for the month "2019-10" show a correct occurence of the data I need:
4 lines for each month of a year, 2 for women, 2 for men each with status AL and SC.

Desired output

date;city;gender;status;value
2019-10;New York City;women;AL;5
2019-10;New York City;women;SC;2
2019-10;New York City;men;AL;3
2019-10;New York City;men;SC;1
2019-11;New York City;women;AL;0
2019-11;New York City;women;SC;0
2019-11;New York City;men;AL;0
2019-11;New York City;men;SC;0
2019-12;New York City;women;AL;0
2019-12;New York City;women;SC;0
2019-12;New York City;men;AL;5
2019-12;New York City;men;SC;3
2020-01;New York City;women;AL;8
2020-01;New York City;women;SC;0
2020-01;New York City;men;AL;0
2020-01;New York City;men;SC;2

Missing lines should get value 0. The original data also includes occurences without a month-line (either for women or men or even no line is delivered).

Any help is greatly appreciated.
As this is my first post on this platform and I'm not natively english speaking please forgive any mistakes I made.

2
  • Does the value of "city" ever change in your data?
    – Ed Morton
    Jan 27 at 21:50
  • No, the "city"-value does not change. Jan 27 at 21:51

1 Answer 1

0

Using any awk in any shell on every Unix box:

$ cat tst.awk
BEGIN {
    FS=OFS=SUBSEP=";"
    split("women" FS "men", genders)
    split("AL" FS "SC", statuses)
}
NR == 1 {
    print
    next
}
{
    vals[$1,$2,$3,$4] = $5
    if ( NR == 2 ) {
        begDate = $1
        city = $2
    }
    endDate = $1
}
END {
    split(begDate,begYm,/-/)
    split(endDate,endYm,/-/)
    for ( yr=begYm[1]; yr<=endYm[1]; yr++ ) {
        begMth = ( yr == begYm[1] ? begYm[2] : 1 )
        endMth = ( yr == endYm[1] ? endYm[2] : 12 )
        for ( mth=begMth; mth<=endMth; mth++ ) {
            date = sprintf("%04d-%02d", yr, mth)
            for ( i=1; i in genders; i++ ) {
                for ( j=1; j in statuses; j++ ) {
                    idx = date FS city FS genders[i] FS statuses[j]
                    print idx, vals[idx]+0
                }
            }
        }
    }
}

$ awk -f tst.awk file
date;city;gender;status;value
2019-10;New York City;women;AL;5
2019-10;New York City;women;SC;2
2019-10;New York City;men;AL;3
2019-10;New York City;men;SC;1
2019-11;New York City;women;AL;0
2019-11;New York City;women;SC;0
2019-11;New York City;men;AL;0
2019-11;New York City;men;SC;0
2019-12;New York City;women;AL;0
2019-12;New York City;women;SC;0
2019-12;New York City;men;AL;5
2019-12;New York City;men;SC;3
2020-01;New York City;women;AL;8
2020-01;New York City;women;SC;0
2020-01;New York City;men;AL;0
2020-01;New York City;men;SC;2

Note that the above will work even if your "city" value contains a -, e.g. Washington-on-the-Brazos, because I do not include - in the FS values and instead call split() in the END section to separate the date into year and month.

1
  • 1
    Ed, I can't thank you enough! This solution works like a charm. Sending you some coffees... Jan 28 at 12:56

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.