6

I have a log file with user_id's and another column with contest results. I would like to:

  1. find all the user_ids of the users who won
  2. given those user_id's, return ALL the log entries for those users

Example:

log.csv

id,user_id,winner,page_id
1,user_1,win,1
2,user_1,,10
3,user_2,,1
4,user_2,,2
5,user_4,win,10
6,user_5,,2
7,user_5,win,3

Given a log file like this, I'm currently doing this as a two-step process:

Step 1: Return each row mentioning the word 'win'

/win/ {
    FS=","

    # To make unique, store user_id's in array
    n[$2] = 0
}

# Print the unique array keys
END{
    for (x in n)
        print x
}

This yields:

user_1
user_4
user_5

I save this output in the file output.txt

Then, I pass that file and the original log file into another awk file:

NR == FNR{
    n[$1] = 0   # Set the user ID to the array
    next        # Go to the next file
}
{
    FS=","
    if($2 in n){
        print $0
    }
}

This returns the correct output (all rows for each of the user_id's that won):

1,user_1,win,1
2,user_1,,10
5,user_4,win,10
6,user_5,,2
7,user_5,win,3

Is there a more elegant way to do this? With a single awk file?

  • Would this shell script work for you? for u in $(grep win input.log | awk -F, '{print $2}'); do grep "$u" input.log; done – jayhendren Feb 7 '15 at 0:54
  • I should have mentioned that I'm using mawk for speed. I'd like to avoid shell loops; I've got TB of data. Thanks @jayhendren – zbinsd Feb 7 '15 at 0:57
  • 3
    There's nothing to stop you simply reading the same file twice: how about mawk -F, 'NR==FNR {if ($3=="win") winners[$2]++; next}; $2 in winners' file file for example? – steeldriver Feb 7 '15 at 1:20
  • Thanks @steeldriver. This is blazing fast compared to everything else I've tried (see my comment below) and comes in at just under a second for my test file (420k+ lines). It's what I'm now using. – zbinsd Feb 7 '15 at 2:57
3

I would use two arrays:

awk -F, '{a[$0]=$2;if($3=="win")b[$2]++}END{for(i in a){if(b[a[i]])print i}}'
|improve this answer|||||
  • I should add that your one liner is quite a bit more compact, but running using mawk, the total time to process 420k lines (160+MB file) was around 2.533s. The individual times of my two scripts when combined was: 1.213s. That's a 2x improvement using my scripts. I'll keep it the chosen answer, but I'll have to use my original scripts. Thanks for your beautiful answer :) – zbinsd Feb 7 '15 at 2:09
  • 1
    @zbinsd that's most probably because here the whole file is stored in the memory (in fact even more then one file), and memory operations cost some time. However I don't see a way to improve it. The bigger file is, the worse this solution is. For smaller files running two awk processes should be slower as time of forks would be important. – jimmij Feb 7 '15 at 2:29
3

Is there a more elegant way to do this?

Yes, of course there is. Just run Awk twice across the same file (as you said in your title).

awk -F, '$3=="win"{won[$2]} FNR==NR{next} $2 in won' log.csv log.csv
|improve this answer|||||
1

How I fill grep is quicker than awk So if you have GNU grep with perl extention you can try

fgrep -f <(grep -oP "[^,]*(?=,win)" log.csv) log.csv

Without perl.ext you'l have to pipe grep output through cut

fgrep -f <(grep win log.csv | cut -d, -f2) log.csv

Or use sed (it seems to be quicker a little than above grep|cut)

fgrep -f <(sed -n '/win/s/^[^,]*,\([^,]*\).*/\1/p' log.csv) log.csv
|improve this answer|||||
1

This is a complete single file gnu awk solution you can just run it as :> awk -f singlestep.awk log.csv

BEGIN {
    FS=",";
    #you cannot use FILENAME , since in BEGIN section you are not processing any files and the FILENAME variable is empty
    # So you need to use the ARGV
    while(getline < ARGV[1])
    {
        if ($0 ~ /win/) {
            # To make unique, store user_id's in array
            n[$2] = 0;
        }
    }
}
{
    if ($2 in n)
    {
        print $0;
    }
}
|improve this answer|||||
0

Rather late for this, but for posterity, I'd like to point out that you can do this:

awk '
   BEGIN 
   {
       while(getline < FILENAME)
       {    
           # do first pass stuff
       }
   }

   {
        # do second pass stuff
   }
' file

If you want to do more passes, you can close(FILENAME) after the first while loop, then do a second one.

|improve this answer|||||
0

Using csvkit, one may do

$ csvsql --query 'SELECT b.* FROM log AS a JOIN log AS b USING (user_id) WHERE a.winner="win"' log.csv
id,user_id,winner,page_id
1,user_1,win,1
2,user_1,,10
5,user_4,win,10
6,user_5,,2
7,user_5,win,3

But I'm not so sure that will be very fast on a huge input file. By default, an SQLite database is created, populated and queried in the background.

You may however do this in the shell directly too:

$ join -t ',' -1 2 -2 2 log.csv log.csv | awk -v FS=',' -v OFS=',' '$3 == "win" { print $5,$1,$6,$7 }'
1,user_1,win,1
2,user_1,,10
5,user_4,win,10
6,user_5,,2
7,user_5,win,3

Again, I'm not sure how this scales to huge files. Also, the input file has to be sorted on column 2 (which the example data is).

|improve this answer|||||

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.