---
# System prepended metadata

title: Virgil - EDA Visualization - S11 Advanced Pandas
tags: [' EDAVisualization', ' LearnWorld', Virgil]

---

---
title: Virgil - EDA Visualization - S11 Advanced Pandas
tags: Virgil, LearnWorld, EDAVisualization
---

<a target="_blank" href="https://colab.research.google.com/drive/1KPj_mgZf_32g0R6VNPaaBgh1WOFJC1V5"><img src="https://www.tensorflow.org/images/colab_logo_32px.png" />Run in Google Colab</a>

```
import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns
import numpy as np
```

# LONG FORM AND WIDE FORM

Data, while being presented in dataframe can be in two forms: Long and Wide

<img src='https://seaborn.pydata.org/_images/data_structure_19_0.png'>

In the **long form**:
- Each variable is a column 
- Each observation is a row


```
#@title Example of long-form dataframe 
flights = sns.load_dataset("flights")
flights.head()
```




<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }

    .dataframe tbody tr th {
        vertical-align: top;
    }

    .dataframe thead th {
        text-align: right;
    }
</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>year</th>
      <th>month</th>
      <th>passengers</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>0</th>
      <td>1949</td>
      <td>Jan</td>
      <td>112</td>
    </tr>
    <tr>
      <th>1</th>
      <td>1949</td>
      <td>Feb</td>
      <td>118</td>
    </tr>
    <tr>
      <th>2</th>
      <td>1949</td>
      <td>Mar</td>
      <td>132</td>
    </tr>
    <tr>
      <th>3</th>
      <td>1949</td>
      <td>Apr</td>
      <td>129</td>
    </tr>
    <tr>
      <th>4</th>
      <td>1949</td>
      <td>May</td>
      <td>121</td>
    </tr>
  </tbody>
</table>
</div>



On the other hand, a **wide form** dataframe is more like a spreadsheet where the columns and rows contain levels of different variables.


```
#@title Example of wide-form dataframe
flights_wide = flights.pivot(index="year", columns="month", values="passengers")
flights_wide.head()
```




<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }

    .dataframe tbody tr th {
        vertical-align: top;
    }

    .dataframe thead th {
        text-align: right;
    }
</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th>month</th>
      <th>Jan</th>
      <th>Feb</th>
      <th>Mar</th>
      <th>Apr</th>
      <th>May</th>
      <th>Jun</th>
      <th>Jul</th>
      <th>Aug</th>
      <th>Sep</th>
      <th>Oct</th>
      <th>Nov</th>
      <th>Dec</th>
    </tr>
    <tr>
      <th>year</th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>1949</th>
      <td>112</td>
      <td>118</td>
      <td>132</td>
      <td>129</td>
      <td>121</td>
      <td>135</td>
      <td>148</td>
      <td>148</td>
      <td>136</td>
      <td>119</td>
      <td>104</td>
      <td>118</td>
    </tr>
    <tr>
      <th>1950</th>
      <td>115</td>
      <td>126</td>
      <td>141</td>
      <td>135</td>
      <td>125</td>
      <td>149</td>
      <td>170</td>
      <td>170</td>
      <td>158</td>
      <td>133</td>
      <td>114</td>
      <td>140</td>
    </tr>
    <tr>
      <th>1951</th>
      <td>145</td>
      <td>150</td>
      <td>178</td>
      <td>163</td>
      <td>172</td>
      <td>178</td>
      <td>199</td>
      <td>199</td>
      <td>184</td>
      <td>162</td>
      <td>146</td>
      <td>166</td>
    </tr>
    <tr>
      <th>1952</th>
      <td>171</td>
      <td>180</td>
      <td>193</td>
      <td>181</td>
      <td>183</td>
      <td>218</td>
      <td>230</td>
      <td>242</td>
      <td>209</td>
      <td>191</td>
      <td>172</td>
      <td>194</td>
    </tr>
    <tr>
      <th>1953</th>
      <td>196</td>
      <td>196</td>
      <td>236</td>
      <td>235</td>
      <td>229</td>
      <td>243</td>
      <td>264</td>
      <td>272</td>
      <td>237</td>
      <td>211</td>
      <td>180</td>
      <td>201</td>
    </tr>
  </tbody>
</table>
</div>



With long-form data, we can access variables in the dataset by their name. That is not the case with wide-form data. 

##1/ Long to Wide ▸ Pivot Table

A pivot table is a table of statistics that summarizes the data of a more extensive table. This summary might include sums, averages, or other statistics, which the pivot table groups together in a meaningful way.


[Documentation](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.pivot_table.html)

[What is Pivot Table?](https://pbpython.com/pandas-pivot-table-explained.html)


```
flights = sns.load_dataset("flights")
flights.head()
```




<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }

    .dataframe tbody tr th {
        vertical-align: top;
    }

    .dataframe thead th {
        text-align: right;
    }
</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>year</th>
      <th>month</th>
      <th>passengers</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>0</th>
      <td>1949</td>
      <td>Jan</td>
      <td>112</td>
    </tr>
    <tr>
      <th>1</th>
      <td>1949</td>
      <td>Feb</td>
      <td>118</td>
    </tr>
    <tr>
      <th>2</th>
      <td>1949</td>
      <td>Mar</td>
      <td>132</td>
    </tr>
    <tr>
      <th>3</th>
      <td>1949</td>
      <td>Apr</td>
      <td>129</td>
    </tr>
    <tr>
      <th>4</th>
      <td>1949</td>
      <td>May</td>
      <td>121</td>
    </tr>
  </tbody>
</table>
</div>




```
# Wide table 
flights_wide = pd.pivot_table(data=flights, index="year", columns="month", values="passengers")
flights_wide.head()
```




<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }

    .dataframe tbody tr th {
        vertical-align: top;
    }

    .dataframe thead th {
        text-align: right;
    }
</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th>month</th>
      <th>Jan</th>
      <th>Feb</th>
      <th>Mar</th>
      <th>Apr</th>
      <th>May</th>
      <th>Jun</th>
      <th>Jul</th>
      <th>Aug</th>
      <th>Sep</th>
      <th>Oct</th>
      <th>Nov</th>
      <th>Dec</th>
    </tr>
    <tr>
      <th>year</th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>1949</th>
      <td>112</td>
      <td>118</td>
      <td>132</td>
      <td>129</td>
      <td>121</td>
      <td>135</td>
      <td>148</td>
      <td>148</td>
      <td>136</td>
      <td>119</td>
      <td>104</td>
      <td>118</td>
    </tr>
    <tr>
      <th>1950</th>
      <td>115</td>
      <td>126</td>
      <td>141</td>
      <td>135</td>
      <td>125</td>
      <td>149</td>
      <td>170</td>
      <td>170</td>
      <td>158</td>
      <td>133</td>
      <td>114</td>
      <td>140</td>
    </tr>
    <tr>
      <th>1951</th>
      <td>145</td>
      <td>150</td>
      <td>178</td>
      <td>163</td>
      <td>172</td>
      <td>178</td>
      <td>199</td>
      <td>199</td>
      <td>184</td>
      <td>162</td>
      <td>146</td>
      <td>166</td>
    </tr>
    <tr>
      <th>1952</th>
      <td>171</td>
      <td>180</td>
      <td>193</td>
      <td>181</td>
      <td>183</td>
      <td>218</td>
      <td>230</td>
      <td>242</td>
      <td>209</td>
      <td>191</td>
      <td>172</td>
      <td>194</td>
    </tr>
    <tr>
      <th>1953</th>
      <td>196</td>
      <td>196</td>
      <td>236</td>
      <td>235</td>
      <td>229</td>
      <td>243</td>
      <td>264</td>
      <td>272</td>
      <td>237</td>
      <td>211</td>
      <td>180</td>
      <td>201</td>
    </tr>
  </tbody>
</table>
</div>



## 2/ Long to Wide + One Hot Encode ▸ Get Dummies

[What is One Hot Encoding?]( https://towardsdatascience.com/what-is-one-hot-encoding-and-how-to-use-pandas-get-dummies-function-922eb9bd4970)

[`pd.get_dummies`](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.get_dummies.html)

[`pd.str.get_dummies`](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.Series.str.get_dummies.html)


```
work = pd.DataFrame({'name': ['Nhan', 'Quan', 'Ai'],
                     'day': ['Thu', 'Fri', 'Mon']})
work
```




<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }

    .dataframe tbody tr th {
        vertical-align: top;
    }

    .dataframe thead th {
        text-align: right;
    }
