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

# # Pandas DataFrame with N/As
# 
# Solving GitHub [issue number 6](https://github.com/ideonate/nb2xls/issues/6)
# 
# [Source](https://pandas.pydata.org/pandas-docs/stable/user_guide/cookbook.html#cookbook-multi-index)

# In[1]:


import pandas as pd
import numpy as np
np.random.seed(0)

df = pd.DataFrame([['a',1,123], ['a',2,345], ['b',1,678], ['a',1,345]]).set_index([0,1])
df


# In[2]:


df = pd.DataFrame({'row': [0, 1, 2],
                    'One_X': [1.1, 1.1, 1.1],
                    'One_Y': [1.2, 1.2, 1.2],
                    'Two_X': [1.11, 1.11, 1.11],
                    'Two_Y': [1.22, 1.22, 1.22]})
df


# In[3]:


df = df.set_index('row'); df


# In[4]:


df.columns = pd.MultiIndex.from_tuples([tuple(c.split('_'))
                                         for c in df.columns])

df 


# In[5]:


df = df.stack(0).reset_index(1); df


# In[6]:


df.columns = ['Sample', 'All_X', 'All_Y']; df


# In[7]:


cols = pd.MultiIndex.from_tuples([(x, y) for x in ['A', 'B', 'C']
                                   for y in ['O', 'I']])

df = pd.DataFrame(np.random.randn(2, 6), index=['n', 'm'], columns=cols)
df


# In[8]:


df = df.div(df['C'], level=1); df


# In[9]:


coords = [('AA', 'one'), ('AA', 'six'), ('BB', 'one'), ('BB', 'two'),
           ('BB', 'six')]

index = pd.MultiIndex.from_tuples(coords)

df = pd.DataFrame([11, 22, 33, 44, 55], index, ['MyData']); df


# In[10]:


import itertools

index = list(itertools.product(['Ada', 'Quinn', 'Violet'],
                                ['Comp', 'Math', 'Sci']))

headr = list(itertools.product(['Exams', 'Labs'], ['I', 'II']))

indx = pd.MultiIndex.from_tuples(index, names=['Student', 'Course'])

cols = pd.MultiIndex.from_tuples(headr)   # Notice these are un-named

data = [[70 + x + y + (x * y) % 3 for x in range(4)] for y in range(9)]

df = pd.DataFrame(data, indx, cols); df


# In[ ]: