<jupyter_start><jupyter_text>Indexing - Selecting subsets of data**Indexing** or **selecting subsets of data** is one of the most confusing aspects to pandas, which is unfortunate, because it is something done so frequently. There is so much to selecting subsets of data that I have dedicated a [7-part series](https://medium.com/dunder-data/selecting-subsets-of-data-in-pandas-6fcd0170be9c) to it.Reasons why selecting subsets of data is confusing:* **Indexing** is a confusing term. **Selecting subsets of data** is more descriptive* Each row and column may be referenced by either their **label** or **integer location*** This dual reference capability is powerful but confusing* The documentation uses the term **position** instead of the more descriptive **integer location**. The indexer **`.iloc`** is an abbreviation of integer location. * There are six indexers, **`[]`**, **`.iloc`**, **`.loc`**, **`.ix`**, **`.at`**, **`.iat`**, that each do something different* **`.ix`** has recently been deprecated in favor of **`.loc`** and **`.iloc`**, but old Stack Overflow answers and tutorials still show it. New questions get asked each day that use this horrible indexer. Getting help on `[]`, `.iloc`, `loc`If you struggle with these indexers, I encourage you to read my detailed notebook with many practice exercises. [Selecting subsets of Data in Pandas with Exercises](../Selecting%20Subsets%20of%20Data%20in%20Pandas%20part%201.ipynb) `.ix` deprecationOne of the largest changes to the pandas API was the deprecation of the **`.ix`** indexer in version 0.20. The **`.ix`** indexer was versatile, ambiguous, and confusing and personally, I was very excited to see its deprecation. **`ix`** was able to select subsets of data by both label and integer location. For instance, if we wanted to select the math and verbal SAT scores for schools with integer location of 5, 505, and 1301 we could do the following:<jupyter_code>import pandas as pd
import numpy as np
college = pd.read_csv('../data/college.csv', index_col=0)
# This is deprecated. NEVER DO THIS!!
college.ix[[5, 505, 1301], ['SATMTMID', 'SATVRMID']]<jupyter_output><empty_output><jupyter_text>Alternatives to simultaneous selection by labels and integer locationIn the rare event that you need to do simultaneous selection by label and integer location, you can do one of the following:* Convert the integer locations to labels and use **`.loc`*** Convert the labels to integer locations and use **`.iloc`**  Convert integer locations to labelsTo convert integer locations to labels, use the index or columns to select the correct labels.<jupyter_code>labels = college.index[[5, 505, 1301]]
labels
college.loc[labels, ['SATMTMID', 'SATVRMID']]<jupyter_output><empty_output><jupyter_text>Convert labels to integer locationsConverting labels to integer locations is a bit trickier. The documentation suggests the Index method **`get_loc`** for a single value and **`get_indexer`** for multiple values. These only work with Indexes containing only unique values.<jupyter_code>cols = ['SATMTMID', 'SATVRMID']
ilocs = college.columns.get_indexer(cols)
ilocs
college.iloc[[5, 505, 1301], ilocs]<jupyter_output><empty_output><jupyter_text>If you were getting a single column:<jupyter_code>college.iloc[[5, 505, 1301], college.columns.get_loc('SATMTMID')]<jupyter_output><empty_output><jupyter_text>Assigning new values to subsets of dataThe **`SettingWithCopy`** warning is a fairly common, yet important nuisance that will pop up for nearly everyone from time to time. It gets triggered whenever you select a subset of data and then in a separate operation, assign new values to that subset. This is referred to as [chained indexing in the documentation](http://pandas.pydata.org/pandas-docs/stable/indexing.htmlwhy-does-assignment-fail-when-using-chained-indexing).The warning arises because pandas is unable to determine if you want the original DataFrame modified or just the subset. Furthermore, sometimes the original DataFrame is modified and sometimes it isn't. It's very confusing to know what is going on. Let's see some simple, straightforward examples that activate the **`SettingWithCopy`** warning with different results of the original data. Make a fresh copy of the college dataset so we don't modify it.<jupyter_code>c1 = college.copy()
c2 = college.copy()<jupyter_output><empty_output><jupyter_text>Select a column as a Series and as a DataFrame<jupyter_code>city1 = c1['CITY']   
city2 = c2[['CITY']]
city1.head()
city2.head()<jupyter_output><empty_output><jupyter_text>Make the assignment that triggers the warning<jupyter_code>city1.iloc[2:5] = 'NEW CITY'
city2.iloc[2:5] = 'NEW CITY'<jupyter_output><empty_output><jupyter_text>Confirm that our subset has new values<jupyter_code>city1.head()
city2.head()<jupyter_output><empty_output><jupyter_text>Look at original data to see what has changed<jupyter_code>c1['CITY'].head()
c2['CITY'].head()<jupyter_output><empty_output><jupyter_text>ConclusionSelecting a Series creates a **view** of the original, which is then modified. Selecting a DataFrame creates a **copy** of the original.A view is a just a pointer to the original data. A copy is a fresh new object not connected to the original.  Double check that we get the same results with `.loc`<jupyter_code>idx = ['University of Alabama at Birmingham', 'Amridge University']
city1.loc[idx] = 'NEW CITY 99999'
city2.loc[idx] = 'NEW CITY 99999'
c1['CITY'].head()
c2[['CITY']].head()<jupyter_output><empty_output><jupyter_text>ConclusionThe indexer used on the view/copy is irrelevant. The only thing that matters is whether the very first selection creates a copy or a view.  Example select rows first with `.iloc`Let's select some rows with **`.iloc`** and then set some values on this subset.<jupyter_code>c1 = college.copy()
df1 = c1.iloc[:5]  # df1 is our new subset
df1<jupyter_output><empty_output><jupyter_text>Make a couple different assignments<jupyter_code>df1.iloc[:2] = 99
df1['STABBR'] = 'TX'
c1.head()<jupyter_output><empty_output><jupyter_text>ConclusionIt appears that **`.iloc`** creates a view and our original data is modified  Exercise 1Use **`.iloc`** to select the first 5 rows and columns. Is a view or copy created?<jupyter_code># your code here<jupyter_output><empty_output><jupyter_text>Exercise 2Select a few rows and all the columns with **`.loc`**. Is a view or copy created?<jupyter_code># your code here<jupyter_output><empty_output><jupyter_text>Two common scenarios when assigning subsets of data1. You want to make an assignment to a particular subset of your DataFrame but want to keep doing analysis on the entire DataFrame2. You want to select a subset of data and store it as its own variable and modify that subset without modifying your original data.We will cover how to properly handle each scenario with getting the **`SettingWithCopy`** warning. Scenario 1For scenario 1, you won't be creating a new variable to reference the subset of data. Instead, you will use a single set of brackets to make the assignment. Let's look at a smaller sample dataset to make this clear.<jupyter_code>df = pd.read_csv('../data/sample_data.csv', index_col=0)
df<jupyter_output><empty_output><jupyter_text>Let's say, we want to change the color for Aaron and Dean to **`PURPLE`**. Doing this idiomatically with a single set of brackets would look like this:<jupyter_code>df.loc[['Aaron', 'Dean'], 'color'] = 'PURPLE'
df<jupyter_output><empty_output><jupyter_text>Incorrect versionsIt's good to look at improper ways to do this so you can spot this in other code.<jupyter_code>df = pd.read_csv('../data/sample_data.csv', index_col=0)
df['color'][['Aaron', 'Dean']] = 'PURPLE'
df<jupyter_output><empty_output><jupyter_text>This was an example of **chained indexing**, which you should never do. The first indexing happens with **`df['color']`**. A temporary object is created which then indexes again with **`[['Aaron', 'Dean']]`**. Another incorrect version<jupyter_code># NO WARNING! Assignment didn't complete. Looks like a bug
df = pd.read_csv('../data/sample_data.csv', index_col=0)
df.loc[['Aaron', 'Dean']]['color'] = 'PURPLE'
df<jupyter_output><empty_output><jupyter_text>Using boolean indexingChange the **`score`** of all the people under 30 to 100.<jupyter_code>df = pd.read_csv('../data/sample_data.csv', index_col=0)

df.loc[df['age'] < 30, 'score'] = 100
df<jupyter_output><empty_output><jupyter_text>Exercise 1Turn to your neighbors. Explain to them why the following did not work. Write another incorrect version of it as well as a correct version.<jupyter_code>df = pd.read_csv('../data/sample_data.csv', index_col=0)

df[df.age < 30]['score'] = 100
df<jupyter_output><empty_output><jupyter_text>Exercise 2Make the following idiomatic<jupyter_code>df = pd.read_csv('../data/sample_data.csv', index_col=0)

df.loc[df['color'].isin(['red', 'green']), 'score'] = 99
# your code here<jupyter_output><empty_output><jupyter_text>Exercise 3Make the following idiomatic<jupyter_code>df = pd.read_csv('../data/sample_data.csv', index_col=0)

df[df['state'] == 'TX'][df['age'] > 30][['height', 'score']] = 99
# your code here<jupyter_output><empty_output><jupyter_text>Exercise 4Make the following idiomatic<jupyter_code>df = pd.read_csv('../data/sample_data.csv', index_col=0)

df.iloc[[6,4,2,3]].iloc[:, :3] = 'CHANGED'<jupyter_output><empty_output><jupyter_text>Summary of Scenario 1:* Use exactly one set of brackets to make the assignment * You know you've made a mistake when you see back to back brackets like this **`][`*** Separate row and column selection by a comma with the same set of brackets  Scenario 2Scenario 2 exists when you take a subset of data and want to keep working with just that subset. You may not care at all about the original DataFrame, but you probably won't want to change its data. In this scenario, you will use the **`copy`** method to create a fresh independent copy of your subset and then make changes to that.<jupyter_code>df = pd.read_csv('../data/sample_data.csv', index_col=0)
df<jupyter_output><empty_output><jupyter_text>Let's say we are only interested in the **`food`** and **`score`** columns and store those columns as a new variable<jupyter_code>food_score = df[['food', 'score']]<jupyter_output><empty_output><jupyter_text>Now, if we wanted to change the scores of the rows with food equal to steak or lamb to 99 we would do the following, which triggers the warning.<jupyter_code>criteria = food_score['food'].isin(['Steak', 'Lamb'])
food_score.loc[criteria, 'score'] = 99
food_score<jupyter_output><empty_output><jupyter_text>This warning was triggered by the very first step```>>> food_score = df[['food', 'score']]```The warning will be triggered for any change to this dataset. For instance, let's add a new column:<jupyter_code>food_score['newcol'] = -1
food_score<jupyter_output><empty_output><jupyter_text>Let's look at our original DataFrame to see if it has changed<jupyter_code>df<jupyter_output><empty_output><jupyter_text>It is still in tact, but pandas does not know whether you wanted it that way. To divorce this new dataset from its original, use the **`copy`** method<jupyter_code>food_score = df[['food', 'score']].copy()

criteria = food_score['food'].isin(['Steak', 'Lamb'])
food_score.loc[criteria, 'score'] = 99
food_score
food_score['newcol'] = -1
food_score<jupyter_output><empty_output><jupyter_text>Pandas now knows that this is independent from any other DataFrame, so no warning appears.  Exercise 5Select the first three rows and first three columns into a new variable and then change all the values to **`CHANGED`** without getting a **`SettingWithCopy`** warning.<jupyter_code># run this first
df = pd.read_csv('../data/sample_data.csv', index_col=0)
# your code here<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11127118644067796, 'fraction_numerical': 0.014322033898305084, 'mean_word_length': 6.1090361445783135, 'pattern_counts': {'":': 0, '<': 123, '<?xml version=': 0, '>': 125, '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': '30', 'dir': 'jupyter-structured-clean-dedup', 'id': '158214', 'license': 'BSD-3-Clause', 'n_tokens_mistral': 3769, 'n_tokens_neox': 3576, 'n_words': 1362, 'path': 'Idiomatic Pandas/02 Idiomatic Pandas.ipynb', 'repo_name': 'Juliannazhao/Learn-Pandas', 'size': '11800'}