<jupyter_start><jupyter_text>*This notebook contains an excerpt from the [Python Data Science Handbook](http://shop.oreilly.com/product/0636920034919.do) by <NAME>; the content is available [on GitHub](https://github.com/jakevdp/PythonDataScienceHandbook).*  Hierarchical Indexing<jupyter_code>import pandas as pd
import numpy as np
population_data = [{'state': 'California', 'year': '2000', 'population': 33871648},
 {'state': 'California', 'year': '2010', 'population': 37253956},
 {'state': 'New York', 'year': '2000', 'population': 18976457},
 {'state': 'New York', 'year': '2010', 'population': 19378102},
 {'state': 'Texas', 'year': '2000', 'population': 20851820},
 {'state': 'Texas', 'year': '2010', 'population': 25145561}]
pop_df = pd.DataFrame(population_data)
pop_df<jupyter_output><empty_output><jupyter_text>Select records of California<jupyter_code>pop_df[pop_df['state']=='California']
pop_df2 = pop_df.set_index('state')
pop_df2
pop_df2.loc['California']
pop_df2.reset_index()<jupyter_output><empty_output><jupyter_text>What is the population of California in year 2000?<jupyter_code>q = pop_df[(pop_df['year']=='2000') & (pop_df['state']=='California')]
q
q.loc[0, 'population']
pop_df2
pop_df2[pop_df2['year']=='2000'].loc['California', 'population']
pop_df
pop_df3 = pop_df.set_index(['state', 'year'])
pop_df3
pop_df3.index
pop_df3.loc['California']
pop_df3.loc[('California', '2010')]
#pop_df.set_index(['year', 'state']).loc['2010']
pop_df3.loc[(:, '2010')]
pop_df3
idx = pd.IndexSlice
pop_df3.loc[idx[:, '2010'], :]
pop_df3.loc[idx[['Texas', 'New York'], '2000':'2010'], :]
pop_df3.T.loc[:,idx[:, '2010']]<jupyter_output><empty_output><jupyter_text>MultiIndex as extra dimensionYou might notice something else here: we could easily have stored the same data using a simple ``DataFrame`` with index and column labels.In fact, Pandas is built with this equivalence in mind. The ``unstack()`` method will quickly convert a multiply indexed ``Series`` into a conventionally indexed ``DataFrame``:<jupyter_code>pop_df3['under18'] = [9267089, 9284094,
                      4687374, 4318033,
                      5906301, 6879014]
pop_df3
f_u18 = pop_df3['under18'] / pop_df3['population']
f_u18
f_u18.unstack()
f_u18.unstack('state')
f_u18.unstack('state').stack()<jupyter_output><empty_output><jupyter_text>Using multi-index to calculate aggregations<jupyter_code>f_u18
f_u18.mean()
f_u18.unstack('state')
f_u18.unstack('state').mean()
f_u18.unstack('year').mean()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1749502982107356, 'fraction_numerical': 0.08469184890656063, 'mean_word_length': 7.5, 'pattern_counts': {'":': 0, '<': 22, '<?xml version=': 0, '>': 22, 'https://': 1, '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': '5', 'dir': 'jupyter-structured-clean-dedup', 'id': '25442', 'license': 'MIT', 'n_tokens_mistral': 1068, 'n_tokens_neox': 902, 'n_words': 189, 'path': '18-introduction_hierarchical_indexing.ipynb', 'repo_name': 'hanisaf/advanced-data-management-and-analytics', 'size': '2524'}