Programming help please

[Bob Mehew]

Hi

Can someone help with what probably is producing a small sorting program.  I have an array of 2 column by 2600 plus rows reflecting the age and length of membership of a large sample of BCA?s membership.  The data has been sorted by ?age? and then ?years member?.  What I am after is a program which will take this data and produce a new array with columns of ?years members? and rows of ?age? and insert at each cell the number of members matching that combination.  So for example, I have five rows of data representing six year olds who have joined and have been members for 1, 2, 5, 5 & 7 years respectively.  What I am after is an output showing

?- 1 2 3 4 5 6 7 8
5
6 1 1 0 0 2 0 1 0
7
8

At 2600 + rows, I am not going to do it by hand.  And my programming has driven me mad since it fails to finish presumably because my loop structure goes off to infinity.

Please PM me if you think you can help.

 

[andrewmcleod]

https://repl.it/L6Zz/23

Horrifically dirty, lazy programming and probably buggy - but in my defence I haven't done any coding in a year

(would be cleaner with NumPy...)

 

[Cookie]

If I've understood the question, you can probably do that with an Excel pivottable - no programming required.

 

[Bob Mehew]

I have been passed a solution in Excel using Countif.  Many thanks for all the suggestions.  I will take a look at the python program in the morning when I finished with the ballot (50 minutes to go).

 

[Bob Mehew]

[quote author=andrewmc]
Horrifically dirty, lazy programming and probably buggy
[/quote]
many thanks, it works.

 

[RichardB1983]

For excel, you'd probably need the "COUNTIFS" function, which can count based on more than one variable - so you can sum up records by both age and membership length, rather than "COUNTIF" which can only count on one condition.

 

[Bob Mehew]

For excel, you'd probably need the "COUNTIFS" function,

Appologies to Andy as he did use the CountifS function.  I misreported his work.  :-[