Get random records from CSV files

Question

I have 50 CSV files, up to 2 millions records each file.

I need daily to get 10000 random records from each of 50 files and make a new CSV file with all info (10000*50).

I can not do it manually because it will take me a lot of time. I've tried to use Access, but because the database is larger then 2G, I can not use it. Also I've tried to use CSVed - good software, but still did not help me.

Can someone recommend how to get random records from files and make a new CSV file?

Answer 1

This is really a job for a scripting language. The task you describe is so specific that you're very unlikely to find a tool that does exactly that. On the other hand, it can be written in a few simple lines of code.

You didn't specify an operating system, so I'm going to assume that it is POSIX-compliant. That includes Linux, OS X, FreeBSD, etc. On Windows, install a set of Unix/Linux tools such as Cygwin.

If you have the shuf utility, that makes it easier than with pure POSIX tools. shuf is available out of the box on non-embedded Linux and Cygwin; on other systems, install GNU coreutils.

Assuming that the CSV files are called myfile-XXX.csv and are in the current directory, the following shell code snippet reads 10000 random lines from each file and writes the lot to a file whose name is based on the current date. The header line is copied from the first file.

#!/bin/sh
(
  set -- myfile-*.csv
  head -n -- "$1"
  for x; do
    <"$x" tail -n +2 | shuf -n 10000
  done
) >daily-sample-$(date +%Y%m%d).csv

shuf reads each file into memory. If you don't have enough memory to do that, here's a different approach that's more complex but only needs to store little more than 10000 lines.

#!/bin/sh
(
  set -- myfile-*.csv
  head -n -- "$1"
  for x; do
    awk '
      BEGIN {srand()}
      NR == 1 {next}
      NR <= 10001 {a[NR-1] = $0; next}
      {r = rand() * (NR-1); if (r <= 10000) a[r] = $0}
      END {for (i=1; i<=10000; i++) print a[i]}
    ' "$x"
  done
) >daily-sample-$(date +%Y%m%d).csv

Warning: untested code. If you need help with this code, you can ask on Unix & Linux Stack Exchange. Be sure to say exactly what the problem is: provide your code, a sample of data (try with smaller files first), explain what you want the code to do and what it does instead.

Instead of shell utilities, you can use languages designed for scripting such as Perl, Python or Ruby, if you prefer.

Answer 2

Talking about software, one option might be ACL Analytics. You can import big sets of information into the software and write scripts to perform tasks automatically. But I don't think it would work efficiently (although I don't know your data).

I see you have an answer in StackOverflow, which states that you could use c# and databases like MySQL or SQL Server Express.

Answer 3

You could use python possible + Pandas - both are free, multi-platform and come with CSV support.

Pandas handles huge datasets really well or with the python csv library you could simply open each file and run through it discarding records until random record numbers are matched.