</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>name</th>
      <th>day</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>0</th>
      <td>Nhan</td>
      <td>Thu</td>
    </tr>
    <tr>
      <th>1</th>
      <td>Quan</td>
      <td>Fri</td>
    </tr>
    <tr>
      <th>2</th>
      <td>Ai</td>
      <td>Mon</td>
    </tr>
  </tbody>
</table>
</div>




```
# One Hot Encode
day = pd.get_dummies(work['day'])
day
```




<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }

    .dataframe tbody tr th {
        vertical-align: top;
    }

    .dataframe thead th {
        text-align: right;
    }
</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>Fri</th>
      <th>Mon</th>
      <th>Thu</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>0</th>
      <td>0</td>
      <td>0</td>
      <td>1</td>
    </tr>
    <tr>
      <th>1</th>
      <td>1</td>
      <td>0</td>
      <td>0</td>
    </tr>
    <tr>
      <th>2</th>
      <td>0</td>
      <td>1</td>
      <td>0</td>
    </tr>
  </tbody>
</table>
</div>




```
# Concat the result back to the dataframe
full = pd.concat([work, day], axis=1)
full
```




<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }

    .dataframe tbody tr th {
        vertical-align: top;
    }

    .dataframe thead th {
        text-align: right;
    }
</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>name</th>
      <th>day</th>
      <th>Fri</th>
      <th>Mon</th>
      <th>Thu</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>0</th>
      <td>Nhan</td>
      <td>Thu</td>
      <td>0</td>
      <td>0</td>
      <td>1</td>
    </tr>
    <tr>
      <th>1</th>
      <td>Quan</td>
      <td>Fri</td>
      <td>1</td>
      <td>0</td>
      <td>0</td>
    </tr>
    <tr>
      <th>2</th>
      <td>Ai</td>
      <td>Mon</td>
      <td>0</td>
      <td>1</td>
      <td>0</td>
    </tr>
  </tbody>
</table>
</div>



#### Get dummies with string


```
work = pd.DataFrame({'name': ['Nhan', 'Quan', 'Ai'],
                     'day': ['Thu,Fri,Sun', 'Fri,Mon', 'Mon,Tue,Sat']})
work
```




<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }

    .dataframe tbody tr th {
        vertical-align: top;
    }

    .dataframe thead th {
        text-align: right;
    }
</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>name</th>
      <th>day</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>0</th>
      <td>Nhan</td>
      <td>Thu,Fri,Sun</td>
    </tr>
    <tr>
      <th>1</th>
      <td>Quan</td>
      <td>Fri,Mon</td>
    </tr>
    <tr>
      <th>2</th>
      <td>Ai</td>
      <td>Mon,Tue,Sat</td>
    </tr>
  </tbody>
</table>
</div>




```
work['day'].str.get_dummies(sep=',')
```




<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }

    .dataframe tbody tr th {
        vertical-align: top;
    }

    .dataframe thead th {
        text-align: right;
    }
</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>Fri</th>
      <th>Mon</th>
      <th>Sat</th>
      <th>Sun</th>
      <th>Thu</th>
      <th>Tue</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>0</th>
      <td>1</td>
      <td>0</td>
      <td>0</td>
      <td>1</td>
      <td>1</td>
      <td>0</td>
    </tr>
    <tr>
      <th>1</th>
      <td>1</td>
      <td>1</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
    </tr>
    <tr>
      <th>2</th>
      <td>0</td>
      <td>1</td>
      <td>1</td>
      <td>0</td>
      <td>0</td>
      <td>1</td>
    </tr>
  </tbody>
</table>
</div>



## 3. Wide to Long ▸ Melt

While there are tables that can be clearly interpreted as long-form and wide form, some are messy with variables both in the format of long and wide. The syntax `pandas.melt` can be used to select which columns to stay as long, and which to convert from wide to long.

One or more columns are chosen to be the identifier. Other columns will be unpivoting to rows.

[`pandas.melt`](https://pandas.pydata.org/docs/reference/api/pandas.melt.html) 



```
song = pd.read_csv('https://raw.githubusercontent.com/hadley/tidy-data/master/data/billboard.csv', encoding="mac_latin2")
song.head()
```




<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }

    .dataframe tbody tr th {
        vertical-align: top;
    }

    .dataframe thead th {
        text-align: right;
    }
