7

I have huge text files with two fields, the first is a string the second is an integer. The files are sorted by the first field. What I'd like to get in the output is one line per unique string and the sum of the numbers for the identical strings. Some strings appear only once while other appear multiple times. E.g. Given the sample data below, for the string glehnia I'd like to get 10+22=32 in the result.

Any suggestions how to do this either with gnuwin32 command line tools or in linux shell?

Thanks!

glehnia 10
glehnia 22
glehniae 343
glehnii 923
glei 1171
glei 2283
glei 3466
gleib 914
gleiber 652
gleiberg 495
gleiberg 709

1
5

In AWK, you could do something like this:

awk '($1 == last) || (last == "") {sum += $2}
     ($1 != last) && (last != "") {print last " " sum; sum = $2}
                                  {last = $1}
     END                          {print last " " sum}' huge_text_file.txt
2
  • Thanks so much! I had to tweak a little to use TAB as delimiters but it got me the kickstart!
    – dnkb
    May 2 '10 at 18:54
  • 1
    @dnkb: Using a comma in your print statements allows you to set the output field separator and then print will use it automatically. This may be what you did: awk 'BEGIN {OFS="\t"} ($1 == last || last == "") {sum += $2} ($1 != last && last != "") {print last, sum; sum = $2} {last = $1} END {print last, sum}' huge_text_file.txt May 2 '10 at 20:29
2

This type of queries are most easily and flexibly done in SQL. You can use SQLet and SQLite for that purpose:

sqlet.py -d' ' -A file1.txt 'select a1, sum(a2) from A group by a1' | sqlite3
1

You could a use a few lines of Lua to acheive this. Lua is available on a wide range of platforms including Windows and Linux.

-- Quick and dirty - no error checking, unsorted output

io.input('huge_text_file.txt')

results = {}

for line in io.lines() do
    for text, number in string.gmatch(line, '(%w+)%s+(%d+)') do
        results[text] = (results[text] or 0) + number
    end
end

for text, number in pairs(results) do
    print(text, number)
end

You can sort the output using any sort utility or a few more lines of Lua.

0

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.