473,225 Members | 1,375 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,225 software developers and data experts.

Pivot Table/Groupby/Sum question

Hi all,

I tried reading http://aspn.activestate.com/ASPN/Coo.../Recipe/334695
on the same subject, but it didn't work for me. I'm trying to learn
how to make pivot tables from some excel sheets and I am trying to
abstract this into a simple sort of example. Essentially I want to
take input data like this:

Name Time of day Amount
Bob Morn 240
Bob Aft 300
Joe Morn 70
Joe Aft 80
Jil Morn 100
Jil Aft 150

And output it as:

Name Total Morning Afternoon
Bob 540 240 300
Joe 150 70 80
Jil 250 100 150
Total 940 410 530

The writing the output part is the easy part. However, I have a
couple problems. 1) Grouping by name seems to work perfectly, but
working by time does not. ie

I will get:
Bob
240
300
Joe
70
80
Jil
100
150
which is great but...
Morn
240
Aft
300
Morn
70
Aft
80
Morn
100
Aft
150
And not
Morn
240
70
100
Aft
300
80
150

2) I can't figure out how to sum these values because of the
iteration. I always get an error like: TypeError: iteration over non-
sequence

Here's the code:

from itertools import groupby

data = [['Bob', 'Morn', 240],['Bob', 'Aft', 300],['Joe', 'Morn', 70],
['Joe', 'Aft', 80],\
['Jil', 'Morn', 100],['Jil', 'Aft', 150]]

NAME, TIME, AMOUNT=range(3)
for k, g in groupby(data, key=lambda r: r[NAME]):
print k
for record in g:
print "\t", record[AMOUNT]
for k, g in groupby(data, key=lambda r: r[TIME]):
print k
for record in g:
print "\t", record[AMOUNT]

Thanks for any comments
Dec 27 '07 #1
9 2963
What about to let SQL to work for you.
HTH

Petr Jakes

Tested on Python 2.5.1
8<----------------------

#!/usr/bin/env python
# -*- coding: utf-8 -*-
import sqlite3
con = sqlite3.connect(":memory:")
cur = con.cursor()

inputData=(
('Bob', 'Morn', 240),
('Bob', 'Aft', 300),
('Joe', 'Morn', 70),
('Joe', 'Aft', 80),
('Jil', 'Morn', 100),
('Jil', 'Aft', 150),
('Jil', 'Aft', 150),
('Jil', 'Aft', 150))

def data_generator(dataSet):
for dataSetRow in dataSet:
yield dataSetRow

pivotSelect='''
SELECT
NAME,
sum (AMOUNT) as TOTAL,
sum (case when (TIME_OF_DAY) = 'Morn' then AMOUNT else 0 END) as
MORN,
sum (case when (TIME_OF_DAY) = 'Aft' then AMOUNT else 0 END) as AFT
FROM MY_NAMES
GROUP BY 1'''

cur.execute("create table MY_NAMES(NAME, TIME_OF_DAY, AMOUNT)")
cur.executemany("""insert into MY_NAMES(NAME, TIME_OF_DAY, AMOUNT)
values (?,?,?)""", data_generator(inputData))
cur.execute(pivotSelect)
for row in cur.fetchall():
print row
Dec 28 '07 #2
On Dec 29, 9:58 am, petr.jakes....@gmail.com wrote:
What about to let SQL to work for you.
The OP is "trying to learn how to make pivot tables from some excel
sheets". You had better give him a clue on how to use ODBC on an
"excel sheet" :-)

[snip]
SELECT
NAME,
sum (AMOUNT) as TOTAL,
sum (case when (TIME_OF_DAY) = 'Morn' then AMOUNT else 0 END) as
MORN,
sum (case when (TIME_OF_DAY) = 'Aft' then AMOUNT else 0 END) as AFT
This technique requires advance knowledge of what the column key
values are (the hard-coded 'Morn' and 'Aft').

<rant>
It is the sort of thing that one sees when %SQL% is the *only*
language used to produce end-user reports. Innocuous when there are
only 2 possible columns, but bletchworthy when there are more than 20
and the conditions are complex and the whole thing is replicated
several times in the %SQL% script because either %SQL% doesn't support
temporary procedures/functions or the BOsFH won't permit their use...
not in front of the newbies, please!
</rant>
Dec 29 '07 #3
Petr, thanks for the SQL suggestion, but I'm having enough trouble in
Python.

John would you mind walking me through your class in normal speak? I
only have a vague idea of why it works and this would help me a lot to
get a grip on classes and this sort of particular problem. The next
step is to imagine if there was another variable, like departments and
add up the information by name, department, and time, and so on...that
will come another day.

Thanks.

On Dec 29, 1:00 am, John Machin <sjmac...@lexicon.netwrote:
On Dec 29, 9:58 am, petr.jakes....@gmail.com wrote:
What about to let SQL to work for you.

The OP is "trying to learn how to make pivot tables from some excel
sheets". You had better give him a clue on how to use ODBC on an
"excel sheet" :-)

[snip]
SELECT
NAME,
sum (AMOUNT) as TOTAL,
sum (case when (TIME_OF_DAY) = 'Morn' then AMOUNT else 0 END) as
MORN,
sum (case when (TIME_OF_DAY) = 'Aft' then AMOUNT else 0 END) as AFT

This technique requires advance knowledge of what the column key
values are (the hard-coded 'Morn' and 'Aft').

