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

Question

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?

Ed Morton · Accepted Answer · 2022-12-17T17:37:15.653

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

score 1 · Answer 2 · answered Dec 17 '22 at 17:43

1

Using sed

while IFS="," read -r user slot; do 
  sed -Ei "/$user/{s/(([^,]*,){$slot})[^,]*/\11/}" matrix.csv
done < slots2change.csv

$ cat matrix.csv
user1,0,1,1,0
user2,0,1,0,1   
user3,1,1,0,1

answered Dec 17 '22 at 17:43

HatLess

10,622
5
14
32

See [why-is-using-a-shell-loop-to-process-text-considered-bad-practice](https://unix.stackexchange.com/questions/169716/why-is-using-a-shell-loop-to-process-text-considered-bad-practice). – Ed Morton Dec 17 '22 at 19:33

Fravadona · Answer 3 · 2022-12-17T18:59:10.327

If the order in which the users are outputted doesn't matter then you could do something like this:

awk '
    BEGIN { FS = OFS = "," }
    FNR == NR {
        fieldsCount[$1] = NF
        for (i = 1; i <= NF; i++ )
            matrix[$1,i] = $i
        next
    }
    { matrix[$1,$2+1] = 1 }
    END {
        for ( id in fieldsCount ) {
            nf = fieldsCount[id]
            for (i = 1; i <= nf; i++)
                printf "%s%s", matrix[id,i], (i < nf ? OFS : ORS)
        }
    }
' matrix.csv slots2change.csv

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

score 1 · Answer 4 · answered Dec 18 '22 at 11:41

This might work for you (GNU sed):

sed -E 's#(.*),(.*)#/^\1/s/,[01]/,1/\2#' fileChanges | sed -f - fileCsv

Create a sed script from the file containing the changes and apply it to the intended file.

The solution above, manufactures a match and substitution for each line in the file changes. This is then piped through to second invocation of sed which applies the sed script to the csv file.

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

4 Answers4