3

I have a file with 250k rows and 10 columns like:

img1 aa bb cc ...
img2 aa yy dd ...
img3 uu bb ee ...
img4 NA bb tt ...

I want a script that will convert this file to:

img1 1 1 1 ...
img2 1 2 2 ...
img3 2 1 3 ...
img4 0 1 4 ...

A unique value from each column after first one should be replaced with a unique identifier starting from 0, where 0 is reserved for the string "NA".

Also, for each column, I want to generate a file that contains the mapping. For example, the file for 2nd column should be:

NA 0
aa 1
uu 2

Can anyone suggest an elegant solution for this? Any help would be greatly appreciated.

2
  • What if there was a line at the end after a line with NA value like img5 aa yy bb cc ...? What would be the mapping? Does this mapping applicable for consecutive column values or also from the beginning of the column which starts? Sep 17, 2017 at 4:02
  • @αғsнιη That would be mapped to 'img5 1 2 5 ...' The values start over again for each column. Sep 17, 2017 at 4:10

3 Answers 3

2

Here's a very simple approach. Works fine for me, using gawk 3.1.7.

#!/usr/bin/awk -f
{
    for(x=2;x<=NF;x++) {
        if(x$x in a) {
            $x=a[x$x]
        } else {
            if($x=="NA") {
                print $x,0 > "column"x
                a[x$x]=0
                $x="0"
            } else {
                m[x]++
                print $x,m[x] > "column"x
                a[x$x]=m[x]
                $x=m[x]
            }
        }
    }
    print $0 > "results"
}
3
  • 1
    Thank you. This solves my problem perfectly. There is one bug, the NA entries generated in the column files are duplicated for each occurrence, not a big issue though. Sep 17, 2017 at 3:09
  • Oops, now fixed, by adding that new a[x$x]=0 line.
    – steve
    Sep 17, 2017 at 7:46
  • 2
    You may use NF in place of 4.
    – Kusalananda
    Sep 17, 2017 at 8:03
2
$ awk 'BEGIN { id["NA"] = ++n } { for (i=2; i<=NF; ++i) { id[$i] || id[$i] = ++n; $i = id[$i] - 1 } } { print } END { for (i in id) { print i, id[i] - 1 >"map" } }' file
img1 1 2 3
img2 1 4 5
img3 6 2 7
img4 0 2 8

This assigns a unique ID to each value in all columns except the first (the img-column). I opted for making the IDs globally unique instead of only unique for a column as this would reduce the number of needed mapping files having to be produced.

The script unraveled:

BEGIN   { id["NA"] = ++n }

        {
            for (i=2; i<=NF; ++i) {
                id[$i] || id[$i] = ++n;
                $i = id[$i] - 1
            }
        }

        { print }

END     {
            for (i in id) {
                print i, id[i] - 1 >"map"
            }
        }

It starts off by assigning the string NA the ID 1 (the IDs are always decremented by 1 before output) and also updates the counter n to 1. n will always be the ID assigned to the previous string.

For each input line, we iterate over the fields. If no ID has been assigned to the string in the current field, we assign one and modify the field in place.

The line is then printed with its modified fields.

At the end, all strings and their corresponding IDs are stored in the file called map.

For the given input, this file may look something like

bb 2
ee 7
cc 3
NA 0
tt 8
dd 5
yy 4
aa 1
uu 6
0
1

GNU awk solution (the two dimensional array support is needed).

awk '{
    printf "%s ", $1;

    for(i = 2; i <= NF; i++) {
        filename = "column_" i - 1 "_mapping"

        if(NR == 1) {
            arr[i]["NA"] = 0;
            print "NA 0" > filename;    
        }

        if(! ($i in arr[i]) ) {
            cnt[i]++;
            arr[i][$i] = cnt[i];
            print  $i, cnt[i] > filename;   
        }

        printf "%d ", arr[i][$i];
    }

    print "";
}' input.txt

Input

img1 aa bb cc
img2 aa yy dd
img3 uu bb ee
img4 NA bb tt

Output

img1 1 1 1 
img2 1 2 2 
img3 2 1 3 
img4 0 1 4 

Content of mapping files

tail -n +1 -- *_mapping

==> column_1_mapping <==
NA 0
aa 1
uu 2

==> column_2_mapping <==
NA 0
bb 1
yy 2

==> column_3_mapping <==
NA 0
cc 1
dd 2
ee 3
tt 4

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.