1

I have multiple large csv files in a folder and I am trying to rbind(concatenate) them into one csv. But, while doing this I want to make sure all column values go in an appropriate column after concatenating. I can't do this in R because of memory limitation. I am pretty much new to shell scripting and I know there might be some way to do that without taking all csv files into memory.

Eg.

> csv1
     A  B  C  D  E
     1  2  4  5  6
     4  5  7  8  9
     3  5  6  7  8
     2  3  4  5  8

> csv1
    C  B  E  D  A
    10 22 43 35 66
    14 15 37 48 99
    33 25 56 67 88

> Desired Output
         A  B  C  D  E
         1  2  4  5  6
         4  5  7  8  9
         3  5  6  7  8
         2  3  4  5  8
        66 22 10 35 43
        99 15 14 48 37
        88 25 33 67 56

My attempts:

I try to set column order in R for each file while saving and then use below code to concatenate. I want to know a way where I can do everything in linux shell.

My Attempt:

nawk 'FNR==1 && NR!=1{next;}{print}' *.csv > result.csv


Any help is highly appreciated.

Solution by RavindraSingh13-

awk '
BEGIN{
  PROCINFO["sorted_in"] = "@ind_num_asc"
}
FNR==1{
  for(i=1;i<=NF;i++){
     a[$i]=i};
  if(FNR==1 && FNR==NR){
     print};
  next
}
{
  for(j in a){
     printf("%s ",$a[j])}
  print ""
}
' csv1 csv2

But, in above solution it is skipping some rows while concatenating.

  • So... you have a solution that works for the example files you have provided, but does not work for your actual files? If that's the case, you need to figure out how your actual files differ from the examples - it's going to be hard for anyone here to guess – steeldriver Feb 15 '18 at 15:57
2

GNU awk solution. Though, it'll map all values in memory.

merge_by_headers.awk script:

#!/bin/awk -f

BEGIN{ PROCINFO["sorted_in"] = "@ind_str_asc" }
NR==1 || FNR==1{ 
    for (i=1; i<=NF; i++) map[i] = $i; 
    c = NF; next 
}
NR==FNR{ 
    row_cnt = NR-1; 
    for (i=1; i<=NF; i++) a[map[i]][row_cnt] = $i; 
    next 
}
{ 
    row_cnt += 1; 
    for (i=1; i<=NF; i++) a[map[i]][row_cnt] = $i; 
}
END{
    delete map; h = ""; 
    for (k in a) h = (h? h OFS:"")k; 
    print h; 
    for (i=1; i<=row_cnt; i++) { 
        cnt = c; 
        for (k in a) printf "%d%s", a[k][i], (--cnt? OFS : ORS) 
    }
}

Usage:

awk -f merge_by_headers.awk 1.csv 2.csv | column -t

The output:

A   B   C   D   E
1   2   4   5   6
4   5   7   8   9
3   5   6   7   8
2   3   4   5   8
66  22  10  35  43
99  15  14  48  37
88  25  33  67  56
  • sh: BEGIN{: command not found sh: -c: line 2: syntax error near unexpected token (' sh: -c: line 2: for (i=1; i<=NF; i++){ map[i] = $i; }' – Rushabh Feb 15 '18 at 17:10
  • I am getting above error – Rushabh Feb 15 '18 at 17:10
  • @Rushabh, note, this is GNU awk solution, check if you have a proper awk implementation installed – RomanPerekhrest Feb 15 '18 at 17:13
  • linux-aws-headers-4.4.0-1041 linux-aws-headers-4.4.0-1043 linux-aws-headers-4.4.0-1044 linux-headers-4.4.0-1041-aws linux-headers-4.4.0-1043-aws linux-headers-4.4.0-1044-aws linux-image-4.4.0-1041-aws linux-image-4.4.0-1043-aws linux-image-4.4.0-1044-aws – Rushabh Feb 15 '18 at 17:14
  • I reran sudo apt-get install gawk – Rushabh Feb 15 '18 at 17:15

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.