</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>year</th>
      <th>artist.inverted</th>
      <th>track</th>
      <th>time</th>
      <th>genre</th>
      <th>date.entered</th>
      <th>date.peaked</th>
      <th>x1st.week</th>
      <th>x2nd.week</th>
      <th>x3rd.week</th>
      <th>x4th.week</th>
      <th>x5th.week</th>
      <th>x6th.week</th>
      <th>x7th.week</th>
      <th>x8th.week</th>
      <th>x9th.week</th>
      <th>x10th.week</th>
      <th>x11th.week</th>
      <th>x12th.week</th>
      <th>x13th.week</th>
      <th>x14th.week</th>
      <th>x15th.week</th>
      <th>x16th.week</th>
      <th>x17th.week</th>
      <th>x18th.week</th>
      <th>x19th.week</th>
      <th>x20th.week</th>
      <th>x21st.week</th>
      <th>x22nd.week</th>
      <th>x23rd.week</th>
      <th>x24th.week</th>
      <th>x25th.week</th>
      <th>x26th.week</th>
      <th>x27th.week</th>
      <th>x28th.week</th>
      <th>x29th.week</th>
      <th>x30th.week</th>
      <th>x31st.week</th>
      <th>x32nd.week</th>
      <th>x33rd.week</th>
      <th>...</th>
      <th>x37th.week</th>
      <th>x38th.week</th>
      <th>x39th.week</th>
      <th>x40th.week</th>
      <th>x41st.week</th>
      <th>x42nd.week</th>
      <th>x43rd.week</th>
      <th>x44th.week</th>
      <th>x45th.week</th>
      <th>x46th.week</th>
      <th>x47th.week</th>
      <th>x48th.week</th>
      <th>x49th.week</th>
      <th>x50th.week</th>
      <th>x51st.week</th>
      <th>x52nd.week</th>
      <th>x53rd.week</th>
      <th>x54th.week</th>
      <th>x55th.week</th>
      <th>x56th.week</th>
      <th>x57th.week</th>
      <th>x58th.week</th>
      <th>x59th.week</th>
      <th>x60th.week</th>
      <th>x61st.week</th>
      <th>x62nd.week</th>
      <th>x63rd.week</th>
      <th>x64th.week</th>
      <th>x65th.week</th>
      <th>x66th.week</th>
      <th>x67th.week</th>
      <th>x68th.week</th>
      <th>x69th.week</th>
      <th>x70th.week</th>
      <th>x71st.week</th>
      <th>x72nd.week</th>
      <th>x73rd.week</th>
      <th>x74th.week</th>
      <th>x75th.week</th>
      <th>x76th.week</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>0</th>
      <td>2000</td>
      <td>Destiny's Child</td>
      <td>Independent Women Part I</td>
      <td>3:38</td>
      <td>Rock</td>
      <td>2000-09-23</td>
      <td>2000-11-18</td>
      <td>78</td>
      <td>63.0</td>
      <td>49.0</td>
      <td>33.0</td>
      <td>23.0</td>
      <td>15.0</td>
      <td>7.0</td>
      <td>5.0</td>
      <td>1.0</td>
      <td>1.0</td>
      <td>1.0</td>
      <td>1.0</td>
      <td>1.0</td>
      <td>1.0</td>
      <td>1.0</td>
      <td>1.0</td>
      <td>1.0</td>
      <td>1.0</td>
      <td>1.0</td>
      <td>2.0</td>
      <td>3.0</td>
      <td>7.0</td>
      <td>10.0</td>
      <td>12.0</td>
      <td>15.0</td>
      <td>22.0</td>
      <td>29.0</td>
      <td>31.0</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>...</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
    </tr>
    <tr>
      <th>1</th>
      <td>2000</td>
      <td>Santana</td>
      <td>Maria, Maria</td>
      <td>4:18</td>
      <td>Rock</td>
      <td>2000-02-12</td>
      <td>2000-04-08</td>
      <td>15</td>
      <td>8.0</td>
      <td>6.0</td>
      <td>5.0</td>
      <td>2.0</td>
      <td>3.0</td>
      <td>2.0</td>
      <td>2.0</td>
      <td>1.0</td>
      <td>1.0</td>
      <td>1.0</td>
      <td>1.0</td>
      <td>1.0</td>
      <td>1.0</td>
      <td>1.0</td>
      <td>1.0</td>
      <td>1.0</td>
      <td>1.0</td>
      <td>8.0</td>
      <td>15.0</td>
      <td>19.0</td>
      <td>21.0</td>
      <td>26.0</td>
      <td>36.0</td>
      <td>48.0</td>
      <td>47.0</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>...</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
    </tr>
    <tr>
      <th>2</th>
      <td>2000</td>
      <td>Savage Garden</td>
      <td>I Knew I Loved You</td>
      <td>4:07</td>
      <td>Rock</td>
      <td>1999-10-23</td>
      <td>2000-01-29</td>
      <td>71</td>
      <td>48.0</td>
      <td>43.0</td>
      <td>31.0</td>
      <td>20.0</td>
      <td>13.0</td>
      <td>7.0</td>
      <td>6.0</td>
      <td>4.0</td>
      <td>4.0</td>
      <td>4.0</td>
      <td>6.0</td>
      <td>4.0</td>
      <td>2.0</td>
      <td>1.0</td>
      <td>1.0</td>
      <td>1.0</td>
      <td>2.0</td>
      <td>1.0</td>
      <td>2.0</td>
      <td>4.0</td>
      <td>8.0</td>
      <td>8.0</td>
      <td>12.0</td>
      <td>14.0</td>
      <td>17.0</td>
      <td>21.0</td>
      <td>24.0</td>
      <td>30.0</td>
      <td>34.0</td>
      <td>37.0</td>
      <td>46.0</td>
      <td>47.0</td>
      <td>...</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
    </tr>
    <tr>
      <th>3</th>
      <td>2000</td>
      <td>Madonna</td>
      <td>Music</td>
      <td>3:45</td>
      <td>Rock</td>
      <td>2000-08-12</td>
      <td>2000-09-16</td>
      <td>41</td>
      <td>23.0</td>
      <td>18.0</td>
      <td>14.0</td>
      <td>2.0</td>
      <td>1.0</td>
      <td>1.0</td>
      <td>1.0</td>
      <td>1.0</td>
      <td>2.0</td>
      <td>2.0</td>
      <td>2.0</td>
      <td>2.0</td>
      <td>2.0</td>
      <td>4.0</td>
      <td>8.0</td>
      <td>11.0</td>
      <td>16.0</td>
      <td>20.0</td>
      <td>25.0</td>
      <td>27.0</td>
      <td>27.0</td>
      <td>29.0</td>
      <td>44.0</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>...</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
    </tr>
    <tr>
      <th>4</th>
      <td>2000</td>
      <td>Aguilera, Christina</td>
      <td>Come On Over Baby (All I Want Is You)</td>
      <td>3:38</td>
      <td>Rock</td>
      <td>2000-08-05</td>
      <td>2000-10-14</td>
      <td>57</td>
      <td>47.0</td>
      <td>45.0</td>
      <td>29.0</td>
      <td>23.0</td>
      <td>18.0</td>
      <td>11.0</td>
      <td>9.0</td>
      <td>9.0</td>
      <td>11.0</td>
      <td>1.0</td>
      <td>1.0</td>
      <td>1.0</td>
      <td>1.0</td>
      <td>4.0</td>
      <td>8.0</td>
      <td>12.0</td>
      <td>22.0</td>
      <td>23.0</td>
      <td>43.0</td>
      <td>44.0</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>...</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
    </tr>
  </tbody>
