add column, populate entire column with name of file (without the ".csv") -for multiple files in BaSH

Question

Caveat: Absolute beginner. I need to add a column to a .csv file where the column header can be "Name", but the entire column should be exactly the same -- the name of the file itself, the filename. Each file has only 3 variables now, but 2100 rows.

Example: For file "bcc1_45Fall_10010002.csv" this is what I have -

   HUC8       YEAR    RO_MM
   10010002   1961    74.7
   10010002   1962    69.1
   10010002   1963    52.0
   10010002   1964   130.7
   10010002   1965    32.2
   10010002   1966    85.4

This is what I want -

  NAME                   HUC8       YEAR    RO_MM
  bcc1_45Fall_10010002   10010002   1961    74.7
  bcc1_45Fall_10010002   10010002   1962    69.1
  bcc1_45Fall_10010002   10010002   1963    52.0
  bcc1_45Fall_10010002   10010002   1964   130.7
  bcc1_45Fall_10010002   10010002   1965    32.2
  bcc1_45Fall_10010002   10010002   1966    85.4

OR this -

  HUC8                    YEAR    RO_MM
  bcc1_45Fall_10010002    1961    74.7
  bcc1_45Fall_10010002    1962    69.1
  bcc1_45Fall_10010002    1963    52.0
  bcc1_45Fall_10010002    1964   130.7
  bcc1_45Fall_10010002    1965    32.2
  bcc1_45Fall_10010002    1966    85.4

If I could simply replace all the data in the "HUC8" column with the filename that would be perfect. It doesn't need to be an extra column.

I need to do this for many thousand files.

If I knew how to do the first part, I could then create a loop. But maybe there's even a better way?

I don't know where to begin.

Answer 1

Using awk and column:

$ awk '
  NR==1{ sub(/\.csv$/, "", FILENAME) } # remove .csv suffix from FILENAME
  NR>1{ $1=FILENAME }                  # replace the first field with filename
  1                                    # print record
' bcc1_45Fall_10010002.csv | column -t
HUC8                  YEAR  RO_MM
bcc1_45Fall_10010002  1961  74.7
bcc1_45Fall_10010002  1962  69.1
bcc1_45Fall_10010002  1963  52.0
bcc1_45Fall_10010002  1964  130.7
bcc1_45Fall_10010002  1965  32.2
bcc1_45Fall_10010002  1966  85.4

---

You could run this in a shell loop to save the modified files to directory modified_files:

mkdir modified_files &&
for i in *.csv; do
  awk 'NR==1{ sub(/\.csv$/, "", FILENAME) } NR>1{ $1=FILENAME }1' "$i" |
    column -t > "./modified_files/$i"
done

---

If you need to replace column HUC8 and this is not the first column, change the code to this:

awk -v search='HUC8' '
  NR==1{
    for(i=1;i<=NF;i++)
      if ($i==search){ fld=i; sub(/\.csv$/, "", FILENAME); break }
  }
  NR>1{ $fld=FILENAME }
  1
' file.csv | column -t

Answer 2

Using Miller, and assuming your files are "simple" CSV (no commas within fields and so on - you can change --csvlite to --csv if full RFC-4180 support is required)

$ cat bcc1_45Fall_10010002.csv
HUC8,YEAR,RO_MM
10010002,1961,74.7
10010002,1962,69.1
10010002,1963,52.0
10010002,1964,130.7
10010002,1965,32.2
10010002,1966,85.4

then

1. to replace the current HUC8 column:

    $ mlr --csvlite put -S '$HUC8 = substr(FILENAME,0,-5)' bcc1_45Fall_10010002.csv
    HUC8,YEAR,RO_MM
    bcc1_45Fall_10010002,1961,74.7
    bcc1_45Fall_10010002,1962,69.1
    bcc1_45Fall_10010002,1963,52.0
    bcc1_45Fall_10010002,1964,130.7
    bcc1_45Fall_10010002,1965,32.2
    bcc1_45Fall_10010002,1966,85.4
   

