<jupyter_start><jupyter_text>Table of Contents 1&nbsp;&nbsp;Indexing and selecting data1.1&nbsp;&nbsp;Some notes on selecting data1.1.1&nbsp;&nbsp;data[] provides some convenience shortcuts1.1.2&nbsp;&nbsp;Systematic indexing with loc and iloc1.2&nbsp;&nbsp;Boolean indexing (filtering)1.3&nbsp;&nbsp;Some other useful methods: isin and string methods1.4&nbsp;&nbsp;Pitfall: chained indexing (and the 'SettingWithCopyWarning')2&nbsp;&nbsp;Groupby operations2.1&nbsp;&nbsp;Some 'theory': the groupby operation (split-apply-combine)2.2&nbsp;&nbsp;And now applying this on some real data2.3&nbsp;&nbsp;Acknowledgement  Indexing and selecting data Setting the index to the country names:<jupyter_code>countries = countries.set_index('country')
countries<jupyter_output><empty_output><jupyter_text>Some notes on selecting dataOne of pandas' basic features is the labeling of rows and columns, but this makes indexing also a bit more complex compared to numpy. We now have to distuinguish between:- selection by label (index or column name)- selection by position.  `data[]` provides some convenience shortcuts  For a DataFrame, basic indexing selects the columns.Selecting a single column:<jupyter_code>countries['area']<jupyter_output><empty_output><jupyter_text>or multiple columns:<jupyter_code>countries[['area', 'population']]<jupyter_output><empty_output><jupyter_text>But, slicing accesses the rows:<jupyter_code>countries['France':'Netherlands']<jupyter_output><empty_output><jupyter_text>NOTE: Unlike slicing in numpy, the end label is **included**. So as a summary, `[]` provides the following convenience shortcuts:- Series: selecting a label: `s[label]`- DataFrame: selecting a single or multiple columns: `df['col']` or `df[['col1', 'col2']]`- DataFrame: slicing the rows: `df['row_label1':'row_label2']` or `df[mask]`  Systematic indexing with `loc` and `iloc` When using `[]` like above, you can only select from one axis at once (rows or columns, not both). For more advanced indexing, you have some extra attributes:    * `loc`: selection by label* `iloc`: selection by positionThese methods index the different dimensions of the frame:* `df.loc[row_indexer, column_indexer]`* `df.iloc[row_indexer, column_indexer]` Selecting a single element:<jupyter_code>countries.loc['Germany', 'area']<jupyter_output><empty_output><jupyter_text>But the row or column indexer can also be a list, slice, boolean array, ..<jupyter_code>countries.loc['France':'Germany', ['area', 'population']]<jupyter_output><empty_output><jupyter_text>Selecting by position with `iloc` works similar as indexing numpy arrays:<jupyter_code>countries.iloc[0:2,1:3]<jupyter_output><empty_output><jupyter_text>The different indexing methods can also be used to assign data:<jupyter_code>countries2 = countries.copy()
countries2.loc['Belgium':'Germany', 'population'] = 10
countries2<jupyter_output><empty_output><jupyter_text>Boolean indexing (filtering) Often, you want to select rows based on a certain condition. This can be done with 'boolean indexing' (like a where clause in SQL). The indexer (or boolean mask) should be 1-dimensional and the same length as the thing being indexed.<jupyter_code>countries['area'] > 100000
countries[countries['area'] > 100000]<jupyter_output><empty_output><jupyter_text>EXERCISE: Add a column `density` with the population density (note: population column is expressed in millions)     EXERCISE: Select the capital and the population column of those countries where the density is larger than 300     EXERCISE: Add a column 'density_ratio' with the ratio of the density to the mean density     EXERCISE: Change the capital of the UK to Sheffield     EXERCISE: Select all countries whose population density is between 100 and 300 people/km²  Some other useful methods: `isin` and string methods The `isin` method of Series is very useful to select rows that may contain certain values:<jupyter_code>s = countries['capital']
s.isin?
s.isin(['Berlin', 'London'])<jupyter_output><empty_output><jupyter_text>This can then be used to filter the dataframe with boolean indexing:<jupyter_code>countries[countries['capital'].isin(['Berlin', 'London'])]<jupyter_output><empty_output><jupyter_text>Let's say we want to select all data for which the capital starts with a 'B'. In Python, when having a string, we could use the `startswith` method:<jupyter_code>'Berlin'.startswith('B')<jupyter_output><empty_output><jupyter_text>In pandas, these are available on a Series through the `str` namespace:<jupyter_code>countries['capital'].str.startswith('B')<jupyter_output><empty_output><jupyter_text>For an overview of all string methods, see: http://pandas.pydata.org/pandas-docs/stable/api.htmlstring-handling     EXERCISE: Select all countries that have capital names with more than 7 characters     EXERCISE: Select all countries that have capital names that contain the character sequence 'am'  Pitfall: chained indexing (and the 'SettingWithCopyWarning')<jupyter_code>countries.loc['Belgium', 'capital'] = 'Ghent' 
countries
countries['capital']['Belgium'] = 'Antwerp' 
countries
countries[countries['capital'] == 'Antwerp']['capital'] = 'Brussels' 
countries<jupyter_output><empty_output><jupyter_text>How to avoid this?* Use `loc` instead of chained indexing if possible!* Or `copy` explicitly if you don't want to change the original data.  Groupby operations  Some 'theory': the groupby operation (split-apply-combine)The "group by" concept: we want to **apply the same function on subsets of your dataframe, based on some key to split the dataframe in subsets**This operation is also referred to as the "split-apply-combine" operation, involving the following steps:* **Splitting** the data into groups based on some criteria* **Applying** a function to each group independently* **Combining** the results into a data structureSimilar to SQL `GROUP BY` The example of the image in pandas syntax:<jupyter_code>df = pd.DataFrame({'key':['A','B','C','A','B','C','A','B','C'],
                   'data': [0, 5, 10, 5, 10, 15, 10, 15, 20]})
df<jupyter_output><empty_output><jupyter_text>Using the filtering and reductions operations we have seen in the previous notebooks, we could do something like:    df[df['key'] == "A"].sum()    df[df['key'] == "B"].sum()    ...But pandas provides the `groupby` method to do this:<jupyter_code>df.groupby('key').aggregate(np.sum)  # 'sum'
df.groupby('key').sum()<jupyter_output><empty_output><jupyter_text>And many more methods are available.   And now applying this on some real data We go back to the titanic survival data:<jupyter_code>df = pd.read_csv("data/titanic.csv") # or https://raw.githubusercontent.com/AlessandroChecco/pandas-tutorial-solved/master/data/airbase_data.csv
# or df = pd.read_csv('http://pastebin.com/raw/H67fnXSM')
df.head()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11259150805270864, 'fraction_numerical': 0.010980966325036604, 'mean_word_length': 6.833715596330276, 'pattern_counts': {'":': 0, '<': 69, '<?xml version=': 0, '>': 71, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '17', 'dir': 'jupyter-structured-clean-dedup', 'id': '265142', 'license': 'BSD-2-Clause', 'n_tokens_mistral': 2164, 'n_tokens_neox': 2035, 'n_words': 687, 'path': 'JupyterHub - part 2.ipynb', 'repo_name': 'AlessandroChecco/pandas-tutorial', 'size': '6830'}