</table>
<p>5 rows × 83 columns</p>
</div>




```
# Indetifier: Columns that stay in long form
identifiers = song.columns[:7]

# Values: columns to convert to long form
values = song.columns[7:]

# Convert
song.melt(id_vars=identifiers,
          value_vars=values,
          var_name='week',
          value_name='rank')
```




<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }

    .dataframe tbody tr th {
        vertical-align: top;
    }

    .dataframe thead th {
        text-align: right;
    }
</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>year</th>
      <th>artist.inverted</th>
      <th>track</th>
      <th>time</th>
      <th>genre</th>
      <th>date.entered</th>
      <th>date.peaked</th>
      <th>week</th>
      <th>rank</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>0</th>
      <td>2000</td>
      <td>Destiny's Child</td>
      <td>Independent Women Part I</td>
      <td>3:38</td>
      <td>Rock</td>
      <td>2000-09-23</td>
      <td>2000-11-18</td>
      <td>x1st.week</td>
      <td>78.0</td>
    </tr>
    <tr>
      <th>1</th>
      <td>2000</td>
      <td>Santana</td>
      <td>Maria, Maria</td>
      <td>4:18</td>
      <td>Rock</td>
      <td>2000-02-12</td>
      <td>2000-04-08</td>
      <td>x1st.week</td>
      <td>15.0</td>
    </tr>
    <tr>
      <th>2</th>
      <td>2000</td>
      <td>Savage Garden</td>
      <td>I Knew I Loved You</td>
      <td>4:07</td>
      <td>Rock</td>
      <td>1999-10-23</td>
      <td>2000-01-29</td>
      <td>x1st.week</td>
      <td>71.0</td>
    </tr>
    <tr>
      <th>3</th>
      <td>2000</td>
      <td>Madonna</td>
      <td>Music</td>
      <td>3:45</td>
      <td>Rock</td>
      <td>2000-08-12</td>
      <td>2000-09-16</td>
      <td>x1st.week</td>
      <td>41.0</td>
    </tr>
    <tr>
      <th>4</th>
      <td>2000</td>
      <td>Aguilera, Christina</td>
      <td>Come On Over Baby (All I Want Is You)</td>
      <td>3:38</td>
      <td>Rock</td>
      <td>2000-08-05</td>
      <td>2000-10-14</td>
      <td>x1st.week</td>
      <td>57.0</td>
    </tr>
    <tr>
      <th>...</th>
      <td>...</td>
      <td>...</td>
      <td>...</td>
      <td>...</td>
      <td>...</td>
      <td>...</td>
      <td>...</td>
      <td>...</td>
      <td>...</td>
    </tr>
    <tr>
      <th>24087</th>
      <td>2000</td>
      <td>Ghostface Killah</td>
      <td>Cherchez LaGhost</td>
      <td>3:04</td>
      <td>R&amp;B</td>
      <td>2000-08-05</td>
      <td>2000-08-05</td>
      <td>x76th.week</td>
      <td>NaN</td>
    </tr>
    <tr>
      <th>24088</th>
      <td>2000</td>
      <td>Smith, Will</td>
      <td>Freakin' It</td>
      <td>3:58</td>
      <td>Rap</td>
      <td>2000-02-12</td>
      <td>2000-02-12</td>
      <td>x76th.week</td>
      <td>NaN</td>
    </tr>
    <tr>
      <th>24089</th>
      <td>2000</td>
      <td>Zombie Nation</td>
      <td>Kernkraft 400</td>
      <td>3:30</td>
      <td>Rock</td>
      <td>2000-09-02</td>
      <td>2000-09-02</td>
      <td>x76th.week</td>
      <td>NaN</td>
    </tr>
    <tr>
      <th>24090</th>
      <td>2000</td>
      <td>Eastsidaz, The</td>
      <td>Got Beef</td>
      <td>3:58</td>
      <td>Rap</td>
      <td>2000-07-01</td>
      <td>2000-07-01</td>
      <td>x76th.week</td>
      <td>NaN</td>
    </tr>
    <tr>
      <th>24091</th>
      <td>2000</td>
      <td>Fragma</td>
      <td>Toca's Miracle</td>
      <td>3:22</td>
      <td>R&amp;B</td>
      <td>2000-10-28</td>
      <td>2000-10-28</td>
      <td>x76th.week</td>
      <td>NaN</td>
    </tr>
  </tbody>
