[SOLVED] change field value of one file based on another input file using awk

Issue

I have a sparse matrix ("matrix.csv") with 10k rows and 4 columns (1st column is "user", and the rest columns are called "slots" and contain 0s or 1s), like this:

user1,0,1,0,0
user2,0,1,0,1   
user3,1,0,0,0

Some of the slots that contain a "0" should be changed to contain a "1". I have another file ("slots2change.csv") that tells me which slots should be changed, like this:

user1,3
user3,2
user3,4

So for user1, I need to change slot3 to contain a "1" instead of a "0", and for user3 I should change slot2 and slot4 to contain a "1" instead of a "0", and so on.

Expected result:

user1,0,1,1,0
user2,0,1,0,1
user3,1,1,0,1

How can I achieve this using awk or sed?

Looking at this post: awk or sed change field of file based on another input file, a user proposed an answer that is valid if the "slots2change.csv" file do not contain the same user in diferent rows, which is not the case in here.

The solution proposed was:

awk 'BEGIN{FS=OFS=","}
    NR==FNR{arr[$1]=$2;next}
    NR!=FNR {for (i in arr)
        if ($1 == i) {
            F=arr[i] + 1
            $F=1
        }
     print
    }
' slots2change.csv matrix.csv

But that answer doesn't apply in the case where the "slots2change.csv" file contain the same user in different rows, as is now the case.

Any ideas?

Solution

Using GNU awk for arrays of arrays:

$ cat tst.awk
BEGIN { FS=OFS="," }
NR == FNR {
    users2slots[$1][$2]
    next
}
$1 in users2slots {
    for ( slot in users2slots[$1] ) {
        $(slot+1) = 1
    }
}
{ print }

$ awk -f tst.awk slots2change.csv matrix.csv
user1,0,1,1,0
user2,0,1,0,1
user3,1,1,0,1

or using any awk:

$ cat tst.awk
BEGIN { FS=OFS="," }
NR == FNR {
    if ( !seen[$0]++ ) {
        users2slots[$1] = ($1 in users2slots ? users2slots[$1] FS : "") $2
    }
    next
}
$1 in users2slots {
    split(users2slots[$1],slots)
    for ( idx in slots ) {
        slot = slots[idx]
        $(slot+1) = 1
    }
}
{ print }

$ awk -f tst.awk slots2change.csv matrix.csv
user1,0,1,1,0
user2,0,1,0,1
user3,1,1,0,1

Answered By - Ed Morton

Answer Checked By - Marilyn (WPSolving Volunteer)