0

I have 3 comma separated quoted fields.

last crawled,linking page,domain
"Nov 17, 2018","https://allestoringen.be/problemen/bwin/antwerpen","allestoringen.be"
"Aug 11, 2017","http://casino.linkplek.be/","linkplek.be"
"Nov 17, 2018","http://pronoroll.blogspot.com/p/blog-page_26.html","pronoroll.blogspot.com"
etc

I need to remove duplicates on the date fields and find the count of unique linking pages for each unique date (column $2) and count of unique domains for the unique date (column $3). I have tried :

awk '{A[$1 OFS $2]++} END {for(k in A) print k, A[k]}' FPAT='([^,]*)|("[^"]+")' file
awk '{A[$1 OFS $3]++} END {for(k in A) print k, A[k]}' FPAT='([^,]*)|("[^"]+")' file

But I am a little confused on getting all 3 columns in a single go.

0
# script.awk

BEGIN {
    FPAT = "[^\"]+"
}

{
    # the first if is to avoid processing title row and rows that do not contain fields
    if (NF > 1) {
        # the third field is the linking page column; the second field is a comma
        if ($3 != "" && $1 $3 in unique_linking_pages == 0) {
            unique_linking_pages[$1 $3]
            unique_linking_page_counts[$1]++
        }
        # the fifth field is the domain column; the fourth field is a comma
        if ($5 != "" && $1 $5 in unique_domains == 0) {
            unique_domains[$1 $5]
            unique_domain_counts[$1]++
        }

        # in case of empty fields in columns 2 and or 3 of the input file,
        # this ensures that all the dates are recorded
        dates[$1]
    }
}

END {
    printf "Date, Unique Linking Page Count, Unique Domain Count\n"
    for (date in dates)
        output = output date " | " unique_linking_page_counts[date] " | " unique_domain_counts[date] "\n"

    system("column -t -s '|' <<< '" output "'")
}
  • @NikoGambt..can the output be in the form of: date, unique_linking_page_counts, unique_domain_counts No pages or domains...just counts – Mallik Kumar Jan 28 at 13:13
  • So, for example, even though Nov 17, 2018 has two unique linking pages, you want the unique_linking_page_counts to be equal to 2 for that date?? The only way to do what you're asking is to group the unique linking pages by date (and to group the unique domains by date too). However, doing so would lose the information of what linking pages (and domains) are unique! – Niko Gambt Jan 28 at 13:35
  • Yes....I want to get only unique dates, ie. Nov 17, 2018 appears once and number of unique linking pages will be 2 as in this case...this is true for domains as well. – Mallik Kumar Jan 28 at 13:41
  • @MallikKumar Just an advice, you should have provided an example output in your OP. No one has the superpower to guess what you want. Just like me, anybody can misunderstand what you want. – Niko Gambt Jan 28 at 13:43
  • My mistake. Should have put one in my post. Appreciate your help in this matter. – Mallik Kumar Jan 28 at 13:47

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.