0

I'm doing the calculation in periscopedata and I want to transpose some of the tables to the desired output.

i.e)

F.name      Date       Result
gross_sale  2015/01/01 450
gross_sale  2015/01/02 554
gross_sale  2015/01/03 439
sale_disc   2015/01/01 31
sale_dsic   2015/01/03 22

I want the result look like this

F.name     2015/01/01 2015/01/02 2015/01/03
gross_sale 450        554        439
sale_disc  31         0          22

periscopedata support pandas Dataframes and I've tried below but that throws error. 

import pandas as pd

df2 = df.T

periscope.table(df2)

Guide me how to adjust the python code so that I can get the correct result I want.

Cloud Learner
  • 111
  • 1
  • 1
  • 8

1 Answers1

0
df = pd.DataFrame({"F.name": ["gross_sale", "gross_sale", "gross_sale", "sale_disc", "sale_disc"],
                  "Date": ["2015/01/01", "2015/01/02", "2015/01/03", "2015/01/01", "2015/01/03"],
                  "Result": [450, 554, 439, 31, 22]})

df.pivot(index='F.name', columns='Date', values='Result').fillna(0)
Ted
  • 1,189
  • 8
  • 15
  • Why I'm getting `pandas.core.base.DataError: No numeric types to aggregate`? – Cloud Learner Aug 21 '19 at 14:13
  • @CloudLearner Try `df["Result"] = df["Result"].apply(pd.to_numeric)` before the pivot command. – Ted Aug 21 '19 at 14:15
  • Okay now I'm not getting the F.name in the final result but the result coming correct. Do I need to reset the index probably `reindex` to get the result . – Cloud Learner Aug 21 '19 at 14:19
  • @CloudLearner Oh yes, I had assumed that you had `F.name` not as the index. Call `df.reset_index(inplace=True)` before the `pivot` – Ted Aug 21 '19 at 14:38