How to find the same lines and merge the values?

Question

I have the following tabular separated table:

NM_000057   0
NM_000059   0
NM_000060   0
NM_000061   0
NM_000062   0
NM_000063   0
NM_000063   0
NM_000063   3
NM_000063   2
NM_000063   0
NM_000063   0
NM_000063   0
NM_000064   0
NM_000065   0
NM_000066   0
NM_000067   0
NM_000068   0
NM_000069   0
NM_000070   0

I want to look for the first value, if there are more than one equal, I want to merge it and add the values from the second column. In the example:

NM_000057   0
NM_000059   0
NM_000060   0
NM_000061   0
NM_000062   0
**NM_000063 5**
NM_000064   0
NM_000065   0
NM_000066   0
NM_000067   0
NM_000068   0
NM_000069   0
NM_000070   0

Thank you!

Just a question to make sure: Where should resulting line go ? I see your entries are numbered. You want to preserve the order of lines ? Or can the new line be added last to file ? — Sergiy Kolodyazhnyy, Sep 26 '16 at 10:59
I would like to create a new file only with the unique entries and the sum of the values (which are in the second column) that are equally named. Preserving the order of lines would be nice too. — Joan Gibert Fernandez, Sep 26 '16 at 14:36

αғsнιη · Answer 1 · 2016-09-26T10:20:33.107

2

Use 'awk',

awk '{seen[$1]+=$2} END{for (x in seen) print x, seen[x]}' infile > outfile

In above awk command, main this 'seen[$1]+=$2' part do the job, the variable $1 as the key feild suming the value of second column when matched key seen.

And at the end, we are looping over seen array with x as variable and print the keys seen in first column then the sum result of each key by seen[x].

edited Sep 26 '16 at 10:20

answered Sep 26 '16 at 10:05

αғsнιη

35,092
41
129
192

Hi! Thank you! This command seems to only look at the first line of my file... – Joan Gibert Fernandez Sep 26 '16 at 10:51
@JoanGibertFernandez what kind of line endings does your file have (Linux / DOS / Mac)? – steeldriver Sep 26 '16 at 15:40
@steeldriver my output file only have the first line of the input file. In the example: NM_000057 0 – Joan Gibert Fernandez Sep 26 '16 at 16:35
@JoanGibertFernandez please run the command `file` on your **input** data file (e.g. `file input`). Does it reply something like `ASCII text, with CR line terminators`? – steeldriver Sep 26 '16 at 16:41
@steeldriver yes. It is ASCII text, with CR line terminators – Joan Gibert Fernandez Sep 26 '16 at 16:43
In that case, you either need to convert the file to standard Unix line endings (`LF` in place of `CR`) or tell `awk` to expect `CR` line endings e.g. `awk -v RS='\r' '{ ... }'` (if you want `CR` line endings in the *output*, change `ORS` correspondingly too). – steeldriver Sep 26 '16 at 16:47

steeldriver · Answer 2 · 2016-09-26T11:38:38.003

1

Having recently discovered GNU Datamash, I'm going to throw in

datamash groupby 1 sum 2 < input

If your data is not already sorted you may need to add the -s option, and if it is separated by other whitespace (instead of tabs), add -W

edited Sep 26 '16 at 11:38

answered Sep 26 '16 at 10:18

steeldriver

131,985
21
239
326

Hi! Thank you but it seems that I do not have datamash in my MacOS. Is it exclusive for Linux? – Joan Gibert Fernandez Sep 26 '16 at 14:24
@JoanGibertFernandez a version of it should be available via `brew` I think - see http://brewformulas.org/Datamash - or you can build it from source – steeldriver Sep 26 '16 at 15:39
@JoanGibertFernandez ... although you really shouldn't be asking OSX questions on AskUbuntu, for exactly the reason you have just discovered - the answers given may not always apply to your OS – steeldriver Sep 26 '16 at 16:49

How to find the same lines and merge the values?

2 Answers2