1

I am new to the use of the bash shell and all the unix commands. So I hope my question is not too stupid, I could not find anything in the previously asked questions to help me solving my problem.

I created a file that looks like this:

apple:::NN \t garden:::NN \t 5
garden:::NN \t great:::Adj \t 1
nice:::Adj \t a:::DT \t 2
etc

Now I have another file:

apple:::NN \t garden:::NN \t 15
house:::NN \t nice:::Adj \t 1
garden:::NN \t great:::Adj \t 5
etc

I need to combine the files, so the merged output file would look like this:

apple:::NN \t garden:::NN \t 20
garden:::NN \t great:::Adj \t 6
nice:::Adj \t a:::DT \t 2
house:::NN \t nice:::Adj \t 1

The problem is that the lines are not the same in both files, so I cannot iterate through the files line by line. I can split the lines of course, however I have to consider column 1 and column 2 as a unit to be able to add the numbers if column 1 and column 2 are the same in both files. Lines that are just found in 1 file have to go to the output file as they are.

I can do it manually using 'awk' or 'grep' but is it possible to also do that in a loop? If someone has a hint to solve the 'line' problem, that would help me a lot already!

The closest solution I could get to, is the following Merge files using a common column However, I could not get the join command using 2 columns plus adding up the values of the 3rd column.

I really appreciate any help!

  • This is on-topic here, but I just want to mention that you may get a better answer at StackOverflow.com since it's more focused on programming. – wjandrea Dec 1 '16 at 18:06
  • Are the cells of the first and second columns all unique relative to their own column? – wjandrea Dec 1 '16 at 18:11
  • What do the ` \t ` sequences mean? are you trying to indicate that your data are tab-delimited? – steeldriver Dec 1 '16 at 18:22
  • @wjandrea no they are not. There are multiple lines in the column containing these words. It is just the combination of the 2 columns that is unique in each of the whole files. – dani_anyman Dec 1 '16 at 18:33
  • @ steeldriver ja, that is what I was trying to indicate. – dani_anyman Dec 1 '16 at 18:34
1

The "classic" solution for this kind of thing uses an associative array in awk:

$ awk 'BEGIN{FS="\t"; OFS=FS} {a[$1 FS $2] += $3;} END {for (i in a) print i, a[i]}' file1 file2
nice:::Adj      a:::DT  2
house:::NN      nice:::Adj      1
apple:::NN      garden:::NN     20
garden:::NN     great:::Adj     6

(note that the output order is not guaranteed). A similar algorithm can be implemented in perl using a hash.

A newer tool that you may wish to try out is GNU datamash which allows grouping by fields and various mathematical operations on the results e.g.

$ cat file1 file2 | datamash -s groupby 1,2 sum 3 
apple:::NN      garden:::NN     20
garden:::NN     great:::Adj     6
house:::NN      nice:::Adj      1
nice:::Adj      a:::DT  2
|improve this answer|||||

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.