</table>
<p>24092 rows × 9 columns</p>
</div>



[A good example about tidying data.](https://www.jeannicholashould.com/tidy-data-in-python.html)

[A research on Tidying Data](http://vita.had.co.nz/papers/tidy-data.pdf)


```
# EXAMPLE: Employee
work = pd.DataFrame({'name': ['Nhan', 'Quan', 'Ai', 'Nhan'],
                     'day': ['Thu-Fri-Sun', 'Fri-Mon', 'Mon-Tue-Sat', 'Wed-Sun'],
                     'salary': [100, 200, 200, 2000]})
work
```




<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }

    .dataframe tbody tr th {
        vertical-align: top;
    }

    .dataframe thead th {
        text-align: right;
    }
</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>name</th>
      <th>day</th>
      <th>salary</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>0</th>
      <td>Nhan</td>
      <td>Thu-Fri-Sun</td>
      <td>100</td>
    </tr>
    <tr>
      <th>1</th>
      <td>Quan</td>
      <td>Fri-Mon</td>
      <td>200</td>
    </tr>
    <tr>
      <th>2</th>
      <td>Ai</td>
      <td>Mon-Tue-Sat</td>
      <td>200</td>
    </tr>
    <tr>
      <th>3</th>
      <td>Nhan</td>
      <td>Wed-Sun</td>
      <td>2000</td>
    </tr>
  </tbody>
</table>
</div>




```
work_dummies = work['day'].str.get_dummies(sep='-')
full = pd.concat([work, work_dummies], axis=1)
full
```




<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }

    .dataframe tbody tr th {
        vertical-align: top;
    }

    .dataframe thead th {
        text-align: right;
    }
