<jupyter_start><jupyter_text>Subsetting the data About the DataIn this notebook, we will be working with earthquake data from September 18, 2018 - October 13, 2018 (obtained from the US Geological Survey (USGS) using the [USGS API](https://earthquake.usgs.gov/fdsnws/event/1/)) SetupWe will be working with the `data/earthquakes.csv` file again, so we need to handle our imports and read it in.<jupyter_code>from google.colab import drive
drive.mount('/content/drive')
cd /content/drive/MyDrive/Hands-On-Data-Analysis-with-Pandas-2nd-edition/ch_02/
import pandas as pd

df = pd.read_csv('data/earthquakes.csv')<jupyter_output><empty_output><jupyter_text>Selecting columnsGrab an entire column using attribute notation:<jupyter_code>df.mag<jupyter_output><empty_output><jupyter_text>Grab an entire column using dictionary syntax:<jupyter_code>df['mag']<jupyter_output><empty_output><jupyter_text>Selecting multiple columns:<jupyter_code>df[['mag', 'title']]<jupyter_output><empty_output><jupyter_text>Selecting columns using list comprehensions and string operations:<jupyter_code>df[
    ['title', 'time']
    + [col for col in df.columns if col.startswith('mag')]
]<jupyter_output><empty_output><jupyter_text>Breaking down this example:1. the list comprehension<jupyter_code>[col for col in df.columns if col.startswith('mag')]<jupyter_output><empty_output><jupyter_text>2. assembling the list<jupyter_code>['title', 'time'] \
+ [col for col in df.columns if col.startswith('mag')]<jupyter_output><empty_output><jupyter_text>3. using this list as the list of columns<jupyter_code>df[
    ['title', 'time']
    + [col for col in df.columns if col.startswith('mag')]
]<jupyter_output><empty_output><jupyter_text>Slicing Selecting rowsUsing row numbers (inclusive of first index, exclusive of last):<jupyter_code>df[100:103]<jupyter_output><empty_output><jupyter_text>Selecting rows and columns with chaining<jupyter_code>df[['title', 'time']][100:103]<jupyter_output><empty_output><jupyter_text>Order doesn't matter here:<jupyter_code>df[100:103][['title', 'time']].equals(
    df[['title', 'time']][100:103]
)<jupyter_output><empty_output><jupyter_text>So we know how to select rows and columns, but can we update values? Well, if we try using what we have learned so far, we will see the following warning:<jupyter_code>df[110:113]['title']
df[110:113]['title'] = df[110:113]['title'].str.lower()<jupyter_output>/usr/local/lib/python3.7/dist-packages/ipykernel_launcher.py:1: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  """Entry point for launching an IPython kernel.<jupyter_text>Note that it worked here, but `pandas` says we were setting a value on a copy of a slice and that we should use `loc` instead (topic of the following section):<jupyter_code>df[110:113]['title']<jupyter_output><empty_output><jupyter_text>IndexingNow if we do this with `loc` as the warning suggests, everything goes smoothly. Note we have to lower the end index by one since `loc` is inclusive of endpoints:<jupyter_code>df.loc[110:112, 'title'] = df.loc[110:112, 'title'].str.lower()
df.loc[110:112, 'title']<jupyter_output><empty_output><jupyter_text>Indexing with `loc`Selection of the format `loc[row_indexer, column_indexer]` where `:` can be used to select all:<jupyter_code>df.loc[:,'title']<jupyter_output><empty_output><jupyter_text>We can use `loc` to select specific rows and columns without chaining. If we use row numbers with `loc`, they are now **inclusive** of the end index:<jupyter_code>df.loc[10:15, ['title', 'mag']]<jupyter_output><empty_output><jupyter_text>Indexing with `iloc`Exclusive of the endpoint just as Python slicing:<jupyter_code>df.iloc[10:15, [19, 8]]<jupyter_output><empty_output><jupyter_text>We can use slicing syntax with `iloc` for both rows and columns:<jupyter_code>df.iloc[10:15, 6:10]<jupyter_output><empty_output><jupyter_text>When using `loc`, we can slice on column names. This will be inclusive of the endpoint because you can't be expected to know what the next column name will be. As such, we have multiple ways to achieve the same end goal:<jupyter_code>df.iloc[10:15, 6:10].equals(
    df.loc[10:14, 'gap':'magType']
)<jupyter_output><empty_output><jupyter_text>Looking up scalar valuesWe used `loc` and `iloc` to grab subsets of the dataframe. However, if we are just interested in the specific value at a given `[row, column]`, then we can use `iat` and `at`. We use `at` with labels:<jupyter_code>df.at[10, 'mag']<jupyter_output><empty_output><jupyter_text>...and `iat` with integer indices:<jupyter_code>df.iat[10, 8]<jupyter_output><empty_output><jupyter_text>FilteringWe can filter our dataframes using a **Boolean mask**, which can be made as follows:<jupyter_code>df.mag > 2<jupyter_output><empty_output><jupyter_text>To use a mask for selection, we simply place it inside the brackets:<jupyter_code>df[df.mag >= 7.0]<jupyter_output><empty_output><jupyter_text>We can use masks with `loc`:<jupyter_code>df.loc[
    df.mag >= 7.0,
    ['alert', 'mag', 'magType', 'title', 'tsunami', 'type']
]<jupyter_output><empty_output><jupyter_text>Masks can be created using multiple criteria when combined with bitwise operators `&` for AND and `|` for OR. We must also surround each criterion with parentheses. We can't use `and`/`or` here because we need to evaluate row by row:<jupyter_code>df.loc[
    (df.tsunami == 1) & (df.alert == 'red'),
    ['alert', 'mag', 'magType', 'title', 'tsunami', 'type']
]<jupyter_output><empty_output><jupyter_text>An example with an OR condition, which is less restrictive:<jupyter_code>df.loc[
    (df.tsunami == 1) | (df.alert == 'red'),
    ['alert', 'mag', 'magType', 'title', 'tsunami', 'type']
]<jupyter_output><empty_output><jupyter_text>Masks can be created from any criteria that results in a Boolean. For example, we can select all earthquakes with the string `Alaska` in the `place` column with a non-null value for the `alert` column. To get non-nulls, we can use the `isnull()` method with the bitwise negation operator (`~`) or the `notnull()` method:<jupyter_code>df.loc[
    (df.place.str.contains('Alaska')) & (df.alert.notnull()),
    ['alert', 'mag', 'magType', 'title', 'tsunami', 'type', 'place']
]<jupyter_output><empty_output><jupyter_text>We can even use regular expressions here:<jupyter_code>df.loc[
    (df.place.str.contains(r'CA|California$')) & (df.mag > 3.8),
    ['alert', 'mag', 'magType', 'title', 'tsunami', 'type','place']
]<jupyter_output><empty_output><jupyter_text>We can use the `between()` method to turn 2 individual checks (is less than or equal to some maximum value and is greater than or equal to some minimum value) into a single one. Note this is inclusive of the endpoint by default:<jupyter_code>df.loc[
    df.mag.between(6.5, 7.5),
    ['alert', 'mag', 'magType', 'title', 'tsunami', 'type']
]<jupyter_output><empty_output><jupyter_text>We can use the `isin()` method to check for membership in a list of values:<jupyter_code>df.loc[
    df.magType.isin(['mw', 'mwb']),
    ['alert', 'mag', 'magType', 'title', 'tsunami', 'type']
]<jupyter_output><empty_output><jupyter_text>We can grab the index of the minimum and maximum values of a given column and use those to select the entire row where they occur:<jupyter_code>[df.mag.idxmin(), df.mag.idxmax()]
df.loc[
    [df.mag.idxmin(), df.mag.idxmax()],
    ['alert', 'mag', 'magType', 'title', 'tsunami', 'type']
]<jupyter_output><empty_output><jupyter_text>Note that there is a `filter()` method, but it doesn't filter the data in the same sense as we discussed in this section. Here are a few things you can do with this method.- grab columns of a dataframe by passing a list to `items`:<jupyter_code>df.filter(items=['mag', 'magType']).head()<jupyter_output><empty_output><jupyter_text>- grab all the columns that contain a string with the `like` parameter:<jupyter_code>df.filter(like='mag').head()<jupyter_output><empty_output><jupyter_text>- use regular expressions; here, we select any columns that start with `t`:<jupyter_code>df.filter(regex=r'^t').head()<jupyter_output><empty_output><jupyter_text>- use `filter()` along the rows, by passing in `axis=0`. Here, we will use the `place` column as the index (we will cover `set_index()` in chapter 3):<jupyter_code>df.set_index('place').filter(like='Japan', axis=0).filter(items=['mag', 'magType', 'title']).head()<jupyter_output><empty_output><jupyter_text>This also works on `Series` objects and will run on the index:<jupyter_code>df.set_index('place').title.filter(like='Japan').head()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.13775452166988966, 'fraction_numerical': 0.015811625526106245, 'mean_word_length': 6.685314685314685, 'pattern_counts': {'":': 0, '<': 144, '<?xml version=': 0, '>': 148, 'https://': 2, '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': '36', 'dir': 'jupyter-structured-clean-dedup', 'id': '82262', 'license': 'MIT', 'n_tokens_mistral': 3141, 'n_tokens_neox': 3000, 'n_words': 858, 'path': 'ch_02/5-subsetting_data.ipynb', 'repo_name': 'ikwak2/Hands-On-Data-Analysis-with-Pandas-2nd-edition', 'size': '8791'}