Python find sum of column in table in text file

Question

This is what the file looks like

5.0000E+02   5.23744E-06  0.0006
1.0600E+03   2.15119E-06  0.0023
1.6900E+03   1.83529E-06  0.0035
2.4000E+03   1.76455E-06  0.0044
3.1900E+03   1.78831E-06  0.0050
4.0800E+03   1.86632E-06  0.0056
5.0800E+03   1.91086E-06  0.0061
6.1900E+03   1.97899E-06  0.0066   <--- Get data from here...
7.4300E+03   2.03105E-06  0.0070
8.8400E+03   2.08666E-06  0.0074
1.0400E+04   2.12723E-06  0.0078
1.2200E+04   2.20352E-06  0.0081
1.4100E+04   2.02335E-06  0.0089
1.6400E+04   1.98286E-06  0.0094
1.8900E+04   1.58478E-06  0.0107
2.1700E+04   1.09529E-06  0.0133
2.4900E+04   6.59218E-07  0.0173
2.8500E+04   3.19703E-07  0.0250
3.2500E+04   1.55052E-07  0.0358
3.7000E+04   6.94320E-08  0.0542
4.2100E+04   3.44175E-08  0.0764
4.7800E+04   2.37904E-08  0.0944
5.4200E+04   1.29016E-08  0.1283
6.1400E+04   5.45355E-09  0.1770
6.9500E+04   4.18030E-09  0.2486
7.8700E+04   2.47747E-09  0.2629
8.8900E+04   2.69887E-09  0.2820
1.0100E+05   2.15937E-09  0.4286
1.1300E+05   4.39994E-10  0.7824
1.2800E+05   0.00000E+00  0.0000
1.4400E+05   0.00000E+00  0.0000
1.6300E+05   0.00000E+00  0.0000
1.8300E+05   0.00000E+00  0.0000
2.0700E+05   0.00000E+00  0.0000
2.3300E+05   0.00000E+00  0.0000
2.6300E+05   0.00000E+00  0.0000
2.9600E+05   0.00000E+00  0.0000   <--- ...to here 
3.3300E+05   0.00000E+00  0.0000
3.7600E+05   0.00000E+00  0.0000
4.2300E+05   0.00000E+00  0.0000
4.7600E+05   0.00000E+00  0.0000
5.3600E+05   0.00000E+00  0.0000
6.0400E+05   0.00000E+00  0.0000
6.8000E+05   0.00000E+00  0.0000
7.6500E+05   0.00000E+00  0.0000
8.6100E+05   0.00000E+00  0.0000
9.6900E+05   0.00000E+00  0.0000
1.0900E+06   0.00000E+00  0.0000
1.2200E+06   0.00000E+00  0.0000
1.3800E+06   0.00000E+00  0.0000
1.5500E+06   0.00000E+00  0.0000
1.7500E+06   0.00000E+00  0.0000
1.9700E+06   0.00000E+00  0.0000
2.2100E+06   0.00000E+00  0.0000
2.5000E+06   0.00000E+00  0.0000
2.8000E+06   0.00000E+00  0.0000
3.1500E+06   0.00000E+00  0.0000
3.5400E+06   0.00000E+00  0.0000
3.9900E+06   0.00000E+00  0.0000
4.4900E+06   0.00000E+00  0.0000
5.0500E+06   0.00000E+00  0.0000
5.6800E+06   0.00000E+00  0.0000
6.3900E+06   0.00000E+00  0.0000
1.0000E+07   0.00000E+00  0.0000

So the the Python script would get this data:

6.1900E+03   1.97899E-06  0.0066  
7.4300E+03   2.03105E-06  0.0070
8.8400E+03   2.08666E-06  0.0074
1.0400E+04   2.12723E-06  0.0078
1.2200E+04   2.20352E-06  0.0081
1.4100E+04   2.02335E-06  0.0089
1.6400E+04   1.98286E-06  0.0094
1.8900E+04   1.58478E-06  0.0107
2.1700E+04   1.09529E-06  0.0133
2.4900E+04   6.59218E-07  0.0173
2.8500E+04   3.19703E-07  0.0250
3.2500E+04   1.55052E-07  0.0358
3.7000E+04   6.94320E-08  0.0542
4.2100E+04   3.44175E-08  0.0764
4.7800E+04   2.37904E-08  0.0944
5.4200E+04   1.29016E-08  0.1283
6.1400E+04   5.45355E-09  0.1770
6.9500E+04   4.18030E-09  0.2486
7.8700E+04   2.47747E-09  0.2629
8.8900E+04   2.69887E-09  0.2820
1.0100E+05   2.15937E-09  0.4286
1.1300E+05   4.39994E-10  0.7824
1.2800E+05   0.00000E+00  0.0000
1.4400E+05   0.00000E+00  0.0000
1.6300E+05   0.00000E+00  0.0000
1.8300E+05   0.00000E+00  0.0000
2.0700E+05   0.00000E+00  0.0000
2.3300E+05   0.00000E+00  0.0000
2.6300E+05   0.00000E+00  0.0000
2.9600E+05   0.00000E+00  0.0000

Then I need the sum of the central column.

Like this: (1.97899E-06 + 2.03105E-06 + 2.08666E-06 + ... + 0.00000E+00) = 1.90994E-05

Only the second column matters for this problem.

The first column represent time. The second column represent some data numbers. The third column represent some random numbers.

please help me to find out :((

Answer 1

sum([int(s.strip().split()[1]) for s in open('exp.txt').readlines()])

Change split according to field separator. Reference.

Answer 2

import numpy
data = numpy.loadtxt('filename.txt')
print(data[7:,1].sum())

It's possible that I have the indexes transposed, in which case it would be data[1,7:].sum()

Answer 3

First you need to open the file. The best way to do this is:

with open("myfile.txt","r") as f:
    # do stuff with file f here

Then you need to get the individual lines. If the file isn't too large (as in very large) you can store it all in memory.

Get the lines as a list by calling list(f), eg. list_of_file = list(f).

Then get the lines from line a to line b with lines_i_want = list_of_file[a:b].

Then get the central column (as floats) with centre_column = [float(line.split()[1]) for line in lines_i_want].

Now add them with total = sum(centre_column).

Or, for brevity at the expense of being difficult to read:

with open("myfile.txt") as f:
    print(sum(float(i.split()[1]) for i in list(f)[a:b]))

If the file is large and cannot be stored in memory then you should use islice from the itertools module instead of just slicing the list:

with open("myfile.txt") print(sum(float(line.split()[1]) for line in islice(f, a, b)))

Make sure you include the line from itertools import islice at the top of the program if you want to do this!

Answer 4

First you open the file and create a list.

with open('filename.txt','rU') as f:
    table = f.readlines()

Assuming you always label the rows you want you can find the bounds by

bounds = [table.index(row) for row in table if len(row.split()) is not 3]

This looks at each line of your table and finds the places where you want to start and stop. To get the rows you want to sum up:

target_rows = table[bounds[0]:bounds[1]+1]

There is a +1 on the bounds[1] because you want to include the last entry and the way list splicing works in python doesn't include the last entry. To extract the second column:

target_col = [float(row.split()[1]) for row in target_rows]

And finally to get the sum:

total = sum(target_col)

Furthermore, target_rows are give the rows you want to output. You can output it to give you the desired output table:

output = ['    '.join(row.split()[:3]) for row in target_rows]