How do i add a sum coulmn to data?

This is the place for queries that don't fit in any of the other categories.

How do i add a sum coulmn to data?

Postby henoit » Tue May 13, 2014 9:50 am

Hi all,

I have done some work in VBA before but am new to python and am trying to get the correct way of getting the scores to sum up for each player (separately for home and away).

So I have this table(from excel):

Code: Select all
>>> print(tabulate(data))
hom awa hsc asc
tom    pete    1.0                   3.0
pete   gary     2.0                   0.0
gary    tom     4.0                   2.0
tom     gary    3.0                   3.0
tom     pete    1.0                   2.0
gary    pete    1.0                   1.0
pete    tom     2.0                   4.0
gary    tom
tom     pete
pete    gary


and basically I want to append a column to the hsc (some score) and asc(away score) column that:
gives the total score of the given player at the location for the matches before the current one. For example in column 6, where tom plays pete i want an additional column that adds up all toms scores at home i.e.(1+3) = 4, as well as one which adds up all petes scores away (3) = 3.

so this table would look like so when completed:

Code: Select all
hom awa hsc hag asc aag
tom    pete    1.0 0.0 3.0 0.0
pete   gary     2.0 0.0 0.0 0.0
gary    tom     4.0 0.0 2.0 0.0
tom     gary    3.0 1.0 3.0 0.0
tom     pete    1.0 4.0 2.0 3.0
gary    pete    1.0 4.0 1.0 5.0
pete    tom     2.0 2.0 4.0 2.0
gary    tom     ---- 5.0 ---- 6.0
tom     pete    ---- 5.0 ---- 6.0
pete    gary     ---- 4.0 ---- 3.0


where (hag = home aggregate) and (aag = away aggregate)

so im guessing I need to use some form of sumif function but I am struggling to get started.

Any help would be greatly appreciated.
Last edited by Yoriz on Tue May 13, 2014 11:49 am, edited 1 time in total.
Reason: First post lock, Added code tags, Changed title.
henoit
 
Posts: 3
Joined: Mon May 12, 2014 3:50 am

Re: How do i add a sum coulmn to data?

Postby Yoriz » Tue May 13, 2014 11:50 am

Hi welcome to the forum.
Please read the new users read this link in my signature.
New Users, Read This
Join the #python-forum IRC channel on irc.freenode.net!
Spam topic disapproval technician
Windows7, Python 2.7.4., WxPython 2.9.5.0., some Python 3.3
User avatar
Yoriz
 
Posts: 837
Joined: Fri Feb 08, 2013 1:35 am
Location: UK

Re: How do i add a sum coulmn to data?

Postby Kebap » Tue May 13, 2014 1:22 pm

Hi henoit, welcome to python and the forums. Hey I once started with VBA, too. Good thing I found python.

Check out this example:

Code: Select all
my_list = [2, 3, 5, 17, 23]
my_sum = 0
for my_value in my_list:
    if my_value >= 5:
        my_sum = my_sum + my_value


There are shorter ways, too, but this should be easily readable.

Code: Select all
my_sum = sum(v for v in [2, 3, 5, 17, 23] if v >= 5)
Learn: How To Ask Questions The Smart Way
Join the #python-forum IRC channel on irc.freenode.net and chat with uns directly!
Kebap
 
Posts: 396
Joined: Thu Apr 04, 2013 1:17 pm
Location: Germany, Europe

Re: How do i add a sum coulmn to data?

Postby henoit » Thu May 15, 2014 12:06 pm

Thanks both,

The example definitely helped. I currently have:

Code: Select all
>>> data
[['name 1', 'name 2', 'score 1', 'score 2'], ['tom', 'pete', 1.0, 3.0], ['pete', 'gary', 2.0, 0.0], ['gary', 'tom', 4.0, 2.0], ['tom', 'gary', 3.0, 3.0], ['tom', 'pete', 0.0, 2.0], ['gary', 'pete', 1.0, 1.0], ['pete', 'tom', 2.0, 4.0], ['gary', 'tom', '', ''], ['tom', 'pete', '', ''], ['pete', 'gary', '', '']]


Is what I am working with, and the attempt is:

Code: Select all
for x in range (len(data)) in data:
   score = data[x][3]
   for z in range ((len(data)-1) in data:
      if data[x][1] = data[z][1]:
         total = total + score
         data[x].append(total)


I want it to count the number of goals previously scored at home by the player playing at home (not including the current match).


am I close at all?

Henoit
henoit
 
Posts: 3
Joined: Mon May 12, 2014 3:50 am

Re: How do i add a sum coulmn to data?

Postby Kebap » Thu May 15, 2014 2:27 pm

henoit wrote:am I close at all?

The way I like it, I test in interactive mode. You can just create a simple small example for you to try the new commands, then see if you're close. Otherwise, deal with the error message, and/or come back and show us the messages. This way, you do not have to write the whole script at once perfectly.

One thing I noticed you do, it is quite popular in other languages:

Code: Select all
for x in range (len(data)):
     print data[x]


In Python however, we just do this:

Code: Select all
for item in data:
     print item


You seem to confuse the both now, sorry if I caused this. ;)
Learn: How To Ask Questions The Smart Way
Join the #python-forum IRC channel on irc.freenode.net and chat with uns directly!
Kebap
 
Posts: 396
Joined: Thu Apr 04, 2013 1:17 pm
Location: Germany, Europe

Re: How do i add a sum coulmn to data?

Postby henoit » Sun May 18, 2014 10:01 am

thanks for the help and advice, Ill have a good work through splitting it up
henoit
 
Posts: 3
Joined: Mon May 12, 2014 3:50 am


Return to General Coding Help

Who is online

Users browsing this forum: No registered users and 3 guests