CODEWITHSUNDEEP
awk
HollowBastion
HollowBastion

Reputation: 223

Counting the occurences of letters in a table with awk

I have a file similar to this one:

location1 person1 -
location2 person1 a
location3 person1 c
location2 person2 x
location1 person2 -
location3 person2 -

Each file has multiple entries for each person at different locations (notice the order of locations is not the same for each person). The third column is either a letter or '-'.

I have awk reading the file, and I want to produce a result table like so:

person location1 location2 location3
person1 0 1 1
person2 0 1 0

Where there was a letter for the person and location, it is recorded as a 1 in the table, and if the original table had a '-', then it is recorded as a 0 in the result table.

My humble attempt (it isn't working well, but at least I think an associative array should be in the code somewhere):

awk -F" " '{if($3!="-") a[$2]=$1} END {for (k in a) print k} file.txt

Upvotes: 0

Views: 68

Answers (1)

fedorqui
fedorqui

Reputation: 290265

Store the data in a wannabe-matrix and then loop through the also stored locations and persons!

{
seen[$1 FS $2]=(($3 == "-") ? "-"  0 : 1);
locs[$1]
people[$2]
}
END {
      for (loc in locs) 
        printf "\t%s", loc
      print ""
      for (person in people) {
          printf "%s%s", per, FS
          for (loc in locs) 
              printf "%d\t", seen[loc FS person]
          print ""
       }
}

See it in action:

$ awk -f a.wk your_file
location1   location2   location3
person1 0   1   1   
person2 0   1   0

Upvotes: 1

Related Questions