1

I have a bunch of input csv files (delimited with semi-colon ";" having the following format

YEAR;MONTH;DAY;RES1FILE1;RES2FILE1;RES3FILE1
1901;01;01;101;154;169
1901;01;02;146;174;136

The number of columns for each files is variable, meaning that some files could have 6 columns and some others 4.

I would like to paste each files into one big csv file (with ";" as a delimiter. My problem is that, in order to avoid redundancy, I would like to avoid pasting the first three column each time since for every files they are the same (YEAR;MONTH;DAY).

Therefore the output should look like this:

YEAR;MONTH;DAY;RES1FILE1;RES2FILE1;RES3FILE1;RES1FILE2;RES2FILE2
1901;01;01;101;154;169;185;165
1901;01;02;146;174;136;129;176

I am currently using the following command:

arr=( *_rcp8p5.csv )
paste "${arr[@]}" | cut -f-4,$(seq -s, 8 4 $((4*${#arr[@]}))) >out_rcp8p5.txt

But it is not working at all

  • 2
    What exactly do you mean by "paste"? Edit the question and add an example of the input and output. – Kusalananda Aug 9 '16 at 13:34
  • 1
    @Kusalananda - most likely OP wants to do the same (or similar) thing as here only this time the no. of columns in each file may vary. – don_crissti Aug 9 '16 at 13:51
  • What you need is the join command. Except you don't seem to have a primary key (unique identifier) for each record. I guess you want to use the line number for that. – Wildcard Aug 9 '16 at 21:44
2

I can think of two ways to approach this:

  1. implement your own 'paste' that skips the first three fields of all but the first file - for example

    awk -F\; '
      FNR==NR {
        a[FNR]=$0; next;
      }
      {
        for (i=4;i<=NF;i++) a[FNR] = sprintf("%s;%s", a[FNR], $i);
      } 
      END {
        for (n=1;n<=FNR;n++) print a[n];
      }' file*.csv
    
  2. paste the files together, then retain fields based on an indicator derived from the header row

    paste -d\; file*.csv | 
      perl -MList::MoreUtils=indexes -F\; -alne '
        @keep = indexes { $_ !~ /YEAR|MONTH|DAY/ } @F if $. == 1; 
        print join ";", @F[0..2,@keep]'
    

    (if you don't have the List::MoreUtils module, you should be able to implement the same functionality using perl's grep).

  • Both options work just perfectly! If you do have a little bit of time to explain the different command used it could be really great! Thank you very much. – steve Aug 12 '16 at 13:21

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.