Hi guys:-) I have no experience in programming, and i realise that asking for someone to write an entire programme for me might be a stretch, but unfortunately i havent got any mates that are good at programming. I think the program i need is fairly simple once you know how to write code:-)

I have come across some very uncooperative software on a piece of scientific equipment that I'm currently using. It generates excel files with a huge amount of data points (3-400.000) with each data point having 2 parameters and a time. I need to calculate the average of each parameter for all data points within each second. There are a varying amount of data points for each second, which is why i can't simply have excel do the calculations for me...

The excel files have parameter A in column A, parameter B in column B and the time in column C, if needed i can provide a data file to try the program on:-)

If any kind person will help me, we can figure out a way for me to compensate you for your help:-)


Keeping my fingers crossed, hope to hear from one of you soon:-)

Best regards, casper

As a starting point, here is a function which computes the averages for a sequence of values (parameter A, parameter B, time). The assumption is that the input data are sorted by time. Try to run it and see if it does what you want

from itertools import groupby

def gen_averages(input_triples):
    for second, triples in groupby(input_triples, lambda triple: triple[2]):
        a, b = 0.0, 0.0
        n = 0
        for x, y, t in triples:
            a += float(x)
            b += float(y)
            n += 1
        yield (a/n, b/n, second)

def test_me():
    data = [
        (1, 1, 364),
        (2, 4, 365),
        (3, 5, 365),
        (8, -1, 365),
        (3, 2, 366),
        (1, 1, 366),
        (0, 0, 367),
    ]
    for triple in gen_averages(data):
        print(triple)

if __name__ == "__main__":
    test_me()

""" My output -->
(1.0, 1.0, 364)
(4.333333333333333, 2.6666666666666665, 365)
(2.0, 1.5, 366)
(0.0, 0.0, 367)
"""

It only remains to read the data in the excel files and perhaps write the output to other excel files. We can use the modules xlrd xlwr xlutils for this.
What are your OS and your version of python ? Also, can you attach an excel file (or a part of it) to a post (you may have to zip it first) ?

Edited 6 Years Ago by Gribouillis: n/a

Thanks mate:-) I tried to write the code into python, and that went well:-) Im almost embarrased to ask, but how do i start the program? When i press "run module" I just get:
>>>
>>>

