# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python 3
#     language: python
#     name: python3
# ---

import pandas as pd
import numpy as np
import seaborn as sns
from pydataset import data
import matplotlib as plt

# Paper by: <NAME>
#
# https://vita.had.co.nz/papers/tidy-data.pdf
#
# ### Data cleaning:
#
# tidy data != clean data
#
#
# - outlier checking  
# - date parsing
# - missing value imputation etc.
# - <ins>data tidying: structuring datasets to facilitate analysis.</ins>
#
#

# ### Data semantics: tidy data

# - Value: every value belongs to a variable and an observation.
# - Variable: a variable contains all values that measure the same underlying attribute (like height, temperature, duration) across units.
# - Observation: an observation contains all values measured on the same unit (like a person, or a day, or a race) across attributes.

# #### CSV data for lesson and exercises:
#
# - https://classroom.google.com/u/0/w/MjI5NDAwNDI2NTg5/tc/MjI5NDAwNDI2NjAy

# lets look at this data:
treatments = pd.read_csv('untidy-data/treatment.csv')
treatments

#rename columns
treatments.columns = ['name', 'a', 'b', 'c']
treatments

# restructure data using 'melt'
treatments = treatments.melt(id_vars=['name'], var_name='treatment', value_name='response')

treatments

# ### Tidy data : 
# - Each variable forms a column.
# - Each observation forms a row.
# - Each cell has a single value.
# - data is tabular, i.e. made up of rows and columns

# Examples of tidy-data
tips = data('tips')
tips

# #### General Ideas  
# - If the units are the same, maybe they should be in the same column.
# - If one column has measurements of different units, it should be spread out  
# - Should you be able to groupby some of the columns? combine them  
# - Can I pass this data to seaborn?  
#
# - Can we ask interesting questions and answer them with a groupby? i.e. generally we don't want to be taking row or column averages.
#

# ### How to deal with 'messy' data

#
# ##### Reshaping data:  
#   
# - Wide data --> Long data format (Melt)  
# - Long data --> Wide Data format (pivot)  

# #### 1. Messy data: Column headers are values, not variable names.

df = pd.read_csv('untidy-data/pew.csv')

# look at info
df.info()

# look at the head
df.head()

# melt the data. 
pd.melt(df, id_vars = 'religion', var_name = 'income', value_name = 'count')

# #### pd.melt arguments
# - id_vars = columns you want to keep (not melt)
# - var_name = name of new column you created by melting columns
# - value_name = column name for values

# #### Another example: one variable stored across multiple columns

billboard = pd.read_csv('untidy-data/billboard.csv', encoding= 'unicode_escape')

billboard.shape

billboard.head()

billboard_long = pd.melt(billboard, id_vars = ['year', 'artist', 'track', 'time', 'genre', 'date.peaked', 'date.entered'], 
                         var_name = 'week', value_name = 'rating')

billboard_long

billboard_long.groupby('track').rating.mean()

# #### 2. Messy data: Multiple variables are stored in one column.

df = pd.DataFrame({
    'name': ['Sally', 'Jane', 'Billy', 'Suzy'],
    'pet': ['dog: max', 'dog: buddy', 'cat: grizabella', 'hamster: fred']
})
df

df[['pet_species', 'pet_name']] = df.pet.str.split(':', expand = True)
df

df = df.drop(columns = 'pet', implace = True)

df

# #### Messy data: Variables are stored in both rows and columns

weather = pd.read_csv('untidy-data/weather.csv')
weather.head()

weather_melt = weather.melt(id_vars = ['id', 'year','month','element'],
                           var_name = 'day', value_name = 'temp')

weather_melt

weather_tidy = weather_melt.pivot_table(index = ['id','year','month','day'],
                                       columns = 'element', values = 'temp')

weather_tidy

df = weather_tidy.reset_index()
df

# #### pd.pivot_table arguments
# - Index = columns you want to keep (not pivot)
# - columns = column you want to pivot
# - value_name = values we want to populate in the new columns
# - aggfunct = how you want tp aggregate the duplicate rows

# #### Bit more complex example

sales = pd.read_csv('untidy-data/sales.csv')

sales

sales_melt = sales.melt(id_vars = 'Product')
sales_melt

sales_melt[['year', 'measure']] =sales_melt.variable.str.split(' ', expand = True)
sales_melt = sales_melt.drop(columns = 'variable')

sales_melt.head()

sales_tidy = sales_melt.pivot_table(index = ['Product', 'year'], columns = 'measure', values = 'value')

sales_tidy = sales_tidy.reset_index

sales_tidy()

# +
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

# +
# Exercises 

# +
# Do your work for this exercise in a jupyter notebook or python script named tidy_data. 
# Save this work in your classification-exercises repo. Then add, commit, and push your changes.

# +
#1) Load the attendance.csv file and calculate an attendnace percentage for each student. 
# One half day is worth 50% of a full day, and 10 tardies is equal to one absence.
# You should end up with something like this:

#name
#Billy    0.5250
#Jane     0.6875
#John     0.9125
#Sally    0.7625
#Name: grade, dtype: float64

# -

attendance = pd.read_csv('untidy-data/attendance.csv')
attendance

attendance_melt = attendance.melt(id_vars = 'Unnamed: 0', var_name = 'date', value_name = 'attendence')
attendance_melt

attendance_melt['attendence'] = attendance_melt['attendence'].str.replace('P','0')
attendance_melt['attendence'] = attendance_melt['attendence'].str.replace('H','0.50')
attendance_melt['attendence'] = attendance_melt['attendence'].str.replace('T','0.10')
attendance_melt['attendence'] = attendance_melt['attendence'].str.replace('A','1')
attendance_melt

attendance_melt["attendence"] = pd.to_numeric(attendance_melt["attendence"])

attendance_melt = attendance_melt.rename(columns ={'Unnamed: 0': 'name'})

att = attendance_melt.groupby('name').attendence.mean() - 1
att.abs()

#2) Read the coffee_levels.csv file.
# Transform the data so that each carafe is in it's own column.
# Is this the best shape for the data?
coffee = pd.read_csv('untidy-data/coffee_levels.csv')
coffee

coffee_melt = coffee.melt(id_vars = ['hour', 'coffee_amount'])
coffee_melt

coffee_tidy = coffee_melt.pivot_table(index = 'hour', columns = 'value', values = 'coffee_amount')
coffee_tidy

coffee_tidy = coffee_tidy.reset_index

coffee_tidy()

# +
#3) Read the cake_recipes.csv data. This data set contains cake tastiness scores for combinations of different recipes, oven rack positions, and oven temperatures.
# Tidy the data as necessary.
# Which recipe, on average, is the best? recipe b
# Which oven temperature, on average, produces the best results? 275
# Which combination of recipe, rack position, and temperature gives the best result? recipe b, bottom rack, 300 degrees
# -

cake = pd.read_csv('untidy-data/cake_recipes.csv')
cake

cake = cake.rename(columns ={'recipe:position': 'rp'})

cake[['recipe', 'position']] = cake.rp.str.split(':', expand = True)
cake

cake = cake.drop(columns = ['rp'])

cake = cake.drop(columns = ['bottom','top'])

cake

cake_melt = cake.melt(id_vars = ['recipe','position'])
cake_melt

cake_melt = cake_melt.rename(columns ={'variable': 'oven_temp'})
cake_melt = cake_melt.rename(columns ={'value': 'score'})

cake_melt

cake_melt.groupby('recipe').score.mean()

cake_melt.groupby('oven_temp').score.mean()

cake_melt.groupby('score').max()



StarcoderdataJupyterScriptsDedupFiltered