<jupyter_start><jupyter_text>SQL-Type Operation 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 = pd.read_csv('./Pandas Dataset - I/co2-mm-mlo.csv', na_values=-99.99, index_col='Date', parse_dates=True)
gl = pd.read_csv('./Pandas Dataset - I/co2-mm-gl.csv', na_values=-99.99, index_col='Date', parse_dates=True)
print(mlo,gl)
ml, gl = mlo[['Average']], gl[['Average']]
print(ml,gl)
ml.columns, gl.columns = ['Average_mlo'], ['Average_gl']
print(ml,gl)
ml = ml[ml.index >= '1980-01']
print(ml)
ml, gl = ml.head(), gl.head()
print(ml, gl)<jupyter_output>Average_mlo
Date                   
1980-01-01       337.90
1980-02-01       338.34
1980-03-01       340.01
1980-04-01       340.93
1980-05-01       341.48             Average_gl
Date                  
1980-01-01      338.45
1980-02-01      339.14
1980-03-01      339.46
1980-04-01      339.86
1980-05-01      340.30<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('./Pandas Dataset - I/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') #only common index value will select...<jupyter_output><empty_output><jupyter_text>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.1, 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')
z.min(level=0)<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('x').describe()
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').min()
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>
{'fraction_non_alphanumeric': 0.14295774647887324, 'fraction_numerical': 0.04107981220657277, 'mean_word_length': 5.565485362095532, 'pattern_counts': {'":': 0, '<': 60, '<?xml version=': 0, '>': 63, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '15', 'dir': 'jupyter-structured-clean-dedup', 'id': '132515', 'license': 'MIT', 'n_tokens_mistral': 1627, 'n_tokens_neox': 1478, 'n_words': 381, 'path': 'Day_41(Pandas).ipynb', 'repo_name': 'HaseebRajput007/100DaysOfCode-DataScience', 'size': '4260'}