frequency of millions of data values in a csv column

Question

I have a long list of numbers (a single column that has 5 million rows), that are not all unique from one another. I want to see which thousand of them are the most frequent occurrences in the list. Any ideas on how I could do this easily? I could use excel or a python script or other means too.

Answer 1

In Bash:

sort filename | uniq -c | sort -nr

Answer 2

Here's one way with Python using csv.reader and collections.Counter:

import csv
from collections import Counter
from itertools import chain
from io import StringIO

mystr = StringIO("""1
2
3
3
1
1""")

# replace mystr with open('file.csv', 'r')
with mystr as fin:
    # define lazy reader object
    reader = csv.reader(mystr)
    # flatten, convert to int, feed to Counter object
    c = Counter(map(int, chain.from_iterable(reader)))

# calculate 2 most common items, return number and counts
print(c.most_common(2))

[(1, 3), (3, 2)]

Answer 3

Tom's approach in Python:

d = dict()

import sys
for filename in sys.argv[1:]:
    file = open(filename, 'r')
    for line in file.read().splitlines():
        if line not in d:
            d[line] = 1
        else:
            d[line] += 1
    file.close()

import operator
print "Item,Count"
for line in sorted(d.items(), key=operator.itemgetter(1)):
    print line[0] + "," + str( line[1] )

Usage:

python linesorter.py filename1.txt filename2.txt filename_...