<jupyter_start><jupyter_text>07 - Pandas, Multiple Tables, and Tidy Data<jupyter_code># Includes and Standard Magic...
### Standard Magic and startup initializers.

# Load Numpy
import numpy as np
# Load MatPlotLib
import matplotlib
import matplotlib.pyplot as plt
# Load Pandas
import pandas as pd

# This lets us show plots inline and also save PDF plots if we want them
%matplotlib inline
from matplotlib.backends.backend_pdf import PdfPages
matplotlib.style.use('fivethirtyeight')

# These two things are for Pandas, it widens the notebook and lets us display data easily.
from IPython.core.display import display, HTML
display(HTML("<style>.container { width:95% !important; }</style>"))

# Show a ludicrus number of rows and columns
pd.set_option('display.max_rows', 500)
pd.set_option('display.max_columns', 500)
pd.set_option('display.width', 1000)<jupyter_output><empty_output><jupyter_text>First, let's take a look at some basic Pandas functionality<jupyter_code>s1 = pd.DataFrame([0.3, 0.8, 0.1, 4.0, 1.2], 
                  index = ['a', 'c', 'd', 'e', 'f'], 
                  columns=['data'])

display(s1)
s2 = pd.DataFrame([0.9, 0.1, 4.8, 0.3], 
                  index = ['b', 'c', 'd', 'g'], 
                  columns=['data'])
display(s2)<jupyter_output><empty_output><jupyter_text>Re-index can get used to do some funky things or make bigger frames.. [Doc Page](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.reindex.html)```Places NA/NaN in locations having no value in the previous index. A new object is produced unless the new index is equivalent to the current one and copy=False.```<jupyter_code># We can use re-index to fill in some gaps if we want..
import string

display(s1.reindex(list(string.ascii_lowercase)[:10]))
display(s1.reindex(list(string.ascii_lowercase)[:10], method='ffill'))<jupyter_output><empty_output><jupyter_text>Stop Here For Quiz!<jupyter_code>s1
s2[s2['data'] > 3]
s1[ (s1['data'] < 1.0) & (s1['data'] > 3.0) ]
s1
s1 + 0.5
s2
s1+s2<jupyter_output><empty_output><jupyter_text>Note that + is like what we'll learn is an inner join soon!  Let's look at how we can use indicies to slice frames<jupyter_code>s1.loc['f']
s1.loc['a':'d']
s2.loc['b']
display(s1.iloc[0:3])
display(s1.iloc[1:5])
s2['data'].sum()<jupyter_output><empty_output><jupyter_text>You can also apply functions across and down and with lambdas...<jupyter_code>frame = pd.DataFrame(np.random.randn(4,3), columns=list('abc'),
                 index=['Utah', 'Ohio', 'Texas','Oregon'])
display(frame)
frame.abs()
minmax = lambda x: x.max() - x.min()

frame.apply(minmax)
# Default is axis=0 or per column, can also do per row!
frame.apply(minmax, axis=1)<jupyter_output><empty_output><jupyter_text>Ranking ExamplesSome of the [Pandas Rank](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.rank.html) commands aren't totally obvious...<jupyter_code>frame = pd.DataFrame({'a':[0, 1, 0, 1],
                      'b':[4.3, 7, -3, 2],
                   'c':[-2, 5, 8, -2.5]})
display(frame)
# Default is average but you can do other things!

display(frame.rank(ascending=False))

display(frame.rank(ascending=False, method='first'))
# Can also sort along rows!

frame.rank(axis=1)<jupyter_output><empty_output><jupyter_text>Back to the slides to discuss basic GroupBy operations first!  Importance of using np.nanNote that we have to import numpy.nan but once we do we can use the groupby and other methods without having to worry about what to do with missing data.For the example below, what happens when we don't use NaNs in the data table?<jupyter_code># Make a data frame from a lists
# Try replacing 'XX' with various values..

df = pd.DataFrame({'age':     [12.2, 11.0, 15.6, '--'],
                  'wgt_kg':   [42.3, 40.8, 65.3, 84.2],
                  'hgt_cm':   [145.1, 143.8, 165.3, 185.8],
                  'sex':      ['male', 'female', 'male', 'male'],
                  'country': ['USA', 'AUS', 'EU', 'USA']})
df
df['age'].describe()
# Note the type that has been imputed here...
df.dtypes
df['age'].sum()
# Need to make sure it's set as number!

df["age"] = pd.to_numeric(df["age"])
# But first we have to make sure it's a NAN!!
display(df["age"].replace("--", np.nan))

df["age"].replace("--", np.nan,inplace=True)
df.dtypes
df["age"].sum()<jupyter_output><empty_output><jupyter_text>The Groupby CommandBelow we see what happens in Pandas when we use the groupby command.<jupyter_code>df = pd.DataFrame({'age':     [12.2, 11.0, 15.6, 35.1],
                  'wgt_kg':   [42.3, 40.8, 65.3, 84.2],
                  'hgt_cm':   [145.1, 143.8, 165.3, 185.8],
                  'sex':      ['male', 'female', 'male', 'male'],
                  'country': ['USA', 'AUS', 'EU', 'USA']})
