How to combine fields from two TXT files in awk

Question

I have two text files with content like this:

sum.txt

|Name       |Sum |

|User A     |100.0 |

|User B     |40.0 |

per_proj.txt

|Name   |Project   |Hours |

|User A |Project 1 |50.0 |

|User A |Project 2 |30.0 |

|User A |Project 3 |20.0 |

|User B |Project 1 |20.0 |

|User B |Project 2 |20.0 |

What I'd like to achieve is to merge these two files and get output like this:

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |Sum   | Project 1 | Project 2| Project 3|

|User A     |100.0 |50.0 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |30.0     &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |20.0 |

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;| Project 1 | Project 2| 

|User B     |100.0 |20.0 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;      |20.0   &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;   |

Can someone please share his/her knowledge on this one?

I was exercising with awk snippets that I found here (starting with How to merge two files using AWK?), but with no success :(

The last line in result should be: |User B |40.0 |20.0 |20.0 | — R L, Aug 08 '19 at 12:00
Can you post your code, please? The code on the linked answer will work, so there is probably just a small thing wrong. — Jon, Aug 08 '19 at 14:44
No, &nbps; were pathetic try of make table looks better, sorry. Thanks for the beautiful piece of code, Shawn. — R L, Aug 09 '19 at 11:17

score 0 · Answer 1 · answered Aug 09 '19 at 00:47

All you really need is your per_proj.txt file. Calculate the sums on the fly (Note: GNU awk specific):

$ gawk 'BEGIN { FS = OFS = "|" }
        NR > 1 { users[$2] += $4; projects[$3] = 1; projecttotals[$2, $3] += $4 }
        END {
          PROCINFO["sorted_in"] = "@ind_str_asc"
          printf "| Name | Sum |"
          for (p in projects) printf " %s |", p
          printf "\n"
          for (u in users) {
            printf "| %s | %.1f |", u, users[u]
            for (p in projects) printf " %.1f |", projecttotals[u, p]
            printf "\n"
          }
        }' per_proj.txt
| Name | Sum | Project 1  | Project 2  | Project 3  |
| User A  | 100.0 | 50.0 | 30.0 | 20.0 |
| User B  | 40.0 | 20.0 | 20.0 | 0.0 |

You might have to play around a bit to get the exact output format you want, but that should give you the idea.

How to combine fields from two TXT files in awk

1 Answers1