How to create folders based on part of a filename and also extract & rename specific columns using awk

Question

the following code extracts data from csv files contained in a folder, creates new folders (based on filename and date), creates and stores multiple csv files in the said folders. more info on this is here how-to-split-csv-file-and-create-multiple-csv-files-based-on-a-column.

gawk -F, '
BEGIN{ start=strftime("%Y %m %d 00 00 00", systime()-86400);
       for(min=0; min<1440; min++)
           timestamp[strftime("%F %H:%M", mktime(start)+min*60)]
     }

{ gsub(/"/,"") }

FNR==1{
       hdr=$0; yday=strftime("%Y%m%d", systime()-86400);
       fname=FILENAME; sub(/.csv$/,"", fname); dirName=fname"_"yday;
       system("mkdir "dirName); next
     }

(substr($1,1,16) in  timestamp){
       cp=$1; gsub(/[-: ]|00$/, "", cp);
       print hdr ORS $0 >(dirName"/"cp".csv");
       close(dirName"/"cp".csv");
       delete  timestamp[substr($1,1,16)] }

ENDFILE{ for (x in  timestamp){
             cpx=x; gsub(/[-: ]/, "", cpx);
             print hdr ORS x ",0,0,0,0" >(dirName"/"cpx".csv");
             close(dirName"/"cpx".csv")
     }
}' multiple*.csv

1. how can I use part of a filename to create a folder (within awk) e.g. filename is Thaban_TD_xxxxxx_Forms1.csv and want to create a folder named xxxxxx_date. In its current form, the code above create folders with names based on full csv file name. the length of xxxxxx varies but the file name format is always the same.

2. I'd also like to extract Timestamp, Data2, Data4 columns, rename 'Data2' to 'Info' and 'Data4' to 'Output', and then round off data in Info and Output columns to 3 decimal digits. in the same code

input file: Thaban_TD_xxxxxx_Forms1.csv

TIMESTAMP,Data1,Data2,Data3,Data4
"2021-01-03 00:00:00",80953,3.243183,2.943338,358.0123
"2021-01-03 00:01:00",80954,2.173187,1.990327,344.5851
...
"2021-01-03 23:59:00",80957,4.04172,3.82053,355.5481
"2021-01-04 00:00:00",80955,3.700353,3.593842,346.2665
...
"2021-01-04 23:59:00",80956,3.125094,2.922542,350.9915
"2021-01-05 00:00:00",80957,4.04172,3.82053,355.5481
...
"2021-01-05 23:59:00",80956,3.125094,2.922542,350.9915
etc...

This is how I'd like my output to be

Output file 202101030000.csv inside created folder xxxxxx_20210103

TIMESTAMP,Info,Output
2021-01-03 00:00:00,3.243,358.012

...

Output file 202101032359.csv inside created folder xxxxxx_20210103

TIMESTAMP,Info,Output
2021-01-03 23:59:00,4.042,355.548

Answer 1

gawk -F, '
{ gsub(/"/,"") }

FNR==1{
       delete timestamp;
       start=strftime("%Y %m %d 00 00 00", systime()-86400);
       for(min=0; min<1440; min++)
           timestamp[strftime("%F %H:%M", mktime(start)+min*60)]
       $3="Info"; $5="Output"; hdr=$1 FS $3 FS $5; 
       yday=strftime("%Y%m%d", systime()-86400);
       fname=FILENAME; gsub(/Thaban_TD_|_.*\.csv$/,"", fname); dirName=fname"_"yday;
       system("mkdir "dirName); next
     }

(substr($1,1,16) in  timestamp){
       cp=$1; gsub(/[-: ]|00$/, "", cp);
       printf("%s%s,%.3f,%.3f\n", hdr ORS, $1, $3, $5)>(dirName"/"cp".csv");
       close(dirName"/"cp".csv");
       delete  timestamp[substr($1,1,16)] }

ENDFILE{ for (x in  timestamp){
             cpx=x; gsub(/[-: ]/, "", cpx);
             print hdr ORS x ",0,0" >(dirName"/"cpx".csv");
             close(dirName"/"cpx".csv")
     }
}' multiple*.csv

Things we updated here are as following:

• Updated gsub(/Thaban_TD_|_.*\.csv$/,"", fname)

  to remove Thaban_TD_ and _<anything>.csv parts from the fileName

• Added $3="Info"; $5="Output"

  to rename the column#3 to Info and column#5 to Output for the first line only (NR==1)

• Updated hdr=$1 FS $3 FS $5

  for header-line we only need columns #1, #3 and #5 (FS is the Field Separator and that is what we defined in -F,)

• Updated printf("%s%s,%.3f,%.3f\n", hdr ORS, $1, $3, $5)

  to output columns#3 and #5 with 3 decimal points %.3f.

• Updated print hdr ORS x ",0,0"

  reduce to output only 3 columns.