df
df.groupby(['sex']).describe()

# ?? Try describing it..?
# We can pass operators to groupby to get better results.
df.groupby(['sex']).mean()
# Can also group by multiple columns
df.groupby(['sex', 'country']).describe()

## We can also limit ourselves by just subselecting columns
display(df.groupby(["country"]).count())
display(df.groupby(["country"])[["sex"]].count())
grouped = df.groupby(['country'])
type(grouped)<jupyter_output><empty_output><jupyter_text>To get access to a group that we made, use the [get_group() command](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.core.groupby.GroupBy.get_group.html).We can also get access to the actual tuples and they're types that are generated by the `groupby` commands<jupyter_code>grouped.groups
grouped.get_group('AUS')
# Or do it fancy?
[display(grouped.get_group(i)) for i in grouped.groups]<jupyter_output><empty_output><jupyter_text>Joins in PandasLet's see a few [joins in Pandas](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.join.html) and how to execute them.**Note:** We can also use the [merge command in pandas](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.merge.html) to joins as well.  We'll explore this more fully in the lab.  The key idea is that merge let's us pick columns to do the merge/join while join will always join on the index.<jupyter_code># Careful here! We have to set the index!

df1 = pd.DataFrame([(1, 'foo', 3), (2, 'bar', 2), (3, 'foo', 4), (4, 'foo', 3)],
                  columns=['ID','A', 'B'])
df1.set_index('ID', inplace=True)
display(df1)

df2 = pd.DataFrame([(1, 1.2), (2, 2.5), (3, 2.3), (5, 8.0)], 
                   columns=['ID','C'])
df2.set_index('ID', inplace=True)
display(df2)
# Inner join
df1.join(df2, how='inner')
# Can do inner, outter, left, right.
df1.join(df2, how='right')<jupyter_output><empty_output><jupyter_text>If we don't have the same column names we can tell Pandas that we want to join on a specific column.<jupyter_code>df1 = pd.DataFrame([(1, 'foo', 3), (2, 'bar', 2), (3, 'foo', 4), (4, 'foo', 3)],
                  columns=['leftid','A', 'B'])
display(df1)

df2 = pd.DataFrame([(1, 1.2), (2, 2.5), (3, 2.3), (5, 8.0)], 
                   columns=['rightid','C'])
df2.set_index('rightid', inplace=True)
display(df2)
# Inner join
df1.join(df2, how='inner')
# Is this what we wanted?!?!
# We could have set the index but we can also tell Pandas to do it for us.
df1.join(df2, how='inner', on='leftid')<jupyter_output><empty_output><jupyter_text>Pandas MergeOr we can skip all this foolishness by using the [pandas merge](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.merge.html) command...<jupyter_code># Or we can skip all this foolishness by using the merge command...
df1 = pd.DataFrame([(1, 'foo', 3), (2, 'bar', 2), (3, 'foo', 4), (4, 'foo', 3)],
                  columns=['ID','A', 'B'])
display(df1)

df2 = pd.DataFrame([(1, 1.2), (2, 2.5), (3, 2.3), (5, 8.0)], 
                   columns=['ID','C'])
