0

I am looking into automating some processes / calculations but I may first need to format a slightly awkward CSV file set. (For this I am using bash, as requested).

The csv file set follows (roughly) the format below

CODE,Sitting,Jan,Feb,Mar,Apr,May,Jun,Jul,Totals
CLLK_J9,First Sitting,,,2,5,2,,,10
,Second Sitting,,,,,,,,1
RTHM_A8,First Sitting,,,1,,3,,,6
,Second Sitting,,,,,1,,,1
FFBJ_FA9,First Sitting,,,,8,6,,,25
,Second Sitting,,,,,11,,,12
UUYIOR_HJ9,First Sitting,,,1,3,6,,,17
IKRO_Lk8,First Sitting,,,,3,3,,,37
,Second Sitting,,,,6,11,,,34

enter image description here

I am trying to fill in the empty fields in the column CODE with the content of the field from the previous line (ordinarily these empty fields occur next to a "second sitting" instance in column 2). So, for the above example, the result should be like

CODE,Sitting,Jan,Feb,Mar,Apr,May,Jun,Jul,Totals
CLLK_J9,First Sitting,,,2,5,2,,,10
CLLK_J9,Second Sitting,,,,,,,,1
etc.

I am starting to read some awk documentation as it seems a reasonably powerful utility for this task - but haven't made any progress yet. Thoughts?

ta

1
  • 1
    A textual representation of the file will help answerers test their solutions. Also, bash is a shell, not a text editor -- it can call the eventual solution. – Jeff Schaller Jul 30 '20 at 18:57
5

Using Miller (https://github.com/johnkerl/miller) is very simple. Running

mlr --csv fill-down -f CODE input.csv >output.csv

you will have

+------------+----------------+-----+-----+-----+-----+-----+-----+-----+--------+
| CODE       | Sitting        | Jan | Feb | Mar | Apr | May | Jun | Jul | Totals |
+------------+----------------+-----+-----+-----+-----+-----+-----+-----+--------+
| CLLK_J9    | First Sitting  | -   | -   | 2   | 5   | 2   | -   | -   | 10     |
| CLLK_J9    | Second Sitting | -   | -   | -   | -   | -   | -   | -   | 1      |
| RTHM_A8    | First Sitting  | -   | -   | 1   | -   | 3   | -   | -   | 6      |
| RTHM_A8    | Second Sitting | -   | -   | -   | -   | 1   | -   | -   | 1      |
| FFBJ_FA9   | First Sitting  | -   | -   | -   | 8   | 6   | -   | -   | 25     |
| FFBJ_FA9   | Second Sitting | -   | -   | -   | -   | 11  | -   | -   | 12     |
| UUYIOR_HJ9 | First Sitting  | -   | -   | 1   | 3   | 6   | -   | -   | 17     |
| IKRO_Lk8   | First Sitting  | -   | -   | -   | 3   | 3   | -   | -   | 37     |
| IKRO_Lk8   | Second Sitting | -   | -   | -   | 6   | 11  | -   | -   | 34     |
+------------+----------------+-----+-----+-----+-----+-----+-----+-----+--------+
2

If you want to try it with awk, the following should work:

awk -F',' -v OFS=',' 'FNR>1{if ($1!="") last=$1; else $1=last}1' input.csv

This will set input and output field separators to ,.

Then, for every line after the first line (FNR>1), it will check if the first column ($1) is empty. If it is not, the value will be stored in the variable last for later use. If it is empty, it will be filled with the previously stored value.

The 1 outside the rule block { ... } instructs awk to print the current line including all modifications made. Note that unless this command (or actually any boolean condition evaluating to true) is specified, or an explicit print/printf command is given inside a rule block, awk will not print the current line.

Result:

CODE,Sitting,Jan,Feb,Mar,Apr,May,Jun,Jul,Totals
CLLK_J9,First Sitting,,,2,5,2,,,10
CLLK_J9,Second Sitting,,,,,,,,1
RTHM_A8,First Sitting,,,1,,3,,,6
RTHM_A8,Second Sitting,,,,,1,,,1
FFBJ_FA9,First Sitting,,,,8,6,,,25
FFBJ_FA9,Second Sitting,,,,,11,,,12
UUYIOR_HJ9,First Sitting,,,1,3,6,,,17
IKRO_Lk8,First Sitting,,,,3,3,,,37
IKRO_Lk8,Second Sitting,,,,6,11,,,34
1

Not an AWK solution, but...

$ cat yourfile.csv | csv-sqlite 'select code, (select i2.code from input i2 where i2.code != "" and i2.rowid <= i1.rowid order by i2.rowid desc limit 1) as new_CODE, Sitting, Jan, Feb, Mar, Apr, May, Jun, Jul, Totals from input i1' -s
CODE         new_CODE     Sitting          Jan   Feb   Mar   Apr   May   Jun   Jul   Totals
CLLK_J9      CLLK_J9      First Sitting                2     5     2                 10
             CLLK_J9      Second Sitting                                             1
RTHM_A8      RTHM_A8      First Sitting                1           3                 6
             RTHM_A8      Second Sitting                           1                 1
FFBJ_FA9     FFBJ_FA9     First Sitting                      8     6                 25
             FFBJ_FA9     Second Sitting                           11                12
UUYIOR_HJ9   UUYIOR_HJ9   First Sitting                1     3     6                 17
IKRO_Lk8     IKRO_Lk8     First Sitting                      3     3                 37
             IKRO_Lk8     Second Sitting                     6     11                34

(if you'll drop "-s" it will come back as CSV)

csv-sqlite comes from https://github.com/mslusarz/csv-nix-tools

Query was stolen from this answer.

1

Using any awk in any shell on every UNIX box:

$ awk 'BEGIN{FS=OFS=","} $1==""{$1=p} {p=$1} 1' file
CODE,Sitting,Jan,Feb,Mar,Apr,May,Jun,Jul,Totals
CLLK_J9,First Sitting,,,2,5,2,,,10
CLLK_J9,Second Sitting,,,,,,,,1
RTHM_A8,First Sitting,,,1,,3,,,6
RTHM_A8,Second Sitting,,,,,1,,,1
FFBJ_FA9,First Sitting,,,,8,6,,,25
FFBJ_FA9,Second Sitting,,,,,11,,,12
UUYIOR_HJ9,First Sitting,,,1,3,6,,,17
IKRO_Lk8,First Sitting,,,,3,3,,,37
IKRO_Lk8,Second Sitting,,,,6,11,,,34

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.