Create Pivot Table from Python?

[erikcw]

Hi,

I'm trying to figure out how to create a pivot table from a
dynamically generated spreadsheet. Can anyone point me in the right
direction?

Thanks!
Erik

[John Machin]

Maybe, if I could figure out what you are asking. Sorry for being more
obtuse than normal, but it's been about 40 degrees Celsius here and
rolling power cuts have forced me to take the palantir offline.

By "pivot table" do you mean a static "crosstab", or something like the
PivotTable (TM) of Microsoft Excel, which is a dynamic gadget that the
user can manipulate?

Why "create a PT *from* a spreadsheet? Do you mean "create a PT and
insert into a spreadsheet"?

What is, and what is the relevance of, "dynamically generated"?

Cheers,
John

[erikcw]

Hi John,

I'm referring to "PivotTable (TM) of Microsoft Excel". Basically I
want to export some reporting data from a server side python script
into an excel spreadsheet. I'd like the generated spreadsheet file to
contain a Pivot Table object to help the end user manipulate the data.

Thanks!
Erik

[John Machin]

On 31/01/2009 3:16 PM, erikcw wrote:
> Hi John,
>
> I'm referring to "PivotTable (TM) of Microsoft Excel". Basically I
> want to export some reporting data from a server side python script
> into an excel spreadsheet. I'd like the generated spreadsheet file to
> contain a Pivot Table object to help the end user manipulate the data.
>

Thanks for the clarification.

At the moment the prime candidate would be driving Excel via COM. A
distant second would be using pyUNO to script OpenOffice's Calc -- among
other contraindications may well be that their "DataPilot" (no TM AFAIK)
doesn't have the same functionality as MS's PivotTable (TM).

Cheers,
John

[erikcw]

I was afraid that was going to be the answer. I'm generating the
spreadsheet using xlwt on a Linux webserver. So Excel/COM is out and
the server isn't running X windows.

[Ana Nelson]

Can you do the pivot table analysis in some other way and just
incorporate the results into the spreadsheet? Or do your end users
need to be able to manipulate the pivot table themselves?

[erikcw]

End users need to be able to manipulate the pivot table themselves.

[Patrick Waldo]

Hi Everyone,

I asked this very question a while ago and I got some good responses.
Check out http://groups.google.com/group/comp.lang.python/browse_thread/thread/47a56e764ea4d73a/c528fcb850f5c9d5?lnk=gst&q=Pivot+Table#c528fcb850f5c9d5

Title was Pivot Table/Groupby/Sum question. Back then I was a newbie
programmer (not that I'm so great now) so I'm sure there are better
ways to go about it since I use Python to help me at work.
Unfortunately, I'm on a business trip right now and away from my
machine with the code. In a couple of days I'll be able to give a
more specific response, since I have code that generates a Pivot Table
from a previous excel sheet.

Best,
Patrick

[Ana Nelson]

What about giving them a built-in Visual Basic macro and button so the
pivot table can be generated with just one click?

It's not as good as a ready-made pivot table but maybe better than
nothing.

[erikcw]

So can I generate the spreadsheet from scratch with xlwt, or do I
create the spreadsheet then just use xlwt to edit/add data?

[Ana Nelson]

Okay, I got ahead of myself and was thinking you could write VB macro
pages into workbooks but that's not possible at this point.

You could put the macro into a separate workbook, and it would be
available to your users as long as macros.xls was open when they open
my-xlwt-workbook-filled-with-yummy-data.xls

macros.xls can just have the macro linked to a keyboard shortcut, and
then you can have instructions for your users in the xlwt book which
say "make sure you have macros.xls open, then type ctrl+shift+p (or
whatever) to create a pivot table".

[John Machin]

[Please type your responses at the OTHER END of the message. Please trim
text that has been more seen more than once already.]

On 2/02/2009 8:32 AM, Ana Nelson wrote:
> Okay, I got ahead of myself and was thinking you could write VB macro
> pages into workbooks but that's not possible at this point.

Some developers have interesting reactions to suggestions like that.
Here's one example. Fire up a Python interactive prompt, and type:
from __future__ import braces
:-)

> You could put the macro into a separate workbook, and it would be
> available to your users as long as macros.xls was open when they open
> my-xlwt-workbook-filled-with-yummy-data.xls
>
> macros.xls can just have the macro linked to a keyboard shortcut, and
> then you can have instructions for your users in the xlwt book which
> say "make sure you have macros.xls open, then type ctrl+shift+p (or
> whatever) to create a pivot table".

Not a big jump from that to training the users to create the pivot table
themselves.

On Jan 31, 8:13 pm, erikcw <erikwickst...@gmail.com> wrote:
>>>> End users need to be able to manipulate the pivot table themselves.

Need or want? Earlier you said it was to manipulate the data. Can you
please be a little more specific? Does it involve anything more
complicated than a two-way cross-tabulation of ony one variable e.g.
"give me total revenue tabulated by region and product type"?

Does manipulate possibly mean neither "change the data" nor "fiddle with
the pivot table mechanism" but something like "add some extra
columns/rows with formulas" to get some extra statistics? In that case
you can put one or more cross-tabulations plus a bunch of formulas in
the output file with xlwt. Or forget the formulas and just calculate
results in Python.

One disadvantage of an in-Excel PivotTable (TM) is that all of the
underlying data (needs to be | is) carried around ... with a secondary
effect that often user changes seem to take a long time.

Patrick Waldo mentioned the SimplePivotTable gadget (really just a 2-way
crosstab gadget) that I lashed up a while back in response to his
question. Then it was "tested as much as you see"; now it has been
battle-hardened in scenarios like I described above. Anyone want a copy?

[Ana Nelson]

> Not a big jump from that to training the users to create the pivot table
> themselves.

I would disagree here. It's much quicker and easier, not to mention
less
error prone, for users to trigger a macro to create a pivot table than
to take 10 or 20 clicks to do it via the Wizard, especially if this is
going to happen on a daily basis. Also, from the sounds of it, the
users
in question already know how to create pivot tables (since they want
access to live, interactive pivot tables they can play with as opposed
to static data which looks like a pivot table).

> Patrick Waldo mentioned the SimplePivotTable gadget (really just a 2-way
> crosstab gadget) that I lashed up a while back in response to his
> question. Then it was "tested as much as you see"; now it has been
> battle-hardened in scenarios like I described above. Anyone want a copy?

I would be interested in a copy.

[Chris Withers]

John Machin wrote:
>
> Patrick Waldo mentioned the SimplePivotTable gadget (really just a 2-way
> crosstab gadget) that I lashed up a while back in response to his
> question. Then it was "tested as much as you see"; now it has been
> battle-hardened in scenarios like I described above. Anyone want a copy?

Is this a canidate for inclusing in xlutils?

Chris

--
Simplistix - Content Management, Zope & Python Consulting
- http://www.simplistix.co.uk