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.

1
  • 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 Feb 15, 2018 at 15:57

1 Answer 1

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
8
  • 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; }' Feb 15, 2018 at 17:10
  • I am getting above error Feb 15, 2018 at 17:10
  • @Rushabh, note, this is GNU awk solution, check if you have a proper awk implementation installed Feb 15, 2018 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 Feb 15, 2018 at 17:14
  • I reran sudo apt-get install gawk Feb 15, 2018 at 17:15

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .