<jupyter_start><jupyter_text>Grouping by Time and another ColumnIn the previous chapters, we learned how to group by an amount of time with `resample`. Let's do that again here finding the average salary of every employee based on a span of 5 years.<jupyter_code>import pandas as pd
emp = pd.read_csv('../data/employee.csv', parse_dates=['hire_date'], index_col='hire_date')
emp.head(3)
emp.resample('5Y').agg({'salary': 'mean'}).round(-3)<jupyter_output><empty_output><jupyter_text>Replicating `resample` with `groupby` + `Grouper`The following syntax is a bit strange, so it might take reading it a few times to understand what is going on. You can group by time within the `groupby` method but you must use the `pd.Grouper` type to specify the frequency (the offset alias). Specify the frequencyThe main parameter for `Grouper` is `freq`. Set it to the offset alias. I like using the variable name `tg` which stands for 'time grouper'.<jupyter_code>tg = pd.Grouper(freq='5Y')<jupyter_output><empty_output><jupyter_text>Think of `pd.Grouper` like a dictionary that holds informationThe only use case for this object is to pass it into `groupby`. It might be easier to just think of it as a dictionary that holds the frequency. Once we pass it to `groupby`, we can aggregate like we normally do and get the same result as we did with `resample`.<jupyter_code>emp.groupby(tg).agg({'salary':'mean'}).round(-3)<jupyter_output><empty_output><jupyter_text>Grouping by an amount of time and another columnThere are two different ways to group by time and another column. The difference is subtle but important and can make a difference in the result. The datetime column and the other column can either be grouped **together** or grouped **independently**. Let's say we wanted to find the average salary over 5-year time periods for each sex. Group togetherTo group sex and a 5-year time span together, we must use `groupby`. We will simply pass a list of both the `Grouper` object and the column name to groupby.<jupyter_code>tg = pd.Grouper(freq='5Y')
groups = ['sex', tg]
emp.groupby(groups).agg({'salary':'mean'}).round(-3)<jupyter_output><empty_output><jupyter_text>Datetimes are the sameNotice, how the datetimes for both female and male groups are the same. This is not going to be the case below.<jupyter_code>emp.query('sex == "Male"').index.min()
emp<jupyter_output><empty_output><jupyter_text>Group independentlyTo group independently, we first group the non-datetime column with the `groupby` method. The Groupby object has a `resample` method which allows you to then group by an amount of time **within** the groups you just created. You use it just like it was being called from a DataFrame. Notice how the hire dates for males and females are different.<jupyter_code>emp.groupby('sex').resample('5Y').agg({'salary':'mean'}).round(-3)<jupyter_output><empty_output><jupyter_text>Different resultsIts important to see that you will get different results depending on whether you group together or group independently. The reason the results are different is because the earliest male and female employees don't have a hire date that is an exact 5 year multiple difference. The earliest hire date for female employees was 1969 while it is 1968 for males. If the first male and female employees were both hired in 1968 (or 1969), then the returned datetime index would have been the same.  Using a pivot table with `Grouper` for easier comparisonsYou can pass a `Grouper` object to a pivot table to get a nice final product. This groups sex together with time.<jupyter_code>emp.pivot_table(index=tg, columns='sex', values='salary').round(-3)<jupyter_output><empty_output><jupyter_text>Using `Grouper` on a datetime columnIf your datetime column is not in the index, you can still use `Grouper`. Just specify the column name with the `key` parameter. See the example below with `hire_date` not in the index.<jupyter_code>emp2 = pd.read_csv('../data/employee.csv', parse_dates=['hire_date'])
emp2.head(3)
tg2 = pd.Grouper(freq='10Y', key='hire_date')
emp2.groupby(['sex', tg2]).agg({'salary':'mean'})<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.09119496855345911, 'fraction_numerical': 0.009192065795839382, 'mean_word_length': 5.669354838709677, 'pattern_counts': {'":': 0, '<': 33, '<?xml version=': 0, '>': 33, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 2, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '8', 'dir': 'jupyter-structured-clean-dedup', 'id': '555739', 'license': 'MIT', 'n_tokens_mistral': 1216, 'n_tokens_neox': 1166, 'n_words': 533, 'path': 'jupyter_notebooks/pandas/mastering_data_analysis/07. Time Series/05. Grouping by Time and another Column.ipynb', 'repo_name': 'manual123/Nacho-Jupyter-Notebooks', 'size': '4134'}