</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>name</th>
      <th>day</th>
      <th>salary</th>
      <th>Fri</th>
      <th>Mon</th>
      <th>Sat</th>
      <th>Sun</th>
      <th>Thu</th>
      <th>Tue</th>
      <th>Wed</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>0</th>
      <td>Nhan</td>
      <td>Thu-Fri-Sun</td>
      <td>100</td>
      <td>1</td>
      <td>0</td>
      <td>0</td>
      <td>1</td>
      <td>1</td>
      <td>0</td>
      <td>0</td>
    </tr>
    <tr>
      <th>1</th>
      <td>Quan</td>
      <td>Fri-Mon</td>
      <td>200</td>
      <td>1</td>
      <td>1</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
    </tr>
    <tr>
      <th>2</th>
      <td>Ai</td>
      <td>Mon-Tue-Sat</td>
      <td>200</td>
      <td>0</td>
      <td>1</td>
      <td>1</td>
      <td>0</td>
      <td>0</td>
      <td>1</td>
      <td>0</td>
    </tr>
    <tr>
      <th>3</th>
      <td>Nhan</td>
      <td>Wed-Sun</td>
      <td>2000</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>1</td>
      <td>0</td>
      <td>0</td>
      <td>1</td>
    </tr>
  </tbody>
</table>
</div>




```
final = full.melt(id_vars=['name', 'salary'],
          value_vars=['Fri', 'Mon', 'Sat', 'Sun', 'Thu', 'Tue', 'Wed'])

final[final['value'] == 1]
```




<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }

    .dataframe tbody tr th {
        vertical-align: top;
    }

    .dataframe thead th {
        text-align: right;
    }
</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>name</th>
      <th>salary</th>
      <th>variable</th>
      <th>value</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>0</th>
      <td>Nhan</td>
      <td>100</td>
      <td>Fri</td>
      <td>1</td>
    </tr>
    <tr>
      <th>1</th>
      <td>Quan</td>
      <td>200</td>
      <td>Fri</td>
      <td>1</td>
    </tr>
    <tr>
      <th>5</th>
      <td>Quan</td>
      <td>200</td>
      <td>Mon</td>
      <td>1</td>
    </tr>
    <tr>
      <th>6</th>
      <td>Ai</td>
      <td>200</td>
      <td>Mon</td>
      <td>1</td>
    </tr>
    <tr>
      <th>10</th>
      <td>Ai</td>
      <td>200</td>
      <td>Sat</td>
      <td>1</td>
    </tr>
    <tr>
      <th>12</th>
      <td>Nhan</td>
      <td>100</td>
      <td>Sun</td>
      <td>1</td>
    </tr>
    <tr>
      <th>15</th>
      <td>Nhan</td>
      <td>2000</td>
      <td>Sun</td>
      <td>1</td>
    </tr>
    <tr>
      <th>16</th>
      <td>Nhan</td>
      <td>100</td>
      <td>Thu</td>
      <td>1</td>
    </tr>
    <tr>
      <th>22</th>
      <td>Ai</td>
      <td>200</td>
      <td>Tue</td>
      <td>1</td>
    </tr>
    <tr>
      <th>27</th>
      <td>Nhan</td>
      <td>2000</td>
      <td>Wed</td>
      <td>1</td>
    </tr>
  </tbody>
