#!/usr/bin/env python
# coding: utf-8

# # Using Pivoted DataFrames with Perspective
# 
# Perspective tries to infer as much information as possible from already-pivoted dataframes:

# In[ ]:


import pandas as pd
import numpy as np
from perspective import Table, PerspectiveWidget


# For the dataset, we'll use `superstore.arrow` which is used in various Perspective demos:

# In[ ]:


import requests

# Download the arrow
url = "https://unpkg.com/@jpmorganchase/perspective-examples@0.2.0-beta.2/build/superstore.arrow"
req = requests.get(url)
arrow = req.content


# In[ ]:


# Create a table
table = Table(arrow)
view = table.view()
df = view.to_df()
display(df.shape, df.dtypes)


# ### Row Pivots
# 
# Create a row pivoted dataframe, and pass it into `PerspectiveWidget`:

# In[ ]:


df_pivoted = df.set_index(['Country', 'Region'])


# In[ ]:


df_pivoted.head()


# In[ ]:


# Pivots will be read from the df and applied
PerspectiveWidget(df_pivoted)


# ### Column Pivots
# 
# The same is true with column pivots:

# In[ ]:


arrays = [np.array(['bar', 'bar', 'bar', 'bar', 'baz', 'baz', 'baz', 'baz', 'foo', 'foo', 'foo', 'foo', 'qux', 'qux', 'qux', 'qux']),
          np.array(['one', 'one', 'two', 'two', 'one', 'one', 'two', 'two', 'one', 'one', 'two', 'two', 'one', 'one', 'two', 'two']),
          np.array(['X', 'Y', 'X', 'Y', 'X', 'Y', 'X', 'Y', 'X', 'Y', 'X', 'Y', 'X', 'Y', 'X', 'Y'])]
tuples = list(zip(*arrays))
index = pd.MultiIndex.from_tuples(tuples, names=['first', 'second', 'third',])

df_col = pd.DataFrame(np.random.randn(3, 16), index=['A', 'B', 'C'], columns=index)
df_col


# In[ ]:


# Pivots again automatically applied
PerspectiveWidget(df_col)


# ### Pivot Table (Row and Column Pivots)

# In[ ]:


pt = pd.pivot_table(df, values = 'Discount', index=['Country','Region'], columns = ['Category', 'Segment'])
pt


# In[ ]:


PerspectiveWidget(pt)


# ### More pivot examples

# In[ ]:


arrays = {'A':['bar', 'bar', 'bar', 'bar', 'baz', 'baz', 'baz', 'baz', 'foo', 'foo', 'foo', 'foo', 'qux', 'qux', 'qux', 'qux'],
          'B':['one', 'one', 'two', 'two', 'one', 'one', 'two', 'two', 'one', 'one', 'two', 'two', 'one', 'one', 'two', 'two'],
          'C':['X', 'Y', 'X', 'Y', 'X', 'Y', 'X', 'Y', 'X', 'Y', 'X', 'Y', 'X', 'Y', 'X', 'Y'],
          'D':np.arange(16)}

df2 = pd.DataFrame(arrays)
df2


# In[ ]:


df2_pivot = df2.pivot_table(values=['D'], index=['A'], columns=['B','C'], aggfunc={'D':'count'})
df2_pivot


# In[ ]:


PerspectiveWidget(df2_pivot)


# In[ ]:


pt = pd.pivot_table(df, values = ['Discount','Sales'], index=['Country','Region'],aggfunc={'Discount':'count','Sales':'sum'},columns=["State","Quantity"])
pt


# In[ ]:


PerspectiveWidget(pt)