1

Suppose I have 3 dataframes (independent .txt files): 

        Acamar   Begalb     Caspri 
Acamar  0.1      0.0        6.3
Begalb  0.33     8.3        0.0
Caspri  1.4      0.617      2.4

        Acamar   Begalb    Caspri 
Acamar  0.5      3.2        0.1
Begalb  0.0      2.1        0.5
Caspri  4.2      0.0        1.5

        Acamar   Begalb    Caspri 
Acamar  0.5      3.2        0.1
Begalb  0.0      2.1        0.5
Caspri  4.2      0.0        1.5

I want to sum the column values across the 3 dataframes so that I get: 

           Acamar   Begalb    Caspri 
    Acamar  1.1      6.4        6.5
    Begalb  0.33     12.5       1
    Caspri  9.8      0.617      5.4

Using datamash like this: 

cat *.txt | datamash --headers groupby 1 sum 2-4

Populates all my columns with 0 and returns an error saying:

invalid numeric value in field 2: 'Acamar'

Does anyone know how to help, using datamash or something else?

Thanks a lot :)

Improve this question
0

1 Answer 1

Reset to default
0

This piece of code works:

paste -d"\n" *.txt |
    sed -r '1!{/^(\s+[[:alnum:]])+/d;}' |
    LC_ALL=C ./datamash-1.3/datamash --headers groupby 1 sum 2-4

Result:

GroupBy()       sum(Acamar)     sum(Begalb)     sum(Caspri)
Acamar  1.1     6.4     6.5
Begalb  0.33    12.5    1
Caspri  9.8     0.617   5.4

The main issue here is solved both by paste, sed line and LC_ALL value.

  • paste join files line by line (cat *.txt will join files one after the other, inserting header lines inside datas)
  • sed line removes header lines except the first occurrence (source)
    • I assumed that header lines are a sequence of blank characters (\s+) followed by alphanumeric characters ([[:alnum:]])+)
  • LC_ALL is a variable managing the locale, and will prevent any misinterpretation of the dot in values

Also add -W if the fields are separated with spaces rather than tabs.

Improve this answer
1
  • this is perfect! thank you very much for your help :) Commented Jan 10, 2020 at 14:48

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.