1

My input data file looks like this

Town,Code1,Code2,Code3,Code4
Oxford,X532,X011,X989
Oxford,X532,X011
Hendon,X002,X011,X222,X769

I need the output to list the towns with associated code, like this

Oxford,X532,1,Prim
Oxford,X011,2,Secd
Oxford,X989,3,Secd
Oxford,X532,1,Prim
Oxford,X001,2,Secd
Hendon,X002,1,Prim
Hendon,X011,2,Secd
Hendon,X222,3,Secd
Hendon,X769,4,Secd

However, I also need it to sum up where the codes which appear in the same position so the above would look more like this

Oxford,X532,1,Prim,2
Oxford,X011,2,Secd,2
Oxford,X989,3,Secd,1
Oxford,X532,1,Prim,2
Oxford,X001,2,Secd,1
Hendon,X002,1,Prim,1
Hendon,X011,2,Secd,1
Hendon,X222,3,Secd,1
Hendon,X769,4,Secd,1

Currently the AWK I have written does most of what I want, but clearly, I need to create an array to store where the same codes are present in the same position for the count in the output - my AWK as it stands is

FS=","
OFS=","
}
{ 

site=$1
code[1]=$2
code[2]=$3
code[3]=$4
code[4]=$5

key=site

for (i=1; i<=4; ++i)    {
        cnt=i
        ++recs[i]
        posn=""
        if (cnt==1)     {posn="Prim"}
        else            {posn="Secd"}

        if (code[i] !="")       {print key,code[i], cnt, posn}
                        }

}

How can I finish this AWK, to produce the correct output? not sure if I need an END section etc.

3
  • I did some grammar and punctuation fixes. Can you edit question to make it make sense. e.g. ”However, I also need it to sum up where the codes which appear in the same position so the above would look more like this” — I have no idea what you are asking. Jun 10 '19 at 10:58
  • there also seems to be a stray } in your code. Jun 10 '19 at 11:01
  • Why is the count (the last column) for Oxford,X011 2, but not for Hendon,X011?
    – Kusalananda
    Jun 10 '19 at 12:23
0

Try this,

awk -F ',' '{for (i=2;i<=NF;i++) { if (i==2) {print $1","$i","i-1",""Prim"}  else print $1","$i","i-1",""Secd"}}' file
Oxford,X532,1,Prim
Oxford,X011,2,Secd
Oxford,X989,3,Secd
Oxford,X532,1,Prim
Oxford,X001,2,Secd
Hendon,X002,1,Prim
Hendon,X011,2,Secd
Hendon,X222,3,Secd
Hendon,X769,4,Secd

To sum up where the codes which appear in the same position

ARRAY=(`awk -F ',' '{for (i=2;i<=NF;i++) { if (i==2) {print $1","$i","i-1",""Prim"}  else print $1","$i","i-1",""Secd"}}' file`)
for i in ${ARRAY[@]}
do
    PATTERN=`echo $i | awk -F ',' '{print $2}'`
    COUNT=`echo "${ARRAY[@]}"| tr ' ' '\n' | grep -c $PATTERN `
    echo "$i,$COUNT"
done

Oxford,X532,1,Prim,2
Oxford,X011,2,Secd,2
Oxford,X989,3,Secd,1
Oxford,X532,1,Prim,2
Oxford,X001,2,Secd,1
Hendon,X002,1,Prim,1
Hendon,X011,2,Secd,2
Hendon,X222,3,Secd,1
Hendon,X769,4,Secd,1
1
  • Hi, sorry slight mistake in input file. In essence I think my AWK as it stands is 90% there, I just need to add in an array part, within the AWK that outputs a count of each occurrence of the code keyed on site, where the code appears twice, the count would be two. I didn't know if this array needed to be done in the main body or in the END section
    – Jon
    Jun 10 '19 at 13:28
0

I'd process the input file twice: once to count the occurrences of town+code pairs, once to output the report:

awk -F, -v OFS=, '
    FNR == 1 {next}
    NR == FNR {
        for (i=2; i <= NF; i++)
            count[$1,$i]++
        next
    }
    {
        for (i=2; i <= NF; i++)
            print $1, \
                  $i, \
                  (i-1), \
                  (i == 2 ? "Prim" : "Secd"), \
                  count[$1,$i]
    }
' file file
Oxford,X532,1,Prim,2
Oxford,X011,2,Secd,2
Oxford,X989,3,Secd,1
Oxford,X532,1,Prim,2
Oxford,X011,2,Secd,2
Hendon,X002,1,Prim,1
Hendon,X011,2,Secd,1
Hendon,X222,3,Secd,1
Hendon,X769,4,Secd,1
0
BEGIN { 
    FS=","
    OFS=","
}
{
    site=$1
    code[1]=$2
    code[2]=$3
    code[3]=$4
    code[4]=$5    
    key=site

    for (i=1; i<=4; ++i)    {

        cnt=i
        ++recs[i]
        posn=""
        if (cnt==1)     {posn="Prim"}
        else            {posn="Secd"}

        if (code[i] !="")       { myarr[key","code[i]"," posn]++}
                        }
}
END{
    for (i in myarr) {print i","myarr[i]}
}

Results:

Hendon,X002,Prim,1
Hendon,X769,Secd,1
Oxford,X011,Secd,2
Hendon,X222,Secd,1
Oxford,X532,Prim,2
Hendon,X011,Secd,1
Oxford,X989,Secd,1
2
  • Please don't make more work for other people by vandalizing your posts. By posting on the Stack Exchange (SE) network, you've granted a non-revocable right, under the CC BY-SA 3.0 license for SE to distribute that content. By SE policy, any vandalism will be reverted. If you want to know more about deleting a post, consider taking a look at: How does deleting work?
    – iBooot
    Jun 11 '19 at 12:02
  • 1
    Welcome to Unix & Linux! Please use the Post answer button only for actual answers. You should modify your original question to add additional information.
    – Glorfindel
    Jun 11 '19 at 12:03

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.