</table>
</div>



# *further reading* ▸ CUT

[`pd.cut`](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.cut.html)

[`pd.qcut`](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.qcut.html)


```
df = pd.read_csv('https://raw.githubusercontent.com/dhminh1024/practice_datasets/master/titanic.csv')
df.head()
```




<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }

    .dataframe tbody tr th {
        vertical-align: top;
    }

    .dataframe thead th {
        text-align: right;
    }
</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>PassengerId</th>
      <th>Survived</th>
      <th>Pclass</th>
      <th>Name</th>
      <th>Sex</th>
      <th>Age</th>
      <th>SibSp</th>
      <th>Parch</th>
      <th>Ticket</th>
      <th>Fare</th>
      <th>Cabin</th>
      <th>Embarked</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>0</th>
      <td>1</td>
      <td>0</td>
      <td>3</td>
      <td>Braund, Mr. Owen Harris</td>
      <td>male</td>
      <td>22.0</td>
      <td>1</td>
      <td>0</td>
      <td>A/5 21171</td>
      <td>7.2500</td>
      <td>NaN</td>
      <td>S</td>
    </tr>
    <tr>
      <th>1</th>
      <td>2</td>
      <td>1</td>
      <td>1</td>
      <td>Cumings, Mrs. John Bradley (Florence Briggs Th...</td>
      <td>female</td>
      <td>38.0</td>
      <td>1</td>
      <td>0</td>
      <td>PC 17599</td>
      <td>71.2833</td>
      <td>C85</td>
      <td>C</td>
    </tr>
    <tr>
      <th>2</th>
      <td>3</td>
      <td>1</td>
      <td>3</td>
      <td>Heikkinen, Miss. Laina</td>
      <td>female</td>
      <td>26.0</td>
      <td>0</td>
      <td>0</td>
      <td>STON/O2. 3101282</td>
      <td>7.9250</td>
      <td>NaN</td>
      <td>S</td>
    </tr>
    <tr>
      <th>3</th>
      <td>4</td>
      <td>1</td>
      <td>1</td>
      <td>Futrelle, Mrs. Jacques Heath (Lily May Peel)</td>
      <td>female</td>
      <td>35.0</td>
      <td>1</td>
      <td>0</td>
      <td>113803</td>
      <td>53.1000</td>
      <td>C123</td>
      <td>S</td>
    </tr>
    <tr>
      <th>4</th>
      <td>5</td>
      <td>0</td>
      <td>3</td>
      <td>Allen, Mr. William Henry</td>
      <td>male</td>
      <td>35.0</td>
      <td>0</td>
      <td>0</td>
      <td>373450</td>
      <td>8.0500</td>
      <td>NaN</td>
      <td>S</td>
    </tr>
  </tbody>
</table>
</div>



**Example 1:** Divide the age in titanic to 
* Infants: < 1
* Children: < 10
* Teens: < 18
* Adults: < 40
* Middle Age: < 60
* Elders: >= 60


```
pd.cut(x = df['Age'], 
       bins = [0, 1, 10, 18, 40, 60, df['Age'].max()], 
       labels = ['Infants', 'Children', 'Teens', 'Adults', 'MAge', 'Elders'])
```




    0      Adults
    1      Adults
    2      Adults
    3      Adults
    4      Adults
            ...  
    886    Adults
    887    Adults
    888       NaN
    889    Adults
    890    Adults
    Name: Age, Length: 891, dtype: category
    Categories (6, object): ['Infants' < 'Children' < 'Teens' < 'Adults' < 'MAge' < 'Elders']



**Example 2**: Divide the column Fare into 4 quantiles and named them respectively 1, 2, 3, 4


```
# quantile cut
```


```
pd.qcut(x=df['Fare'],
        q=4,
        labels=[1, 2, 3, 4])
```




    0      1
    1      4
    2      2
    3      4
    4      2
          ..
    886    2
    887    3
    888    3
    889    3
    890    1
    Name: Fare, Length: 891, dtype: category
    Categories (4, int64): [1 < 2 < 3 < 4]




```

```
