<jupyter_start><jupyter_text>Tidy data in Pandas  Handy hints  * In general, we are using plotting libraries that return objects encapsulating the plot. You can check the type of these returned objects with `type()`. Jupyter's tools for exploring objects and methods will also be useful: the `?` and `??` operators, and tab autocompletion.  Setup<jupyter_code>import pandas as pd
import numpy as np
# This causes Jupyter to display any matplotlib plots directly in the notebook
# It also works for seaborn, since seaborn uses matplotlib to render plots
# There is an alterative `%matplotlib notebook` magic for interactivity, which we won't use today
# - we will use other libraries for interactive plots
%matplotlib inline
import matplotlib.pyplot as plt
import seaborn as sns<jupyter_output><empty_output><jupyter_text>Pandas warm-up<jupyter_code>iris = sns.load_dataset('iris')
iris.columns
iris.dtypes
iris.shape
iris.head()
iris['sepal_shape'] = iris['sepal_length'] / iris['sepal_width']
iris.head()
fig, ax = plt.subplots()
ax.scatter(x=iris['sepal_width'],y=iris['sepal_length'])<jupyter_output><empty_output><jupyter_text>Tidy data  We can manipulate data using the column names and index, with `stack` and `unstack`.We can manipulate data using particular columns, with `melt` and `pivot` or `pivot_table`.<jupyter_code>iris.index = ['Flower_{}'.format(n+1) for n in range(len(iris))]
iris.index.name = 'Sample'
iris.head()
iris.melt()
iris.reset_index(inplace=True)
iris.head()
iris.melt(id_vars=['Sample']).head()
iris.melt(id_vars=['Sample']).pivot(index='Sample',columns='variable',values='value')<jupyter_output><empty_output><jupyter_text>Tidy data exercises [Link to "untidy data" discussion spreadsheet](https://docs.google.com/spreadsheets/d/1tnLVYxd6k5oGWVHsiF6D78ds1nOztU3Ynri_Bx0oOjE/edit?usp=sharing) Here are two tiny "wide" datasets:<jupyter_code>sales_wide1 = pd.read_csv('housing-data-wide1.csv')
sales_wide1
sales_wide2 = pd.read_csv('housing-data-wide2.csv', parse_dates=['date1','date2'])
sales_wide2<jupyter_output><empty_output><jupyter_text>And two slightly larger datasets of the same form:<jupyter_code>sales_wide1_100 = pd.read_csv('housing-data-wide1-100.csv')
print(sales_wide1_100.shape)
print(sales_wide1_100.columns)
sales_wide2_100 = pd.read_csv('housing-data-wide2-100.csv', parse_dates=['date1','date2'])
print(sales_wide2_100.shape)
print(sales_wide2_100.columns)<jupyter_output>(100, 6)
Index(['property_id', 'bedrooms', 'date1', 'price1', 'date2', 'price2'], dtype='object')<jupyter_text>**Exercises** * Use Pandas to get `sales_wide1` into tidy form. Once you've reshaped the data, you may also want to remove null rows using either `.dropna()` or `.loc[]` and `.isnull()`.* Apply the same tidying to `sales_wide1_100`. Once this data is tidy, plot a histogram using `plt.hist()` (or better, `ax.hist()` !) and passing it the column of house prices.<jupyter_code>tidied = sales_wide1.melt(id_vars=['property_id','bedrooms'], value_name='price')
tidied = tidied.loc[~tidied['price'].isnull()]
tidied = sales_wide1_100.melt(id_vars=['property_id','bedrooms'], value_name='price')
tidied = tidied.loc[~tidied['price'].isnull()]
fig,ax = plt.subplots()
ax.hist(tidied['price'])
# Try the log
fig,ax = plt.subplots()
ax.hist(np.log(tidied['price']))<jupyter_output><empty_output><jupyter_text>* Use Pandas to get `sales_wide2` into tidy form. Note that this is much harder than the first exercise!* If you've tidied this data, apply the same tidying to `sales_wide2_100` and use matplotlib's `plt.plot_date()` or `ax.plot_date()` to create a scatter plot of sale price against sale date.<jupyter_code># There's more than one reasonable solution to this problem

# One approach: Melt down the variables we don't want to keep. 
# Then split the variable column to get useful groupings
melted = sales_wide2.melt(id_vars=['property_id','bedrooms'])
melted['salenum'] = melted['variable'].str[-1]
melted['variable'] = melted['variable'].str[:-1]
melted.head(10)
# Now pivot back to get date and price as columns again
melted.pivot_table(
    index=['property_id','bedrooms','salenum'],
    columns='variable',
    values='value',
    aggfunc='first').reset_index()
# Apply to larger dataset
melted = sales_wide2_100.melt(id_vars=['property_id','bedrooms'])
melted['salenum'] = melted['variable'].str[-1]
melted['variable'] = melted['variable'].str[:-1]
tidied = melted.pivot_table(
    index=['property_id','bedrooms','salenum'],
    columns='variable',
    values='value',
    aggfunc='first').reset_index()
fig,ax = plt.subplots()
ax.plot_date(tidied['date'],tidied['price'])<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.14058127018299246, 'fraction_numerical': 0.018514531754574813, 'mean_word_length': 7.051993067590987, 'pattern_counts': {'":': 0, '<': 28, '<?xml version=': 0, '>': 28, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '7', 'dir': 'jupyter-structured-clean-dedup', 'id': '588706', 'license': 'MIT', 'n_tokens_mistral': 1692, 'n_tokens_neox': 1608, 'n_words': 399, 'path': 'Tidy_data_solutions.ipynb', 'repo_name': 'aspp-apac/2019-data-vizualisation', 'size': '4645'}