display(df2)
x = df1.merge(df2, left_on="ID", right_on="ID", how='inner')
x<jupyter_output><empty_output><jupyter_text>More Complicated IndiciesTo set a **hierarchical index** one can refer to the [documetnation page](https://pandas.pydata.org/pandas-docs/stable/user_guide/advanced.html).<jupyter_code># Setting a heiararichal index -- The bad way would be to build up tuples as an index.

index = [('California', 2000), ('California', 2010),
         ('New York', 2000), ('New York', 2010),
         ('Texas', 2000), ('Texas', 2010)]
populations = [33871648, 37253956,
               18976457, 19378102,
               20851820, 25145561]
pop = pd.Series(populations, index=index)
display(pop)
# Now we can still get slices etc. in a strightforward way.
pop[('California', 2010):('Texas', 2000)]
# But if we want to get all 2010 data we have to do something like...
pop[[i for i in pop.index if i[1] == 2010]]
#We can make the data frame as (note it is tidy!)
df = pd.DataFrame([('California', 2000, 33871648),
                   ('California', 2010, 37253956),
                   ('New York', 2000, 18976457),
                   ('New York', 2010, 19378102),
                   ('Texas', 2000, 20851820),
                   ('Texas', 2010, 25145561)],
                  columns=['state', 'year', 'pop'])
df
df.set_index(['state', 'year'], inplace=True)
df
# And now we can do cool stuff slicing, but it gets compicated with tuples.
df.loc[('California')]
df.index<jupyter_output><empty_output><jupyter_text>The [df.xs](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.xs.html) command can help with slicing multi-indicies.<jupyter_code># Thought this can get a bit complicated... 
df.xs(2010, level=1)<jupyter_output><empty_output><jupyter_text>Melting and Tidy Data<jupyter_code>df = pd.read_csv('./data/religon.csv')
df<jupyter_output><empty_output><jupyter_text>The [melt command](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.melt.html) does...```DataFrame.melt(id_vars=None, value_vars=None, var_name=None, value_name='value', col_level=None, ignore_index=True)Unpivot a DataFrame from wide to long format, optionally leaving identifiers set.This function is useful to massage a DataFrame into a format where one or more columns are identifier variables (id_vars), while all other columns, considered measured variables (value_vars), are “unpivoted” to the row axis, leaving just two non-identifier columns, ‘variable’ and ‘value’.```So here we want to take the above data frame and make it `grow down` by converting income into a categorical variable.<jupyter_code>f_df = pd.melt(df,
               ["religion"],
               var_name="income",
               value_name="freq")
f_df = f_df.sort_values(by=["income"])
f_df.head(10)
# We can now use more simple slicing to see the things we need.

f_df[(f_df['religion'] == 'Atheist')].sort_values(by='income')
f_df[(f_df['income'] == '$50-75k')].sort_values(by='freq', ascending=False)<jupyter_output><empty_output><jupyter_text>A More Complicated Example of Melting...<jupyter_code>b_df = pd.read_csv("./data/billboard.csv")
b_df.head()<jupyter_output><empty_output><jupyter_text>This thing is a mess!! What if want to say, graph the positions of a song for a given artist? Then how would we do it?Again, we are going to melt to make the table `grow down` by keeping the `id_vars` and then melting the rows based on week position to be values.<jupyter_code># Keep identifier variables
id_vars = ["year",
           "artist.inverted",
           "track",
           "time",
           "genre",
           "date.entered",
           "date.peaked"]

# Melt the rest into week and rank columns
b_df = pd.melt(frame=b_df,
             id_vars=id_vars,
             var_name="week",
             value_name="rank")

display(b_df.head(20))
b_df.dtypes
# Let's fix the week thing, it's not good...
b_df["week"] = b_df['week'].str.extract('(\d+)', expand=False).astype(int)
b_df[:5]
b_df["week"].unique()<jupyter_output><empty_output><jupyter_text>There's a subtle problem here, rank is an int but some are NAN's and we can't have [int-nan's...](https://pandas.pydata.org/pandas-docs/version/0.24/whatsnew/v0.24.0.htmloptional-integer-na-support)<jupyter_code># Why not ints? Gotcha! ?!?!?
b_df["rank"] = b_df["rank"].astype(float)

display(b_df.head(20))
# We can see if anything is missing...
# Remember axis=1 goes row wise..so we are saying show us any row with missing values...
b_df[b_df.isnull().any(axis=1)]
# We don't need these so... let's drop them.
b_df = b_df.dropna()<jupyter_output><empty_output><jupyter_text>We're going to use the handy [to_datetime](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.to_datetime.html) which will get us a date!<jupyter_code># Create "date" columns
#  date = (date entered chart) + (# of weeks) - (1 week) [fence post problem]
b_df['date'] = pd.to_datetime(b_df['date.entered']) + pd.to_timedelta(b_df['week'], unit='w') - pd.DateOffset(weeks=1) 
display(b_df[100:110])
b_df.dtypes
# Ignore now-redundant, messy columns -- same as dropping 
b_df = b_df[["year",
         "artist.inverted",
         "track",
         "time",
         "genre",
         "week",
         "rank",
         "date"]]

b_df = b_df.sort_values(ascending=True, by=["year","artist.inverted","track","week","rank"])

# Keep tidy dataset for future usage
billboard = b_df

billboard.head(10)
# Now we can see the artists we have...
billboard['artist.inverted'].unique()
# Easier to visualize!!
b_df[(b_df['artist.inverted'] == 'Jay-Z')]
b_df[(b_df['artist.inverted'] == 'Jay-Z')]['rank'].plot.hist(bins=100, title="Jay-Z Hits by Week")
#Scatterplot of JayZ at different songs/days?
b_df[(b_df['artist.inverted'] == 'Jay-Z')].plot.scatter(y='rank', 
                                                        x='date',
                                                        rot=45)
import seaborn as sns
g = sns.pairplot(x_vars=["date"], 
             y_vars=["rank"], 
             data=b_df[(b_df['artist.inverted'] == 'TLC')], 
             hue="track",
             height=5)

# # Hacky roration?? -- https://github.com/mwaskom/seaborn/issues/867
for ax in g.axes.flat:
    for label in ax.get_xticklabels():
        label.set_rotation(45)

# # Invert..
for ax in g.axes.flat:
    ax.invert_yaxis()
# Or we can get the list of top ranked songs...
b_df[(b_df['rank'] == 1.0)].groupby(['artist.inverted']).count()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1587512222377427, 'fraction_numerical': 0.032127392093867856, 'mean_word_length': 3.8375, 'pattern_counts': {'":': 0, '<': 88, '<?xml version=': 0, '>': 89, 'https://': 12, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 4, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '21', 'dir': 'jupyter-structured-clean-dedup', 'id': '240900', 'license': 'MIT', 'n_tokens_mistral': 5389, 'n_tokens_neox': 4976, 'n_words': 1360, 'path': '_notebooks/07-MultipleTablesandTidyData.ipynb', 'repo_name': 'nmattei/cmps3160', 'size': '14318'}