0

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.

2
  • Is the file really comma separated - or separated by whitespace as shown in your examples? May 26, 2021 at 23:26
  • @steeldriver -a comma. I was just trying to make it more clear. May 27, 2021 at 0:17

4 Answers 4

2

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
8
  • Wow. This sounds like just what I want. I will perform it on a test folder first, of course, but would you be so kind as to explain it a little more? I tried googling NR and it said it was the number of read-write operations? This doesn't help me understand what I'm doing. You have commented the code well, I just don't know the commands and would like to learn. Thanks! May 27, 2021 at 0:46
  • Freddy - @Freddy -it worked GREAT for replacing the info in the "HUC8" column. Unfortunately, it deleted all the info in both other columns (but kept the headers). Simple fix? May 27, 2021 at 1:12
  • NR is the input record number that is currently processed in AWK, i.e. the line number, NF (used in the second example with the loop) is the number of fields of the current record/line and FILENAME is obviously the name of the current input file (see Built-in Variables in the GNU Awk manual). The sub(...) is a string function for text replacement and fields (columns) are prefixed with a $, i.e. $1 is the first field.
    – Freddy
    May 27, 2021 at 1:32
  • 1
    Since , is the field separator in your files, replace awk with awk -F, -v OFS=, to change the input and output field separator and remove | column -t since you don't need to format the output. Please post real data next time :)
    – Freddy
    May 27, 2021 at 1:33
  • I was trying to find a chat option so as not to have this take up unnecessary space, but... I don't know what you mean by "real data". Believe me, many of us new guys would like to "play by the rules" but we're so new, we have no idea. I kind of hate asking questions because I know I'm doing it wrong. But I need to have answers, so I persevere. If I knew, I would. May 27, 2021 at 1:46
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.]

2
$ 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
0

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 
(...)
1
  • Sorry, apparently I wasn't clear. When I said I had many thousands of files, I meant I was hoping not to have to type in their individual names. I'm looking for something like - in words because I don't know the code - "for any file in this folder, use the filename minus the .csv, to replace all the data objects in column "HUC8". May 27, 2021 at 0:23

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.