two input files data processing

Question

I have two huge input files:

file1 contains 3 tabulator separated fields:

• field1: UID
• field2: username
• field3: Real Name

Example of file1:

644     u11496   Real name1
640     u1309    Real name2
641     u3007    Real name3
642     u3030    Real name4
643     u3112    Real name5
54      u0365 Real name6
55      u0613 Real name7
56      u1065 Real name8
57      u1550 Real name9

file2 with many lines each one with 3 tabulator separated fields:

• field1: Can be ignored for this case
• field2: Group name
• field3: Column containing comma-separated UIDs of users

Example of file2:

2       group1   14,730,748,733,746,761,757,766,735,760,747,738,752,737,758,755,734,754,764,334,335,336,337,41,338,339,39,340
6       group2        14
15      group3  14,667,683,713,730,707,748,733,746,761,680,694,757,766,717,735,760,747,704,738,752,737,715,688,681,700,692,758,755,714,734

I need to add to each user line in file1, 4th column (tab-separated) with comma-separated group names which a particular user is member of.

Answer 1

The following shell script (I'm using ksh) will create a third file as to your request.

while read U REST
do
  S="       "   # initialized with a tabulation
  G=""
  grep -E '[        ,]'"$U"'(,|$)' file2.txt | while read X GROUP USRLIST
  do
    G="$G$S$GROUP"
    S=","
  done
  echo "$U  $REST$G"  # tab between $U and $REST
done < file1.txt > file3.txt

with file1.txt as

644 u11496  Real name1
640 u1309   Real name2
641 u3007   Real name3
642 u3030   Real name4
643 u3112   Real name5
54  u0365   Real name6
55  u0613   Real name7
56  u1065   Real name8
57  u1550   Real name9
14  u14     Jules Ceasar

file2.txt as

2   group1  14,730,748,733,746,761,757,766,735,760,747,738,752,737,758,755,734,754,764,334,335,336,337,41,338,339,39,340
6   group2  14   
14  group6  667,683,641
15  group3  14,667,683,713,730,707,748,733,746,761,680,694,757,766,717,735,760,747,704,738,752,737,715,688,681,700,692,758,755,714,734

you will get file3.txt as

644 u11496  Real name1
640 u1309   Real name2
641 u3007   Real name3      group6
642 u3030   Real name4
643 u3112   Real name5
54  u0365   Real name6
55  u0613   Real name7
56  u1065   Real name8
57  u1550   Real name9
14  u14     Jules Ceasar    group1,group2,group3