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 :((

First, I don't understand the criteria used to determine which rows are relevant. Second, what have you tried? Reading a text file, skipping lines, converting to floating point, and summing are all basic concepts you can Google for. Unless you provide code, we can't figure out where you are falling short. — Steven Rumbalski, Jul 30 '13 at 17:15

rlms · Answer 1 · 2013-07-30T17:59:55.317

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!

`print(sum(float(i.split()[1]) for i in islice(f, a, b)))` avoids slurping the whole file into memory by using `itertools.islice`. To increase readability i would rename `i` to `row` or `line` or even just `s` as `i` is often used to indicate an integer. — Steven Rumbalski, Jul 30 '13 at 17:53

score 2 · Accepted Answer · answered Jul 30 '13 at 18:45

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()

answered Jul 30 '13 at 18:45

gggg

2,284
1
17
19

Since it is only the second column that is of interest I would do something like `data = numpy.loadtxt('filename.txt', usecols=1)` and also use `numpy.sum` for speedup. – Daniel Thaagaard Andreasen Jul 30 '13 at 18:51
quick question) how you get 7 and 1 ?? please explain me how these numbers came from?? – user2632088 Jul 30 '13 at 20:56
Assuming you start with the top set of data, you want to select the middle column, and skip the first few rows. The columns will be in the 2nd index 0,1,2, with 1 being the middle. The rows will be in the first index, 7: picks everything from index 7 on, so it will skip 0:6, which means it will skip 7 rows. edit: I didnt notice earlier that you want to skip some rows at the end too, so you could use 7:100 or whatever number is appropriate. – gggg Jul 30 '13 at 21:15
Thank you so much~ for helping~! – user2632088 Jul 30 '13 at 22:09

score 0 · Answer 3 · answered Jul 30 '13 at 17:52

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]

score 0 · Answer 4 · edited May 23 '17 at 11:44

0

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

Change split according to field separator. Reference.

edited May 23 '17 at 11:44

Community

1
1

answered Jan 03 '16 at 17:35

Aneesh

1

Python find sum of column in table in text file

4 Answers4

Linked

Related