0

I have a table like this:

vstid vstrseq  date       page   timespent
1       1     1/1/16      a       20.00
1       1     1/1/16      b       3.00
1       1     1/1/16      c       131.00
1       1     1/1/16      d        .000
1       1     1/1/16      a       3.00

I want this like:

A      B             date                  a      b      c       d
1      1             1/1/16                23     3      131     0

How can I get it done in python? Any suggestions?

FCo
  • 485
  • 5
  • 17

1 Answers1

0

You could use pandas' pivot table for this:

import pandas as pd
import numpy as np

df = pd.DataFrame({
    "vstid": [1]*5,
    "vstrseq": [1]*5,
    "date": ["1/1/16"]*5,
    "page": ["a", "b", "c", "d", "a"],
    "timespent": [20.00, 3.00, 131.00, 0.000, 3.00]
    })

table = df.pivot_table(index=["vstid", "vstrseq", "date"], values="timespent", columns="page", aggfunc=np.sum).reset_index()

print table.to_string(index=False)

which outputs

 vstid  vstrseq    date   a  b    c  d
     1        1  1/1/16  23  3  131  0
Plasma
  • 1,903
  • 1
  • 22
  • 37
  • instead of cretating a datafram, cant i pivot it directly @Plasma –  Apr 15 '16 at 19:53
  • how can i pass my whole table to dataframe as i dont know what data my tables columns will have. @Plasma –  Apr 15 '16 at 19:54
  • @reddyreddy what kind of data structures are you working with? There are tons of ways to read things to dataframes. If your data are list-like, just pass in the variable names instead of a hard-coded list. Also, check out [this](http://stackoverflow.com/questions/11428271/produce-a-summary-pivot-table) question which creates pivot tables from a list of tuples – Plasma Apr 16 '16 at 06:23