<jupyter_start><jupyter_text>Aggregating data with pandas and numpy About the DataIn this notebook, we will be working with 2 datasets:- Facebook's stock price throughout 2018 (obtained using the [`stock_analysis` package](https://github.com/stefmolin/stock-analysis)).- daily weather data for NYC from the [National Centers for Environmental Information (NCEI) API](https://www.ncdc.noaa.gov/cdo-web/webservices/v2).*Note: The NCEI is part of the National Oceanic and Atmospheric Administration (NOAA) and, as you can see from the URL for the API, this resource was created when the NCEI was called the NCDC. Should the URL for this resource change in the future, you can search for "NCEI weather API" to find the updated one.* Background on the weather dataData meanings:- `AWND`: average wind speed- `PRCP`: precipitation in millimeters- `SNOW`: snowfall in millimeters- `SNWD`: snow depth in millimeters- `TMAX`: maximum daily temperature in Celsius- `TMIN`: minimum daily temperature in Celsius Setup<jupyter_code>import numpy as np
import pandas as pd

fb = pd.read_csv('data/fb_2018.csv', index_col='date', parse_dates=True).assign(
    trading_volume=lambda x: pd.cut(x.volume, bins=3, labels=['low', 'med', 'high'])
)
fb.head()
weather = pd.read_csv('data/weather_by_station.csv', index_col='date', parse_dates=True)
weather.head()<jupyter_output><empty_output><jupyter_text>Before we dive into any calculations, let's make sure `pandas` won't put things in scientific notation. We will modify how floats are formatted for displaying. The format we will apply is `.2f`, which will provide the float with 2 digits after the decimal point:<jupyter_code>pd.set_option('display.float_format', lambda x: '%.2f' % x)<jupyter_output><empty_output><jupyter_text>Summarizing DataFramesWe learned about `agg()` in the [`2-dataframe_operations.ipynb`](./2-dataframe_operations.ipynb) notebook when we learned about window calculations; however, we can call this on the dataframe directly to aggregate its contents into a single series:<jupyter_code>fb.agg({
    'open': np.mean, 
    'high': np.max, 
    'low': np.min, 
    'close': np.mean, 
    'volume': np.sum
})<jupyter_output><empty_output><jupyter_text>We can use this to find the total snowfall and precipitation recorded in Central Park in 2018:<jupyter_code>weather.query('station == "GHCND:USW00094728"')\
    .pivot(columns='datatype', values='value')[['SNOW', 'PRCP']]\
    .sum()<jupyter_output><empty_output><jupyter_text>This is equivalent to passing `'sum'` to `agg()`:<jupyter_code>weather.query('station == "GHCND:USW00094728"')\
    .pivot(columns='datatype', values='value')[['SNOW', 'PRCP']]\
    .agg('sum')<jupyter_output><empty_output><jupyter_text>Note that we aren't limited to providing a single aggregation per column. We can pass a list, and we will get a dataframe back instead of a series. Null values are placed where we don't have a calculation result to display:<jupyter_code>fb.agg({
    'open': 'mean',
    'high': ['min', 'max'],
    'low': ['min', 'max'],
    'close': 'mean'
})<jupyter_output><empty_output><jupyter_text>Using `groupby()`Often we won't want to aggregate on the entire dataframe, but on groups within it. For this purpose, we can run `groupby()` before the aggregation. If we group by the `trading_volume` column, we will get a row for each of the values it takes on:<jupyter_code>fb.groupby('trading_volume').mean()<jupyter_output><empty_output><jupyter_text>After we call `groupby()`, we can still select columns for aggregation:<jupyter_code>fb.groupby('trading_volume')['close'].agg(['min', 'max', 'mean'])<jupyter_output><empty_output><jupyter_text>We can still provide a dictionary specifying the aggregations to perform, but passing a list for a column will result in a hierarchical index for the columns:<jupyter_code>fb_agg = fb.groupby('trading_volume').agg({
    'open': 'mean',
    'high': ['min', 'max'],
    'low': ['min', 'max'],
    'close': 'mean'
})
fb_agg<jupyter_output><empty_output><jupyter_text>The hierarchical index in the columns looks like this:<jupyter_code>fb_agg.columns<jupyter_output><empty_output><jupyter_text>Using a list comprehension, we can join the levels (in a tuple) with an `_` at each iteration:<jupyter_code>fb_agg.columns = ['_'.join(col_agg) for col_agg in fb_agg.columns]
fb_agg.head()<jupyter_output><empty_output><jupyter_text>We can group on values in the index if we tell `groupby()`, which `level` to use:<jupyter_code>weather.loc['2018-10'].query('datatype == "PRCP"')\
    .groupby(level=0).mean().head().squeeze()<jupyter_output><empty_output><jupyter_text>We can also create a `Grouper` object, which can also roll up the datetimes in the index. Here, we find the quarterly total precipitation per station:<jupyter_code>weather.query('datatype == "PRCP"').groupby(
    ['station_name', pd.Grouper(freq='Q')]
).sum().unstack().sample(5, random_state=1)<jupyter_output><empty_output><jupyter_text>Note that we can use `filter()` to exclude some groups from aggregation. Here, we only keep groups with names ending in "NY US" in the group's `name` attribute, which is the station name in this case:<jupyter_code>weather.groupby('station_name').filter( # station names with "NY US" in them
    lambda x: x.name.endswith('NY US')
).query('datatype == "SNOW"').groupby('station_name').sum().squeeze() # aggregate and make a series (squeeze)<jupyter_output><empty_output><jupyter_text>Let's see which months have the most precipitation. First, we need to group by day and average the precipitation across the stations. Then we can group by month and sum the resulting precipitation. We use `nlargest()` to give the 5 months with the most precipitation:<jupyter_code>weather.query('datatype == "PRCP"')\
    .groupby(level=0).mean()\
    .groupby(pd.Grouper(freq='M')).sum().value.nlargest()<jupyter_output><empty_output><jupyter_text>Perhaps the previous result was surprising. The saying goes "April showers bring May flowers"; yet April wasn't in the top 5 (neither was May for that matter). Snow will count towards precipitation, but that doesn't explain why summer months are higher than April. Let's look for days that accounted for a large percentage of the precipitation in a given month. In order to do so, we need to calculate the average daily precipitation across stations and then find the total per month. This will be the denominator. However, in order to divide the daily values by the total for their month, we will need a series of equal dimensions. This means we will need to use `transform()`:<jupyter_code>weather.query('datatype == "PRCP"')\
    .rename(dict(value='prcp'), axis=1)\
    .groupby(level=0).mean()\
    .groupby(pd.Grouper(freq='M'))\
    .transform(np.sum)['2018-01-28':'2018-02-03']<jupyter_output><empty_output><jupyter_text>Notice how we have the same value repeated for each day in the month it belongs to. This will allow us to calculate the percentage of the monthly precipitation that occurred each day and then pull out the largest values:<jupyter_code>weather\
    .query('datatype == "PRCP"')\
    .rename(dict(value='prcp'), axis=1)\
    .groupby(level=0).mean()\
    .assign(
        total_prcp_in_month=lambda x: \
            x.groupby(pd.Grouper(freq='M')).transform(np.sum),
        pct_monthly_prcp=lambda x: \
            x.prcp.div(x.total_prcp_in_month)
    )\
    .nlargest(5, 'pct_monthly_prcp')<jupyter_output><empty_output><jupyter_text>`transform()` can be used on dataframes as well. We can use it to easily standardize the data:<jupyter_code>fb[['open', 'high', 'low', 'close']]\
    .transform(lambda x: (x - x.mean()).div(x.std()))\
    .head()<jupyter_output><empty_output><jupyter_text>Pivot tables and crosstabsWe saw pivots in [`ch_03/4-reshaping_data.ipynb`](../ch_03/4-reshaping_data.ipynb); however, we weren't able to provide any aggregations. With `pivot_table()`, we get the mean by default. In its simplest form, we provide a column to place along the columns:<jupyter_code>fb.pivot_table(columns='trading_volume')<jupyter_output><empty_output><jupyter_text>By placing the trading volume in the index, we get the transpose:<jupyter_code>fb.pivot_table(index='trading_volume')<jupyter_output><empty_output><jupyter_text>With `pivot()`, we also weren't able to handle multi-level indices or indices with repeated values. For this reason we haven't been able to put the weather data in the wide format. The `pivot_table()` method solves this issue:<jupyter_code>weather.reset_index().pivot_table(
    index=['date', 'station', 'station_name'], 
    columns='datatype', 
    values='value',
    aggfunc='median'
).reset_index().tail()<jupyter_output><empty_output><jupyter_text>We can use the `pd.crosstab()` function to create a frequency table. For example, if we want to see how many low-, medium-, and high-volume trading days Facebook stock had each month, we can use crosstab:<jupyter_code>pd.crosstab(
    index=fb.trading_volume,
    columns=fb.index.month,
    colnames=['month'] # name the columns index
)<jupyter_output><empty_output><jupyter_text>We can normalize with the row or column totals with the `normalize` parameter. This shows percentage of the total:<jupyter_code>pd.crosstab(
    index=fb.trading_volume,
    columns=fb.index.month,
    colnames=['month'],
    normalize='columns'
)<jupyter_output><empty_output><jupyter_text>If we want to perform a calculation other than counting the frequency, we can pass the column to run the calculation on to `values` and the function to use to `aggfunc`:<jupyter_code>pd.crosstab(
    index=fb.trading_volume,
    columns=fb.index.month,
    colnames=['month'],
    values=fb.close,
    aggfunc=np.mean
)<jupyter_output><empty_output><jupyter_text>We can also get row and column subtotals with the `margins` parameter. Let's count the number of times each station recorded snow per month and include the subtotals:<jupyter_code>snow_data = weather.query('datatype == "SNOW"')
pd.crosstab(
    index=snow_data.station_name,
    columns=snow_data.index.month,
    colnames=['month'],
    values=snow_data.value,
    aggfunc=lambda x: (x > 0).sum(),
    margins=True, # show row and column subtotals
    margins_name='total observations of snow' # name the subtotals
)<jupyter_output><empty_output>
StarcoderdataJupyterStructuredCleanDedup