2. to add a separate Name column:

    $ mlr --csvlite put -S '$Name = substr(FILENAME,0,-5)' bcc1_45Fall_10010002.csv
    HUC8,YEAR,RO_MM,Name
    10010002,1961,74.7,bcc1_45Fall_10010002
    10010002,1962,69.1,bcc1_45Fall_10010002
    10010002,1963,52.0,bcc1_45Fall_10010002
    10010002,1964,130.7,bcc1_45Fall_10010002
    10010002,1965,32.2,bcc1_45Fall_10010002
    10010002,1966,85.4,bcc1_45Fall_10010002
   

3. to add a Name column as the first column:

    $ mlr --csvlite put -S '$Name = substr(FILENAME,0,-5)' then reorder -f Name bcc1_45Fall_10010002.csv
    Name,HUC8,YEAR,RO_MM
    bcc1_45Fall_10010002,10010002,1961,74.7
    bcc1_45Fall_10010002,10010002,1962,69.1
    bcc1_45Fall_10010002,10010002,1963,52.0
    bcc1_45Fall_10010002,10010002,1964,130.7
    bcc1_45Fall_10010002,10010002,1965,32.2
    bcc1_45Fall_10010002,10010002,1966,85.4
   

All the above write the result to standard output - to modify the file in-place, add the -I option. You can pass multiple files at once using shell globs ex. bcc*.csv or *.csv.

[When testing without -I the header line won't be repeated unless a new header is required as a result of record heterogeneity; however with -I an appropriate header will be added to each file.]

Answer 3

$ perl -lne 'BEGIN {$fnr=1};

             if ($fnr == 1) {
               ($fn = $ARGV) =~ s/\.[^.]+$//;
               print "NAME,$_"
             } else {
               print "$fn,$_"
             };

             $fnr++;

             if (eof) {$fnr=1}' *.csv

This will add the filename (without the .csv "extension") as the first field, and print the contents of the .csv files to stdout.

Unlike awk, perl doesn't keep track of the line count for each individual file (it tracks only the total line count, with the $. variable). This script maintains that count manually, first by setting variable $fnr in the BEGIN block, then incrementing it for each line read, and finally by resetting it back to 1 every time the end of a file is reached.

This is easily modified to append the filename as the last field instead of the first. e.g. change the two print statements to:

      print "$_,NAME"
and: 
      print "$_,$fn"

If you need to insert the filename field somewhere else in the line, rather than as the first field, you could use perl's splice function.

For example, the following inserts the filename as the third field (note that perl array indices start from zero, not 1, so the third field is $F[2], not $F[3]):

$ perl -F, -lne 'BEGIN {$fnr=1; $field_num=2};

             if ($fnr == 1) {
               ($fn = $ARGV) =~ s/\.[^.]+$//;
               splice @F, $field_num, 0, "NAME";
             } else {
               splice @F, $field_num, 0, $fn;
             };

             print join(",", @F);

             $fnr++;

             if (eof) {$fnr=1}' *.csv

This uses perl's -F option to set a comma as the field delimiter. This also enables perl's auto-split feature to automatically split the input line into an array called @F (this is similar to awk's default behaviour of auto-splitting the input line into $1, $2, $3, etc). Either the literal string "NAME" or the modified filename is spliced into @F, then the elements of the @F array are printed, joined with comma characters.

Finally, if you want to actually change the contents of the files, use perl's -i option. You can optionally keep a backup of the original file by using an "extension" with the -i option, e.g. rename filename.csv to filename.csv.orig with -iorig. For example:

perl -iorig -lne '......' *.csv

or

perl -iorig -F, -lne '......' *.csv

Answer 4

Then loop over filenames and print columns with awk

for f in *.csv;
do
    head -1 $f > out/$f
    cat $f | awk -v FIN=${f%.csv} 'NR>1 {print FIN, $2, $3}' >> out/$f
done

HUC8       YEAR    RO_MM
bcc1_45Fall_10010002 1961 74.7 
(...)