Adding an extra value into CSV data, according to filename

Question

Let's say i have the following type of filename formats : CO#ATH2000.dat , CO#MAR2000.dat

Each of these, have data like that following:

....   
"12-02-1984",3.8,4.1,3.8,3.8,3.8,3.7,4.1,4.3,3.8,4.1,5.0,4.8,4.5,4.3,4.3,4.3,4.1,4.5,4.3,4.3,4.3,4.5,4.3,4.1
"13-02-1984",3.7,4.3,4.3,4.3,4.1,4.3,4.5,4.8,4.8,5.0,5.2,5.0,5.2,5.2,5.2,4.8,4.8,4.8,4.8,4.8,4.8,4.8,4.5,4.3
"14-02-1984",3.8,4.1,3.8,3.8,3.8,3.8,3.8,4.2,4.5,4.5,4.1,3.6,3.6,3.4,3.4,3.2,3.4,3.2,3.2,3.2,2.9,2.7,2.5,2.2
"15-02-1984",2.2,2.2,2.0,2.0,2.0,1.8,2.1,2.6,2.6,2.5,2.4,2.4,2.4,2.5,2.7,2.7,2.6,2.6,2.7,2.6,2.8,2.8,2.8,2.8
..........

Now i also have the following .sh file that can merge ALL those .dat files into one single output .dat file.

for filename in `ls CO#*`; do
    cat $filename >> CO#combined.dat
done

Now here is the problem. I want inside CO#combined.dat, at each line, before the start of the values, to have a 'standard' value according to the filename-parameter. For example i want each file with ATH in its filename have 3, at the start of each line and with MAR in its filename have 22,.

So the CO#combined.dat should be something like this:

....   
3,"12-02-1984",3.8,4.1,3.8,3.8,3.8,3.7,4.1,4.3,3.8,4.1,5.0,4.8,4.5,4.3,4.3,4.3,4.1,4.5,4.3,4.3,4.3,4.5,4.3,4.1
3,"13-02-1984",3.7,4.3,4.3,4.3,4.1,4.3,4.5,4.8,4.8,5.0,5.2,5.0,5.2,5.2,5.2,4.8,4.8,4.8,4.8,4.8,4.8,4.8,4.5,4.3
20,"14-02-1984",3.8,4.1,3.8,3.8,3.8,3.8,3.8,4.2,4.5,4.5,4.1,3.6,3.6,3.4,3.4,3.2,3.4,3.2,3.2,3.2,2.9,2.7,2.5,2.2
20,"15-02-1984",2.2,2.2,2.0,2.0,2.0,1.8,2.1,2.6,2.6,2.5,2.4,2.4,2.4,2.5,2.7,2.7,2.6,2.6,2.7,2.6,2.8,2.8,2.8,2.8
..........

So in conclusion i want the script to do the above procedure!

Thanks in advance!

jas · Accepted Answer · 2016-03-24 19:00:48Z

2

With awk you can take advantage of the built-in FILENAME variable along with the fact that you can supply multiple files to a given invocation. awk processes each file in turn, setting FILENAME to the name of the file whose records are currently being read.

With that you can set your prefix according to whatever pattern you wish to search for in the file name. Finally you can print the prefix and the original record.

Here's a demonstration on simplified versions of your sample input:

$ cat CO\#ATH2000.dat 
1
2
3

$ cat CO\#MAR2000.dat
A
B
C

$ awk 'FILENAME ~ /MAR/ {pre=22} FILENAME ~ /ATH/ {pre=3} { print pre "," $0 }' CO*.dat
3,1
3,2
3,3
22,A
22,B
22,C

edited Mar 24, 2016 at 19:00

answered Mar 24, 2016 at 14:07

jas

10.9k2 gold badges33 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Benjamin W. Over a year ago

While this code may answer the question, it would be better to include some context, explaining how it works and when to use it. Code-only answers are not useful in the long run.

Sarriman Over a year ago

Works like a charm! :) Thank you!

karakfa · Accepted Answer · 2016-03-24 23:13:14Z

1

can be done simply

for f in CO#*; do 
      case ${f:3:3} in 
          ATH) k=3 ;; 
          *) k=22 ;; 
      esac; 
      sed "s/^/$k,/" $f >> all; 
done

${f:3:3} extract the code ATH or MAR from the filename it's bash substring function; case converts the code to numerical counterpart; sed insert the numerical value and comma at the beginning of each line.

edited Mar 24, 2016 at 23:13

answered Mar 24, 2016 at 14:11

karakfa

67.8k8 gold badges45 silver badges59 bronze badges

3 Comments

glenn jackman Over a year ago

Very nice. I would write: case $f in *ATH*) ... to meet the "each file with ATH in its filename" requirement.

glenn jackman Over a year ago

@sarriman, note that you should not iterate over ls, as karakfa illustrates -- mywiki.wooledge.org/ParsingLs

Sarriman Over a year ago

i dont understant completly this code i think... what case ${f:3:3} does...? and also what sed does..

Collectives™ on Stack Overflow

Adding an extra value into CSV data, according to filename

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related