---
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]