1

I have a fairly large data set ~500 million rows. The data set looks like below. Col 1 is float number, col 2 is mac id(device id)

1616.93,ac:22:0b:a6:22:c3
2872.32,c0:bd:d1:36:bb:49
3314.55,d4:0b:1a:39:19:b2
2854.11,18:f6:43:64:81:67
3540.68,18:f6:43:64:81:67
3856.91,ac:22:0b:a6:22:c3
2497.93,d4:0b:1a:39:19:b2

The problem requires to output the ~100,000 random samples after grouping the col 2 and then finding the largest value from col 1 for that particular group.

Intermediate output would look like(group by col1 ):

1616.93,ac:22:0b:a6:22:c3
3856.91,ac:22:0b:a6:22:c3
2854.11,18:f6:43:64:81:67
3540.68,18:f6:43:64:81:67
2872.32,c0:bd:d1:36:bb:49
3314.55,d4:0b:1a:39:19:b2
2497.93,d4:0b:1a:39:19:b2

After this, I require largest value from every grouped col. Output will look like below:

3856.91,ac:22:0b:a6:22:c3
3540.68,18:f6:43:64:81:67
2872.32,c0:bd:d1:36:bb:49
3314.55,d4:0b:1a:39:19:b2

The final step would be to have a truly random sample and the output will look like below:

3540.68,18:f6:43:64:81:67
2872.32,c0:bd:d1:36:bb:49

Any idea, how to perform this. I have just started working on linux and have no idea on how to perform such a mammoth task. Any help would be appreciated.

  • any takers. the solution provided seems not to be working – user4943236 Jul 16 '15 at 4:59
1

try

BEGIN { srand() ;r=0 ; FS="," ; before="" ; }
{ if ( $1 > V[$2]) V[$2]=$1 ;
    if ( before != $2 && before != ""  ) {
          r=rand()*100 ;
       if ( r  > 50 ) printf "%s,%s\n",V[before],before ;
        }
 before=$2 ;
 }  data-file.txt

where

  • if ( $1 > V[$2]) V[$2]=$1 ; get max value for mac
  • if ( before != $2 && before != "" ) { when a new mac value come
  • r=rand()*100 ; if ( r > 50 ) printf "%s,%s\n",V[before],before ; compute a random value, and print if above 50% (you can change to 5% or 95%)
0

500 million rows is a lot of data so you may want to be looking a more scalable way to handle the job. That said doing this with standard Linux utilities is achievable.

Assuming your data is in a file called data.txt you can print it to the terminal using cat:

$ cat data.txt
1616.93,ac:22:0b:a6:22:c3
2872.32,c0:bd:d1:36:bb:49
3314.55,d4:0b:1a:39:19:b2
2854.11,18:f6:43:64:81:67
3540.68,18:f6:43:64:81:67
3856.91,ac:22:0b:a6:22:c3
2497.93,d4:0b:1a:39:19:b2

This output can then be piped into sort with the options -t ',' -k 2. These options will tell sort to split the data using a comma as the delimiter and then sort by the values in the second column:

$ cat data.txt | sort -t ',' -k 2
2854.11,18:f6:43:64:81:67
3540.68,18:f6:43:64:81:67
1616.93,ac:22:0b:a6:22:c3
3856.91,ac:22:0b:a6:22:c3
2872.32,c0:bd:d1:36:bb:49
2497.93,d4:0b:1a:39:19:b2
3314.55,d4:0b:1a:39:19:b2

For your next task you'll want to use sort with the options -t ',' -k 1 -r. This will sort using a comma as the delimiter and the value in the first column. The -r will sort in revere order giving us the largest entries first:

$ cat data.txt | sort -t ',' -k 1 -r
3856.91,ac:22:0b:a6:22:c3
3540.68,18:f6:43:64:81:67
3314.55,d4:0b:1a:39:19:b2
2872.32,c0:bd:d1:36:bb:49
2854.11,18:f6:43:64:81:67
2497.93,d4:0b:1a:39:19:b2
1616.93,ac:22:0b:a6:22:c3

You'll then want to pipe the output of the above example into sort again, this time using the -t ',' -k 2 -u options. As before -t ',' and -k 2 tell sort to use the second column, while the new option -u tells sort to only keep unique entries.

Because we've sorted the data by the values in the first column, when finding unique entries the highest ones will be found first. This will give us the single highest value for each MAC address:

$ cat data.txt | sort -t ',' -k 1 -r | sort -t ',' -k 2 -u
3540.68,18:f6:43:64:81:67
3856.91,ac:22:0b:a6:22:c3
2872.32,c0:bd:d1:36:bb:49
3314.55,d4:0b:1a:39:19:b2

Finally to get a random sample you can use shuf with the option -n 2 where two is how many random samples you want:

$ shuf data.txt -n 2
3856.91,ac:22:0b:a6:22:c3
3314.55,d4:0b:1a:39:19:b2
  • thank you for your reply. I tried replicating your solution, however I'm not getting the unique mac address. Its still showing the duplicate entries. May I know how to get around with this – user4943236 Jul 16 '15 at 2:37
  • Using cat data.txt | sort -t ',' -k 1 -r | sort -t ',' -k 2 -u appears to work for me. Can you please run sort --version and post the version you are using? And if possible an example of you running the command on a dataset and what it outputs? – p8952 Jul 16 '15 at 8:33
  • I'm using: sort (GNU coreutils) 8.13 – user4943236 Jul 16 '15 at 9:26
  • I've just tested with sort (GNU coreutils) 8.13 and it appears to work for me. What do you see when running cat data.txt and cat data.txt | sort -t ',' -k 1 -r | sort -t ',' -k 2 -u? I get this: pastebin.com/rsBmFhUk which looks correct. – p8952 Jul 16 '15 at 10:04
  • Thank you for your comments. You're doing great work by supporting naives like me. Coming back to question, I'm finding duplicates in second column. The below answer worked for me. sort -t "," -k2,2 -k1,1 -r output.txt| awk -F "," '!a[$2]++'|head -100 – user4943236 Jul 16 '15 at 12:31
0

For records. this seems to be working, none of the other option worked

sort -t "," -k2,2 -k1,1 -r output.txt| awk -F "," '!a[$2]++'|head -100

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.