<rant>
It is the sort of thing that one sees when %SQL% is the *only*
language used to produce end-user reports. Innocuous when there are
only 2 possible columns, but bletchworthy when there are more than 20
and the conditions are complex and the whole thing is replicated
several times in the %SQL% script because either %SQL% doesn't support
temporary procedures/functions or the BOsFH won't permit their use...
not in front of the newbies, please!
</rant>
Dec 29 '07 #4
On Dec 29, 11:51 am, patrick.wa...@gmail.com wrote:
John would you mind walking me through your class in normal speak?
Yes.
I
only have a vague idea of why it works and this would help me a lot to
get a grip on classes and this sort of particular problem.
It's about time you got a *concrete* idea of how something works. Grab
a pencil and a large piece of paper, pretend you are python.exe and
follow through what happens when it executes
ptab = SimplePivotTable(.......)
and the ptab.add_item(......) loop with this set of data:
data = [
['Bob', 'Morn', 240],
['Bob', 'Aft', 300],
['Bob', 'Morn', 40],
]
with the goal of being able to say what is in ptab.cell_dict and
understanding how it got there.

Cheers,
John
Dec 29 '07 #5
Patrick,

in your first posting you are writing "... I'm trying to learn how to
make pivot tables from some excel sheets...". Can you be more specific
please? AFIK Excel offers very good support for pivot tables. So why
to read tabular data from the Excel sheet and than transform it to
pivot tabel in Python?

Petr

Dec 29 '07 #6
On Dec 29, 3:00 pm, petr.jakes....@gmail.com wrote:
Patrick,

in your first posting you are writing "... I'm trying to learn how to
make pivot tables from some excel sheets...". Can you be more specific
please? AFIK Excel offers very good support for pivot tables. So why
to read tabular data from the Excel sheet and than transform it to
pivot tabel in Python?

Petr
Yes, I realize Excel has excellent support for pivot tables. However,
I hate how Excel does it and, for my particular excel files, I need
them to be formated in an automated way because I will have a number
of them over time and I'd prefer just to have python do it in a flash
than to do it every time with Excel.
>It's about time you got a *concrete* idea of how something works.
Absolutely right. I tend to take on ideas that I'm not ready for, in
the sense that I only started using Python some months ago for some
basic tasks and now I'm trying on some more complicated ones. With
time, though, I will get a concrete idea of what python.exe does, but,
for someone who studied art history and not comp sci, I'm doing my
best to get a handle on all of it. I think a pad of paper might be a
good way to visualize it.
Dec 29 '07 #7
Yes, I realize Excel has excellent support for pivot tables. However,
I hate how Excel does it and, for my particular excel files, I need
them to be formated in an automated way because I will have a number
of them over time and I'd prefer just to have python do it in a flash
than to do it every time with Excel.
Patrick,

Few more questions:
- Where the data come from (I mean: are your data in Excel already
when you get them)?

- If your primary source of data is the Excel file, how do you read
data from the Excel
file to Python (I mean did you solve this part of the task already)?

Petr
Dec 29 '07 #8
Yes in the sense that the top part will have merged cells so that
Horror and Classics don't need to be repeated every time, but the
headers aren't the important part. At this point I'm more interested
in organizing the data itself and i can worry about putting it into a
new excel file later.
Jan 3 '08 #9
On Jan 4, 4:55 pm, patrick.wa...@gmail.com wrote:
Petr thanks so much for your input. I'll try to learnSQL, especially
if I'll do a lot of database work.

I tried to do it John's way as en exercise and I'm happy to say I
understand a lot more. Basically I didn't realize I could nest
dictionaries like db = {country:{genre:{sub_genre:3}}} and call them
like db[country][genre][sub_genre]. The Python Cookbook was quite
helpful to figure out why items needed to be added the way they did.
Also using the structure of the dictionary was a conceptually easier
solution than what I found onhttp://aspn.activestate.com/ASPN/Cookbook/Python/Recipe/334695.

So, now I need to work on writing it to Excel. I'll update with the
final code.
Hi, good to know you have succeded. I think it is matter of taste
which way to go (dictionary or database). My feelig is: for data use
database! If you are trying to work with data, you will need it sooner
or later anyway. Again: database is made for data! :-)

Writing your data to excel?
Just save your numbers separated by commas in the file with the
extension csv (my_data.csv) and you can open it directly in Excel.

Good luck :-)

Petr
Jan 4 '08 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Rob | last post by:
I'm just getting around to using pivot tables and charts. I find the Pivot table interface to be INCREDIBLY frustrating. When I view a table in Design view, then choose Pivot table view, I get...
1
by: peter | last post by:
Dear all, I have an existing query called 'A', but I want it to view in Pivot Table. What I do is : - Double click the query and choose Pivot Table view. - I make some changes by adding some...
9
by: PeteCresswell | last post by:
I've got something called "Reference Rates". The idea is that on a given day, we have various rates of return for various entities. e.g. Libor 3-month return, Libor 6-month return, US Treasury...
0
by: salad | last post by:
I have A97 and A2003. I created a form using the Pivot Table wizard. This creates an unbound form with an embedded OLE object containing a Pivot table in Excel. There are 2 controls when the...
5
by: Pourya99 | last post by:
Hello, I have an Access Data Access Page which has a pivot table. The data source of the pivot table is a SQL database table. The data in the pivot table itself is not a problem. I have a text...
3
by: Nassa | last post by:
Hi everyone, I have 3 tables: Telbook(Id:int,Name:char,address:char,comment:ntext,owneruserid:int), PK:id...
4
bhcob1
by: bhcob1 | last post by:
Hi guys, I need to create a Pivot Table on my database, I don't know much about them. I want the user to be able to direct themselves from my main switchboard (via a cmd button) to the pivot...
1
by: Adu | last post by:
Pivot Tables -------------------------------------------------------------------------------- Hi guys, I have two questions: First Question: I have created a Pivot Table on my Access database....
1
by: thomas.wordsworth | last post by:
This is a bit of a two-part question. 1. Is there a way to control what filters are placed on a pivot chart via vba. I can easily change the underlying query and refresh pivot chart but it's...
1
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
0
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.