Reading my first post, im not sure i was clear enough on the output i need, i need the average value of each parameter (FSC and SSC) taken for all data points that have t=[0;1[, t=[1;2[, t=[2;3[ and so on as i need to see whether these average values changes over time.

I couldnt attach the whole file for some reason, even after it was zipped so i uploaded a little sample.

Im working on a Windows XP OS and i downloaded python 2.6.5., but if you prefer working on a different version i will get that:-)

I really appreciate you helping me mate:-)

Best regards, Casper:-)

Attachments
FSC	SSC	Time (s)
262143	43377	0
262143	46245	0,003
42753	13518	0,004
191555	262143	0,004
262143	8965	0,006
262143	7861	0,009
252950	12265	0,01
140544	3820	0,01
262143	175566	0,011
144526	5943	0,012
47350	262143	0,012
46730	262143	0,012
36307	158055	0,013
230784	1848	0,016
262143	200772	0,018
262143	6061	0,019
192896	10872	0,02
55973	3420	0,028
132361	11895	0,028
146618	3396	0,032
212328	3585	0,034
262143	9216	0,035
258532	8289	0,044
43569	12355	0,048
262143	5745	0,052
203023	196999	0,052
36174	16338	0,057
33385	5524	0,057
54714	28494	0,064
262143	5754	0,066
143130	3904	0,068
233107	4174	0,071
39049	262143	0,071
248997	226008	0,073
156782	2949	0,074
122596	18469	0,075
42080	262143	0,082
201072	5733	0,083
47853	61099	0,087
189761	10081	0,088
38742	4722	0,089
144192	24846	0,089
76185	33207	0,101
262143	11803	0,104
144973	156847	0,105
219516	23638	0,106
161324	262143	0,109
228222	9637	0,11
23434	11548	0,115
97904	262143	0,116
184269	2934	0,116
66246	70117	0,121
15694	98298	0,121
201977	4168	0,122
262143	9309	0,129
130036	262143	0,13
23679	153958	0,13
144986	262143	0,131
262143	11184	0,132
54647	7047	0,134
33462	9601	0,136
262143	11350	0,136
131975	262143	0,143
164712	11307	0,143
248156	9540	0,144
262143	4792	0,148
194526	11020	0,148
26650	16506	0,151
31252	17485	0,151
262143	27813	0,152
159179	10440	0,153
38543	262143	0,156
244056	10261	0,16
219929	262143	0,161
262143	9820	0,166
262143	114409	0,167
172029	22872	0,169
36369	12121	0,17
75223	14530	0,172
262143	202575	0,176
262143	228219	0,176
205564	13098	0,178
130765	13197	0,181
25247	262143	0,181
132840	4743	0,183
230926	262143	0,184
229367	60975	0,187
41834	262143	0,187
167527	3340	0,189
262143	5295	0,19
192517	78630	0,191
75129	15438	0,191
227123	5028	0,193
202899	39384	0,194
262143	188376	0,194
249842	209316	0,195
262143	43402	0,197
24591	7539	0,199
29895	262143	0,199
262143	24099	0,2
85268	262143	0,2
157569	91230	0,202
50109	7951	0,204
262143	16942	0,206
199140	8826	0,208
69423	51979	0,21
8340	12405	0,212
77284	32934	0,214
50542	53815	0,22
262143	11875	0,221
185496	8110	0,223
235928	8962	0,228
16569	12853	0,229
72936	127561	0,23
262143	19750	0,231
69665	36978	0,234
262143	8680	0,235
262143	11592	0,239
16829	8196	0,241
160844	6378	0,241
61692	9966	0,245
221172	6480	0,247
50588	19084	0,247
261410	4071	0,25
262143	10866	0,255
262143	44119	0,257
36650	14218	0,257
256870	13270	0,258
249731	127590	0,26
170686	8667	0,264
262143	11466	0,265
70125	9969	0,265
262143	9339	0,266
262143	14751	0,268
253337	11086	0,269
149024	150771	0,276
14611	7261	0,277
143934	262143	0,278
77465	262143	0,28
36166	20020	0,28
59017	262143	0,281
262143	124018	0,282
32017	210142	0,288
250678	24133	0,289
249939	4702	0,294
262143	7326	0,297
95938	4548	0,298
262143	108630	0,299
220617	37149	0,3
69362	18459	0,303
31180	23329	0,303
141705	262143	0,305
18939	15360	0,309
52819	9891	0,313
262143	33103	0,315
132604	6093	0,318
17637	262143	0,321
262143	6874	0,323
186428	11340	0,325
234536	5265	0,327
262143	94633	0,332
35559	98928	0,334
240559	212521	0,341
210310	44485	0,341
260509	226633	0,345
14898	147406	0,347
144886	262143	0,348
262143	16011	0,352
40233	164868	0,353
252652	6778	0,354
262143	10315	0,356
57519	3517	0,36
21703	262143	0,361
262143	12214	0,361
262143	7795	0,364
262143	43965	0,365
221247	3265	0,366
230465	67455	0,367
262143	100972	0,367
27182	15154	0,369
228043	7476	0,372
222215	102160	0,374
262143	7189	0,374
230074	137472	0,375
257993	106971	0,375
215339	17874	0,375
262143	246027	0,376
262143	95436	0,377
185800	262143	0,381
33315	11917	0,381
34651	10714	0,382
100048	262143	0,383
26308	8703	0,384
35637	183232	0,387
139842	14529	0,391
238382	4495	0,394
36274	7990	0,395
117417	10785	0,398
164617	5617	0,399
247435	10231	0,4
45579	8422	0,402
177448	38079	0,402
262143	114636	0,403
262143	262143	0,403
181805	3832	0,408
13652	33594	0,411
164596	2584	0,412
41360	29086	0,415
232743	6070	0,415
242348	10405	0,416
186486	61416	0,417
262143	6211	0,417
241417	7036	0,417
148450	262143	0,42
39215	262143	0,42
262143	8079	0,421
54466	30771	0,421
262143	12664	0,424
192643	70017	0,425
262143	9922	0,425
255001	66639	0,426
262143	9952	0,43
111517	262143	0,431
124364	17415	0,431
235684	37624	0,439
183166	4933	0,44
93024	7038	0,441
258672	17724	0,442
262143	13287	0,442
193439	3094	0,443
262143	55626	0,445
54043	8539	0,447
262143	14353	0,448
134883	9282	0,452
214233	262143	0,456
87781	4981	0,457
83854	16032	0,458
135236	3634	0,459
2856	1948	0,461
46752	83308	0,463
46391	262143	0,466
237048	5067	0,466
52702	8844	0,468
205052	9405	0,469
262143	16179	0,47
262143	11032	0,471
262143	19692	0,474
88904	7228	0,474
262143	5940	0,484
30269	11382	0,485
234454	67758	0,487
262143	38832	0,488
262143	24949	0,491
30863	10336	0,492
39897	90322	0,492
262143	7434	0,493
262143	11038	0,495
262143	3994	0,495
115582	11463	0,495
54524	7858	0,495
235862	87400	0,497
209032	222798	0,498
148989	10587	0,499
262143	7849	0,501
262143	38725	0,502
260434	10812	0,503
262143	97704	0,505
154840	262143	0,506
102155	14650	0,509
235208	10605	0,509
222722	262143	0,51
199674	3795	0,511
27536	22962	0,515
143625	262143	0,518
100402	9649	0,518
237642	9598	0,52
48944	22606	0,521
262143	9907	0,521
190949	8862	0,523
148510	10417	0,523
51920	201604	0,527
256028	2913	0,528
37578	187558	0,529
262143	8346	0,532
262143	43836	0,534
98923	262143	0,534
27284	262143	0,54
241749	45946	0,543
39676	27147	0,544
245199	11949	0,548
262143	127176	0,549
196812	3376	0,549
160851	11596	0,553
262143	8790	0,556
168870	12916	0,557
262143	67593	0,565
84437	262143	0,565
184580	262143	0,566
136774	11311	0,57
133033	7222	0,57
19946	31032	0,573
139683	11170	0,573
179902	11824	0,574
262143	19888	0,58
198079	4080	0,582
66723	262143	0,583
92137	38116	0,585
93500	262143	0,588
262143	16386	0,588
51252	12889	0,591
15690	25861	0,594
40097	262143	0,594
258793	61387	0,595
92456	2352	0,596
217141	180007	0,6
261802	262143	0,606
180392	68086	0,608
262143	11283	0,61
163274	262143	0,611
74590	38347	0,611
243756	4381	0,615
262143	14140	0,619
239047	262143	0,621
237337	31066	0,627
167051	5367	0,63
56329	103252	0,633
191764	3246	0,633
18735	262143	0,635
230961	3426	0,636
262143	68509	0,637
213656	5914	0,638
262143	11091	0,639
19340	18826	0,642
159940	50193	0,642
243138	23035	0,654
46928	104350	0,654
34097	10050	0,655
262143	62205	0,66
132354	12061	0,663
223610	36778	0,664
33041	86590	0,665
247651	14718	0,666
26004	14044	0,668
139872	4494	0,671
250757	7594	0,672
14255	57042	0,673
221237	4885	0,674
225802	7261	0,674
23542	37570	0,677
258460	9355	0,678
68269	40731	0,679
240743	32296	0,679
115647	6981	0,68
262143	72400	0,681
247197	2629	0,682
215456	5883	0,683
185529	262143	0,684
173604	33085	0,687
262143	27739	0,69
262143	77425	0,694
214542	12760	0,694
222392	262143	0,695
44808	16407	0,695
227153	262143	0,696
198621	4020	0,701
241853	15888	0,702
195184	262143	0,705
262143	5982	0,705
214343	262143	0,707
262143	29388	0,707
26012	10293	0,71
262143	37167	0,71
262143	7837	0,714
230324	262143	0,715
27516	262143	0,715
119033	13495	0,716
71909	134898	0,719
262143	37590	0,721
51112	28254	0,727
206781	5031	0,727
97473	262143	0,728
118077	12249	0,728
200105	2965	0,729
193090	27931	0,73
127432	11188	0,734
44799	13227	0,735
80365	93576	0,735
262143	11065	0,738
36108	236353	0,74
70654	36745	0,741
165239	223326	0,742
262143	7318	0,745
55590	59152	0,745
210526	2692	0,751
203273	262143	0,752
262143	153424	0,752
41949	14101	0,757
21482	190888	0,759
22898	13272	0,759
64612	30052	0,762
40570	14824	0,762
218013	10471	0,764
78104	262143	0,768
214725	2980	0,769
79030	148801	0,774
222100	70215	0,776
79780	3687	0,784
217386	3487	0,785
24801	17029	0,785
185019	262143	0,788
14439	262143	0,788
65817	100384	0,796
34435	44673	0,803
165473	7597	0,806
165659	12070	0,806
234846	184641	0,807
27426	161473	0,809
17742	15334	0,812
206279	3295	0,817
35711	262143	0,817
120436	10182	0,819
262143	42819	0,819
262143	14869	0,822
62052	36066	0,823
2532	15040	0,823
23186	14485	0,825
93083	262143	0,828
222153	6114	0,835
257566	104890	0,835
237740	45631	0,838
262143	42375	0,839
258218	16954	0,839
63418	7104	0,839
136646	262143	0,839
262143	7006	0,844
157552	2497	0,846
25155	120243	0,849
125602	189511	0,85
158971	1881	0,85
20333	3931	0,851
32471	13807	0,851
234495	89115	0,851
251279	231376	0,853
101981	23866	0,854
228963	5397	0,863
42023	16359	0,872
262143	12699	0,875
262143	9819	0,876
262143	8415	0,876
210778	67627	0,876
262143	36804	0,879
262143	198069	0,88
36805	6864	0,882
51453	262143	0,885
262143	35547	0,885
244101	93192	0,89
225188	15454	0,892
199422	8721	0,893
262143	78888	0,898
191314	262143	0,9
74305	262143	0,901
262143	10680	0,903
65643	45312	0,906
22398	15690	0,911
32473	262143	0,912
38536	43422	0,913
55614	35362	0,913
229237	58861	0,915
251358	7815	0,915
42166	24463	0,916
156494	262143	0,917
110661	155854	0,918
39120	231777	0,919
43793	199249	0,919
260642	10254	0,92
262143	74781	0,924
239245	4479	0,929
191723	3940	0,931
262143	13081	0,933
67613	3658	0,933
249401	212209	0,933
222318	4026	0,937
262143	84736	0,938
262143	28332	0,939
20053	17184	0,941
47436	25557	0,943
253032	58347	0,946
219013	4765	0,946
246886	72774	0,946
43781	51057	0,948
262143	8277	0,948
73206	55393	0,948
234322	12066	0,95
142026	6381	0,957
88748	13536	0,959
53304	8697	0,96
208951	5542	0,962
231186	97024	0,962
262143	8524	0,967
56751	14404	0,969
49259	2460	0,976
262143	4981	0,977
34692	15744	0,978
40014	19822	0,98
262143	23596	0,982
247374	15193	0,982
262143	18763	0,985
207401	4434	0,986
262143	5593	0,988
135039	262143	0,99
236420	47874	0,992
262143	9822	0,992
55422	258012	0,995
250810	9750	0,996
29189	3631	1
240295	4351	1,003
262143	12318	1,004
86974	68475	1,004
137484	262143	1,007
248412	3118	1,007
56363	34017	1,008
10943	108171	1,008
27956	72102	1,013
46749	69868	1,013
4438	23238	1,018
229

Ok, I renamed your file inputfile.txt and the code below produces an outputfile.txt containing the averages.

# averages.py
from itertools import groupby

def gen_averages(input_triples):
    for second, triples in groupby(input_triples, lambda triple: int(triple[2])):
        a, b = 0.0, 0.0
        n = 0
        for x, y, t in triples:
            a += float(x)
            b += float(y)
            n += 1
        yield (a/n, b/n, second)

def gen_input(textfile):
    f = open(textfile)
    f.readline()
    for line in f:
        triple = tuple(float(x) for x in line.strip().replace(",", ".").split())
        assert(len(triple) == 3)
        yield triple

def compute_averages(inputfile, outputfile):
    with open(outputfile, "w") as out:
        for triple in gen_averages(gen_input(inputfile)):
            out.write("{0:<15.2f}{1:<15.2f}{2:d}\n".format(*triple))
if __name__ == "__main__":
    compute_averages("inputfile.txt", "outputfile.txt")
    print "done (see the output file)"

To run the program, you have 2 solutions: either you start IDLE (python gui), then you open averages.py (the name of the program) and you select 'Run Module' in idle's run menu, or you open a windows cmd shell, you go to the folder containing the program and you type python averages.py on the command line.

The program must be modified if you want to read and write excel files instead of text files.

It works perfectly!!!! Thank you so much:-)
What do you want for it? I can pay you via my paypal account, would that work for you?

Best regards, Casper:-)

It works perfectly!!!! Thank you so much:-)
What do you want for it? I can pay you via my paypal account, would that work for you?

Best regards, Casper:-)

It's ok. It's only a small exercise for most members of the python forum :)

Also, there is a little theoretical problem: your time samples are not uniformly distributed over the 1 second time intervals, so are you sure that a raw averaging is meaningful ? Shouldn't we design a special formula for the average over 1 second ?

Edited 6 Years Ago by Gribouillis: n/a

Comments
nice way to handle this

Really, wow thanks:-) The raw average should be meaningful, the values are a meassure of how individual cells scatter light, the scatter increases when the cells shrink. So the average scatter correlates well with the fraction of the cells sampled within one second that have shrunk.

This is all i need, thanks a bunch:-)

This question has already been answered. Start a new discussion instead.