<jupyter_start><jupyter_text>SQL-type operations If you know something about relational databases and SQL, you may have heard of JOIN and GROUP BY.<jupyter_code>import pandas as pd<jupyter_output><empty_output><jupyter_text>Joining<jupyter_code>mlo, gl = pd.read_csv('../data/co2-mm-mlo.csv', na_values=-99.99, index_col='Date', parse_dates=True), \
    pd.read_csv('../data/co2-mm-gl.csv', na_values=-99.99, index_col='Date', parse_dates=True)
    # pd.read_csv('https://python.g-node.org/wiki/_media/co2-mm-mlo.csv')
ml, gl = mlo[['Average']], gl[['Average']]

ml.columns, gl.columns = ['Average_mlo'], ['Average_gl']

ml = ml[ml.index >= '1980-01']

ml, gl = ml.head(), gl.head()<jupyter_output><empty_output><jupyter_text>We can concatenate the two DataFrames.<jupyter_code>pd.concat([ml, gl])<jupyter_output><empty_output><jupyter_text>Alternatively, the above can be obtained with the self-describing `append()` method.<jupyter_code>ml.append(gl)<jupyter_output><empty_output><jupyter_text>By default, `concat()` concatenates along the rows (axis 0). What we did previously was 'concatenating' along the columns (axis 1). It is actually a join operation, on (index or *key*) `Date`.<jupyter_code>pd.concat([ml, gl], axis=1)<jupyter_output><empty_output><jupyter_text>Indeed, we could alternatively use the `join()` method.<jupyter_code>ml.join(gl)<jupyter_output><empty_output><jupyter_text>Wait! Which frame's index is used? JOIN can be left, right, outer, or inner (picture unions and intersections).<jupyter_code>mlo = pd.read_csv('../data/co2-mm-mlo.csv', na_values=-99.99, index_col='Date', parse_dates=True)
mlo = mlo[['Average']]
mlo.head()
mlo.join(gl)
mlo.join(gl, how='inner')<jupyter_output><empty_output><jupyter_text>Hands-on exercise 1. How else, still using the `join()` method, could we obtain `Average` and `Average_gl` for dates between 1980-01 and 1980-05?  Grouping We introduce the split-apply-combine approach:* split the data into groups;* apply a function to each group independently;* combine the results into an appropriate data structure.<jupyter_code>z = pd.Series([0.5, 0.8, 0.6, 0.3], index=pd.MultiIndex.from_product([[0, 1], [0, 1]], names=['x', 'y']))
z
z.groupby('x')
z.groupby('x').apply(lambda u: u.min())<jupyter_output><empty_output><jupyter_text>The function in question is an aggregation (for each group, return the minimum value). The length of the result is the number of different groups (here, number of unique x values). Aggregation reduces the size of the data structure.<jupyter_code>z.groupby(level=0).apply(lambda u: u.min())<jupyter_output><empty_output><jupyter_text>The aggregation function can be applied directly, if it is available.<jupyter_code>z.groupby('x').min()<jupyter_output><empty_output><jupyter_text>With a hierarchical index, the level parameter can even be passed directly to certain aggregation functions.<jupyter_code>z.min(level='x')<jupyter_output><empty_output><jupyter_text>Counting the number of records in each group is also an aggregation.<jupyter_code>z.groupby('x').size()<jupyter_output><empty_output><jupyter_text>For each unique values of x (which are 0 and 1), we have two entries. The grouping object (here, x) is referred to as the key.<jupyter_code>z.groupby(['x', 'y']).size()
z.groupby('y').describe()
import numpy as np
z.groupby('y').apply(lambda u: np.std(u, ddof=1))
z.groupby('y').apply(lambda u: np.std(u))
z.groupby('y').apply(np.std)<jupyter_output><empty_output><jupyter_text>Filtering is another kind of operation which can be applied to each group. Filtering may reduce the size of the data structure, since some groups might get filtered out.<jupyter_code>z
z.groupby('y').apply(lambda u: u.min() > 0.4)
z.groupby('y').filter(lambda u: u.min() > 0.4)<jupyter_output><empty_output><jupyter_text>The third kind of operation is transformation, where the size of the data structure is preserved.<jupyter_code>z.groupby('y').transform(lambda u: u.min())<jupyter_output><empty_output>
StarcoderdataJupyterStructuredCleanDedup