<jupyter_start><jupyter_text>Pandas[Pandas](http://pandas.pydata.org/) is a an open source library providing high-performance, easy-to-use data structures and data analysis tools. Pandas is particularly suited to the analysis of _tabular_ data, i.e. data that can can go into a table. In other words, if you can imagine the data in an Excel spreadsheet, then Pandas is the tool for the job.A [recent analysis](https://stackoverflow.blog/2017/09/06/incredible-growth-python/) of questions from Stack Overflow showed that python is the fastest growing and most widely used programming language in the world (in developed countries).![python growth](https://zgab33vy595fw5zq-zippykid.netdna-ssl.com/wp-content/uploads/2017/09/growth_major_languages-1-1024x878.png)A [follow-up analysis](https://stackoverflow.blog/2017/09/14/python-growing-quickly/) showed that this growth is driven by the data science packages such as numpy, matplotlib, and especially pandas.![pandas growth](https://zgab33vy595fw5zq-zippykid.netdna-ssl.com/wp-content/uploads/2017/09/related_tags_over_time-1-1024x1024.png)The exponential growth of pandas is due to the fact that it _just works_. It saves you time and helps you do science more efficiently and effictively. Pandas capabilities (from the Pandas website):* A fast and efficient DataFrame object for data manipulation with integrated indexing;* Tools for reading and writing data between in-memory data structures and different formats: CSV and text files, Microsoft Excel, SQL databases, and the fast HDF5 format;* Intelligent data alignment and integrated handling of missing data: gain automatic label-based alignment in computations and easily manipulate messy data into an orderly form;* Flexible reshaping and pivoting of data sets;* Intelligent label-based slicing, fancy indexing, and subsetting of large data sets;* Columns can be inserted and deleted from data structures for size mutability;* Aggregating or transforming data with a powerful group by engine allowing split-apply-combine operations on data sets;* High performance merging and joining of data sets;* Hierarchical axis indexing provides an intuitive way of working with high-dimensional data in a lower-dimensional data structure;* Time series-functionality: date range generation and frequency conversion, moving window statistics, moving window linear regressions, date shifting and lagging. Even create domain-specific time offsets and join time series without losing data;* Highly optimized for performance, with critical code paths written in Cython or C.* Python with pandas is in use in a wide variety of academic and commercial domains, including Finance, Neuroscience, Economics, Statistics, Advertising, Web Analytics, and more.In this lecture, we will go over the basic capabilities of Pandas. It is a very deep library, and you will need to dig into the [documentation](http://pandas.pydata.org/pandas-docs/stable/) for more advanced usage.Pandas was created by [<NAME>](http://wesmckinney.com/). Many of the examples here are drawn from Wes McKinney's book [Python for Data Analysis](http://shop.oreilly.com/product/0636920023784.do), which includes a github repo of [code samples](https://github.com/wesm/pydata-book).<jupyter_code>import pandas as pd
import numpy as np
from matplotlib import pyplot as plt
%matplotlib inline<jupyter_output><empty_output><jupyter_text>Pandas Data Structures: SeriesA Series represents a one-dimensional array of data. The main difference between a Series and numpy array is that a Series has an _index_. The index contains the labels that we use to access the data.There are many ways to [create a Series](https://pandas.pydata.org/pandas-docs/stable/dsintro.htmlseries). We will just show a few.<jupyter_code>names = ['Ryan', 'Chiara', 'Johnny']
values = [35, 36, 1.8]
ages = pd.Series(values, index=names)
ages<jupyter_output><empty_output><jupyter_text>Series have built in plotting methods.<jupyter_code>ages.plot(kind='bar')<jupyter_output><empty_output><jupyter_text>Arithmetic operations and most numpy function can be applied to Series.An important point is that the Series keep their index during such operations.<jupyter_code>np.log(ages) / ages**2<jupyter_output><empty_output><jupyter_text>We can access the underlying index object if we need to:<jupyter_code>ages.index<jupyter_output><empty_output><jupyter_text>We can get values back out using the index via the `.loc` attribute<jupyter_code>ages.loc['Johnny']<jupyter_output><empty_output><jupyter_text>Or by raw position using `.iloc`<jupyter_code>ages.iloc[2]<jupyter_output><empty_output><jupyter_text>If we need to, we can always get the raw data back out as well<jupyter_code>ages.values
ages.index<jupyter_output><empty_output><jupyter_text>Pandas Data Structures: DataFrameThere is a lot more to Series, but they are limit to a single "column". A more useful Pandas data structure is the DataFrame. A DataFrame is basically a bunch of series that share the same index. It's a lot like a table in a spreadsheet.Below we create a DataFrame.<jupyter_code># first we create a dictionary
data = {'age': [35, 36, 1.8],
        'height': [180, 155, 83],
        'weight': [72.5, np.nan, 11.3]}
df = pd.DataFrame(data, index=['Ryan', 'Chiara', 'Johnny'])
df<jupyter_output><empty_output><jupyter_text>Pandas handles missing data very elegantly, keeping track of it through all calculations.<jupyter_code>df.info()<jupyter_output><empty_output><jupyter_text>A wide range of statistical functions are available on both Series and DataFrames.<jupyter_code>df.min()
df.mean()
df.std()
df.describe()<jupyter_output><empty_output><jupyter_text>We can get a single column as a Series using python's getitem syntax on the DataFrame object.<jupyter_code>df['height']<jupyter_output><empty_output><jupyter_text>...or using attribute syntax.<jupyter_code>df.height<jupyter_output><empty_output><jupyter_text>New columns can easily be added to DataFrames<jupyter_code>df['density'] = df.weight / df.height
df<jupyter_output><empty_output><jupyter_text>Merging DataPandas supports a wide range of methods for merging different datasets. These are described extensively in the [documentation](https://pandas.pydata.org/pandas-docs/stable/merging.html). Here we just give a few examples.<jupyter_code>education = pd.Series(['PhD', 'PhD', None, 'masters'],
                     index=['Ryan', 'Chiara', 'Johnny', 'Takaya'],
                     name='education')
# returns a new DataFrame
df.join(education)
# returns a new DataFrame
df.join(education, how='right')
# returns a new DataFrame
df.reindex(['Ryan', 'Chiara', 'Johnny', 'Takaya', 'Kerry'])<jupyter_output><empty_output><jupyter_text>We can also index using a boolean series. This is very useful<jupyter_code>adults = df[df.age > 18]
adults
df['is_adult'] = df.age > 18
df<jupyter_output><empty_output><jupyter_text>PlottingDataFrames have all kinds of [useful plotting](https://pandas.pydata.org/pandas-docs/stable/visualization.html) built in.<jupyter_code>df.plot(kind='scatter', x='age', y='height', grid=True)
df.plot(kind='bar')<jupyter_output><empty_output><jupyter_text>Time IndexesIndexes are very powerful. They are a big part of why Pandas is so useful. There are different indices for different types of data. Time Indexes are especially great!<jupyter_code>two_years = pd.date_range(start='2014-01-01', end='2016-01-01', freq='D')
timeseries = pd.Series(np.sin(2 *np.pi *two_years.dayofyear / 365),
                       index=two_years)
timeseries.plot()<jupyter_output><empty_output><jupyter_text>We can use python's slicing notation inside `.loc` to select a date range.<jupyter_code>timeseries.loc['2015-01-01':'2015-07-01'].plot()<jupyter_output><empty_output><jupyter_text>Stock Market Data* oops - Ryan's links don't work, so we will use static filesNow we read some stock market data from Google finance. I have created direct links to Google and Apple stock price data.<jupyter_code>#!curl -L -o goog.csv http://tinyurl.com/rces-goog
#!curl -L -o aapl.csv http://tinyurl.com/rces-aapl-csv
! cp /home/pangeo/notebooks/GOOGL.csv goog.csv
! cp /home/pangeo/notebooks/AAPL.csv aapl.csv
! head goog.csv<jupyter_output><empty_output><jupyter_text>We can see that this is well-formated, tidy CSV data, ready for immediate ingestion into Pandas.We use Pandas' amazing [read_csv](https://pandas.pydata.org/pandas-docs/stable/generated/pandas.read_csv.html) function to do this.<jupyter_code>goog = pd.read_csv('goog.csv')
goog.head()<jupyter_output><empty_output><jupyter_text>Not bad! But we can do better by giving read_csv some hints.<jupyter_code>goog = pd.read_csv('goog.csv', parse_dates=[0], index_col=0)
goog.head()
goog.info()
goog.Close.plot()
aapl = pd.read_csv('aapl.csv', parse_dates=[0], index_col=0)
aapl.info()
aapl_close = aapl.Close.rename('aapl')
goog_close = goog.Close.rename('goog')
stocks = pd.concat([aapl_close, goog_close], axis=1)
stocks.head()
stocks.plot()<jupyter_output><empty_output><jupyter_text>Pandas knows how to take correlations. And [tons of other computations](https://pandas.pydata.org/pandas-docs/stable/computation.html).<jupyter_code>stocks.corr()<jupyter_output><empty_output><jupyter_text>Because it understands times, it can do really cool stuff like resampling.<jupyter_code># resample by taking the mean over each month

fig, ax = plt.subplots()
stocks.resample('MS').mean().plot(ax=ax, colors=['r', 'b'])
# and each year
stocks.resample('AS').mean().plot(ax=ax, colors=['r', 'b'])<jupyter_output><empty_output><jupyter_text>The string `QS` means "month start. The string `AS` mean "year start". There is a long list of possible [frequency aliases](https://pandas.pydata.org/pandas-docs/stable/timeseries.htmltimeseries-offset-aliases).We can also apply other reduction operations with resample. These are described in the [resample docs](https://pandas.pydata.org/pandas-docs/stable/timeseries.htmlresampling).<jupyter_code># get resample object
rs = stocks.goog.resample('MS')
# standard deviation of each month
rs.std().plot()<jupyter_output><empty_output><jupyter_text>Temperature DataWe download some timeseries data from the [Berkeley Earth(http://berkeleyearth.org/) surface temperature dataset. This is timeseries data from various locations around earth. Let's get our local temperatures.<jupyter_code>! curl -o nyc_temp.txt http://berkeleyearth.lbl.gov/auto/Local/TAVG/Text/40.99N-74.56W-TAVG-Trend.txt<jupyter_output><empty_output><jupyter_text>If we examine this data, we see it is NOT a well formated CSV file. Loading it will be a bit painful, but Pandas makes the job retatively easy.<jupyter_code>! head -72 nyc_temp.txt | tail -8
##### http://berkeleyearth.lbl.gov/locations/40.99N-74.56W
# http://berkeleyearth.lbl.gov/auto/Local/TAVG/Text/40.99N-74.56W-TAVG-Trend.txt


#temp = pd.read_csv('nyc_temp.txt')

col_names = ['year', 'month', 'monthly_anom'] + 10*[]
temp = pd.read_csv('nyc_temp.txt',
                   header=None, usecols=[0, 1, 2], names=col_names,
                   delim_whitespace=True, comment='%')

temp.head()
# need a day
date_df = temp.drop('monthly_anom', axis=1)
date_df['day'] = 1
date_index = pd.DatetimeIndex(pd.to_datetime(date_df))
temp = temp.set_index(date_index).drop(['year', 'month'], axis=1)
temp.head()
temp.plot()
fig, ax = plt.subplots()
temp.plot(ax=ax)
temp.resample('AS').mean().plot(ax=ax)
temp.resample('10AS').mean().plot(ax=ax)<jupyter_output><empty_output><jupyter_text>Pandas can do both time-based resampling and operation over fixed-length rolling windows. These are very similar but distinct; see [discussion in Pandas docs](https://pandas.pydata.org/pandas-docs/stable/computation.htmltime-aware-rolling-vs-resampling).<jupyter_code># more advanced operation on rolling windows
def difference_max_min(data):
    return data.max() - data.min()

rw = temp.rolling('365D')
rw.apply(difference_max_min).plot()<jupyter_output><empty_output><jupyter_text>To create a "climatology" (i.e. the average of all same months), we can use Pandas' [groupby](https://pandas.pydata.org/pandas-docs/stable/groupby.html) functionality.<jupyter_code># diurnal cycle has been removed!
temp.groupby(temp.index.month).mean().plot()
# find the hottest years
temp.groupby(temp.index.year).mean().sort_values('monthly_anom', ascending=False).head(10)<jupyter_output><empty_output><jupyter_text>GroupbyNow we will explore groupby's capabilities more in a public dataset from the City of New York: the [Rat Information Portal](The Rat Information Portal)!<jupyter_code># https://data.cityofnewyork.us/Health/Rats/amyk-xiv9
rats = pd.read_csv('https://data.cityofnewyork.us/api/views/amyk-xiv9/rows.csv',
                  parse_dates=['APPROVED_DATE', 'INSPECTION_DATE'])
rats.info()
rats.head()<jupyter_output><empty_output><jupyter_text>Let's do some grouping to explore the data.<jupyter_code>rats.groupby('INSPECTION_TYPE')['INSPECTION_TYPE'].count()
rats.groupby('BORO_CODE')['BORO_CODE'].count().head()
rats.groupby('STREET_NAME')['STREET_NAME'].count().head(20)<jupyter_output><empty_output><jupyter_text>This dataset clearly needs some cleaning. We can Pandas' [text features](https://pandas.pydata.org/pandas-docs/stable/text.html) to strip the whitespace out of the data.<jupyter_code># clean up street name
street_names_cleaned = rats.STREET_NAME.str.strip()
street_names_cleaned.groupby(street_names_cleaned).count().head(20)
count = street_names_cleaned.groupby(street_names_cleaned).count()
count.sort_values(ascending=False).head(20)<jupyter_output><empty_output><jupyter_text>To get a better idea of the geography, let's plot the locations of the inspections. But first let's look at the statistics.<jupyter_code>rats[['LATITUDE', 'LONGITUDE']].describe()<jupyter_output><empty_output><jupyter_text>There are clearly some weird outliers in the location data. We need to strip these out before plotting.<jupyter_code>valid_latlon = rats[(rats.LATITUDE > 30) & (rats.LONGITUDE < -70)]
valid_latlon.plot.hexbin('LONGITUDE', 'LATITUDE', C='BORO_CODE', cmap='Set1')
# https://github.com/pandas-dev/pandas/issues/10678
valid_latlon.plot.hexbin('LONGITUDE', 'LATITUDE', sharex=False)
valid_latlon.plot.hexbin('LONGITUDE', 'LATITUDE', sharex=False, bins='log', cmap='magma')
manhattan_rats = valid_latlon[valid_latlon.BORO_CODE==1]
manhattan_rats.plot.hexbin('LONGITUDE', 'LATITUDE', sharex=False, bins='log', cmap='magma')
inspection_date = pd.DatetimeIndex(rats.INSPECTION_DATE)

fig, ax = plt.subplots()
rats.groupby(inspection_date.weekday)['JOB_ID'].count().plot(kind='bar', ax=ax)
ax.set_xlabel('weekday');
fig, ax = plt.subplots()
rats.groupby(inspection_date.hour)['JOB_ID'].count().plot(kind='bar', ax=ax)
ax.set_xlabel('hour');
fig, ax = plt.subplots()
rats.groupby(inspection_date.month)['JOB_ID'].count().plot(kind='bar', ax=ax)
ax.set_xlabel('month')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11940298507462686, 'fraction_numerical': 0.013782439155573483, 'mean_word_length': 6.148005766458433, 'pattern_counts': {'":': 0, '<': 139, '<?xml version=': 0, '>': 141, 'https://': 18, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '34', 'dir': 'jupyter-structured-clean-dedup', 'id': '471103', 'license': 'Unlicense', 'n_tokens_mistral': 5109, 'n_tokens_neox': 4823, 'n_words': 1322, 'path': 'Beginner-notebooks/03_pandas.ipynb', 'repo_name': 'Jennie8594/OCPhelp', 'size': '14880'}