<jupyter_start><jupyter_text>*Dataframe* operationsBefore proceeding with the Water Flow exercise, let's first pause and discuss data structures: **vectors**/**series**, **matrices**, **N-dimensional arrays**, and particularly the **dataframe**. What is a dataframe?The **dataframe** is a key tool in data analysis. Dataframes store data a specific format, one that facilitates many different types of analyses. This format is a table similar to an Excel worksheet, but has more strict conventions:* Each **column** represents a **field**. It has a header or label and the values it holds all have the same **data type**.* Each **row** represents an **observation**. All values in a given row related in that they describe the same entity. * Rows are typically referenced by an **index**. Index values are often, but not always, non-repeating sequential integer values.  Typical operations in a dataframeOnce data are organized in a dataframe, it's quite straightforward to do the following:* Select/filter/sort data by row, by column, or by both. * Compute new fields from existing ones* Combine tables, either by appending columns or rows * Reshape tables either by melting, pivoting* Summarizing/Grouping data* Handling missing data * Plotting  dataWe'll introduce how a few of these operations are done using the Python `Pandas` here in this notebook. Specifically, we'll examine how to subset rows and columns can be selected from dataframes as this offers more insight on how dataframes are organized and manipulated in Python. The other tasks will be examined in subsquent notebooks.   Diving in...We'll begin by importing Pandas and then loading the water flow dataset retreived in the previous notebook.<jupyter_code>#import libraries
import pandas as pd
#Load data from the server into a dataframe named 'df'
url = 'http://waterservices.usgs.gov/nwis/dv/?format=rdb&sites=02087500&startDT=1930-10-01&endDT=2017-09-30&statCd=00003&parameterCd=00060&siteStatus=all'
df = pd.read_csv(url,
                 skiprows=30,
                 sep='\t',
                 names=['agency_cd','site_no','datetime','MeanFlow_cfs','Confidence'],
                 dtype={'site_no':'str'},
                 parse_dates=['datetime']
                )<jupyter_output><empty_output><jupyter_text>In the above statement, we instruct pandas to read delimited text data into a dataframe, using a number of parameters to guide the reading in of the data:* The first parameter is the location of the data.is used to specify the location of the data. If the data were saved locally as a text file, we could type in the path to the file (e.g. "c:/workspace/waterdata.csv"). However, as our data resides online, we can simply provide the web address, or URL, and Pandas is smart enough to fetch it there. * `skiprows=` instructs Pandas to skip the first 30 rows in the text file. These are metadata rows, so we can skip them.* `sep=` specifies that our records are separated by a tab, not a comma.* `names=` specifies the column names we want to assign to our output dataframe.* `dtype=` instructs Pandas to set records in the "site_no" column to be strings, not numbers* `parse_dates=` instructs Pandas to set records in the "datetime" column to be dates, not stringsThis import statement is actually more advanced than most. Often, the source CSV file is tidier and can be imported with fewer parameters. Full documentation of the `read_csv` command is found by [Googling "pandas read_csv"](https://www.google.com/search?q=pandas+read_csv).<jupyter_code>#Display each column's data type
df.dtypes<jupyter_output><empty_output><jupyter_text>Selecting dataSelecting data, aka "filtering", "subsetting", "slicing", etc., can be done by column, by row, or both. Identifying the rows or columns can be done by position, index/label, or by query, as we'll see in the following examples.   Selecting specific *columns* of dataIsolating a specific column of data is fairly straightforward; we just enter, in brackets, the name of the column.<jupyter_code>#Create a new array from just the one column
dfMeanFlow = df['MeanFlow_cfs']
#Show the first 5 rows of that array
dfMeanFlow.head()<jupyter_output><empty_output><jupyter_text>To isolate more than one column, we just pass a *list* of column names between the brackets.(*Recall that lists in Python are themselves surrounded by brackets `[]`.*)<jupyter_code>#Create a new dataframe view of just the Flow and Confidence columns
df2 = df[['MeanFlow_cfs','Confidence']]
df2.head()<jupyter_output><empty_output><jupyter_text>► Your turn. Extract just the `site_no` and `datetime` columns to the variable `d3` and display its first 5 records.<jupyter_code>#Create a new dataframe view of just the site_no and datetime columns
df3 = df[['site_no','datetime']]
df3.head()<jupyter_output><empty_output><jupyter_text>Selecting rows of data...There are a few means for selecting rows of data: by position, by index, or query, or by mask ... Here, we'll touch on each.  ♦ Selecting by position with `iloc`First, we can pull one or a **slice** of rows by the row's sequential position in the dataframe using the `iloc` command (short for **i**nteger **loc**ation).*Recall that in Python, list indices begin at zero, not one...* * Single values<jupyter_code>#Show first row of data
df.iloc[0]
#Show second row
df.iloc[1]
#Show last row
df.iloc[-1]<jupyter_output><empty_output><jupyter_text>► What would the command be to show the 100th row of data?<jupyter_code>#Show 100th row
df.iloc[99] #Recall that in Python list indices begin at zero<jupyter_output><empty_output><jupyter_text>* Data **slices**: A slice of data is a set of contiguous rows (or columns). We can slice our data with `iloc` by providing the bounds of the slice we want.*Note that the upper bound is not included in the slice*.<jupyter_code>#Show the first 4 rows. 
df.iloc[0:4]
#Show the first 4 rows (again): Note that if we omit the lower bound, it assumes it's zero
df.iloc[:4]
#Show rows 100 thru 105
df.iloc[99:104]<jupyter_output><empty_output><jupyter_text>► What would the command be to show the last 5 records?<jupyter_code>#Show the last 5 rows 
df.iloc[-5:]<jupyter_output><empty_output><jupyter_text>* Selecting rows *and columns* using `iloc`. Since tables are 2 dimensional, we can easily select/slice data by column or row AND column with `iloc`.<jupyter_code>#First, remind us what our columns are
df.columns
#Select the flow data (4th column) for the 100th record
df.iloc[99,3]
#Select the flow data and confidence values for the 100th to 110th records
df.iloc[99:110,3:]<jupyter_output><empty_output><jupyter_text>--- ♦ Selecting by index with `loc`While `iloc` references rows by their actual position in the data frame, `loc` references them by their **index**. Let's first examine this using the auto-generated indices created when we imported the CSV into a dataframe. Running the `index` function reveals that our initial index was assigned a sequential range of integers.<jupyter_code>#What does our index look like? 
df.index
#Show the rows corresponding to index values 6 thru 10
df.loc[6:10]<jupyter_output><empty_output><jupyter_text>Now, let's change our index from the autogenerated sequential values to the values in stored in the `datetime` column.<jupyter_code>#Change the index to be values in the datetime column and display them
df.set_index('datetime',inplace=True)
df.index
#Show the row with the index matching Jan 1st, 1975
df.loc['1975-01-01']
#Show the slice of rows spanning september 10th thru 15th, 1998
df.loc['1998-09-10':'1998-09-15']
#Return select rows AND columns using loc
df.loc['1998-09-10':'1998-09-15','MeanFlow_cfs':'Confidence']<jupyter_output><empty_output><jupyter_text>► Use `loc` to return `MeanFlow_cfs` data for Sept 1, 2017<jupyter_code>df.loc['2017-09-01','MeanFlow_cfs']<jupyter_output><empty_output><jupyter_text>► Use `loc` to return `MeanFlow_cfs` data Sept, 2017 onward to the end of the dataset<jupyter_code>df.loc['2017-09-01':,'MeanFlow_cfs']<jupyter_output><empty_output><jupyter_text>♦ Selecting by querying dataMoving away from indices, we can query records matching criteria that we specify.<jupyter_code>#Select rows where the Mean flow was less than 50 cfs
df.query('MeanFlow_cfs < 50')
#Select rows where the Confidence indicates estimated:
df.query('Confidence == "A:e"')<jupyter_output><empty_output><jupyter_text>► Query the data for mean flow values equal to 55 cms<jupyter_code>df.query('MeanFlow_cfs == 55')<jupyter_output><empty_output><jupyter_text>♦ Using *masks* to query dataThis method is a bit more convoluted. First we create a **mask** which is a binary column of data, meaning values are either true or false, by supplying a criteria. And then we **apply the mask**, which returns only those records that are true.<jupyter_code>#Create a mask of flows below 53 cfs
maskTinyFlow = df['MeanFlow_cfs'] < 53
#Apply the mask; this will only return rows where the mask was true
df[maskTinyFlow]<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.08899011220975447, 'fraction_numerical': 0.0212198644595045, 'mean_word_length': 5.299510146955913, 'pattern_counts': {'":': 0, '<': 71, '<?xml version=': 0, '>': 69, 'https://': 1, 'lorem ipsum': 0, 'www.': 1, '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': '17', 'dir': 'jupyter-structured-clean-dedup', 'id': '448113', 'license': 'MIT', 'n_tokens_mistral': 2803, 'n_tokens_neox': 2589, 'n_words': 1162, 'path': 'media/02b-DataFrames_KEY.ipynb', 'repo_name': 'DataDevils/WaterFlow-Python', 'size': '9001'}