<jupyter_start><jupyter_text>Intro to Pandas Today we're going to be starting our journey through the Pandas library (created by <NAME>). The name is derived from the term "panel data", an econometrics term for data sets that include observations over multiple time periods for the same individuals.When we refer to working in Pandas, we're typically talking about working within a **DataFrame**, which is what we'll focus on today.  Learning ObjectivesAt the end of this notebook you will be able to:- explain why Pandas is important to Data Scientists- create Pandas Dataframes from lists or a list of dictionaries- load data from csv-files to DataFrames- access data stored in DataFrames- use the common features of a DataFrameWe'll be diving into Pandas DataFrames, which are objects that will hold our data, allowing us to interact with it, manipulate it, and eventually throw it into machine learning algorithms (if we want). Since a Pandas DataFrame is an **object**, this means that we're going to interact with it in much the same way that we interact with all of our other objects in python. Before we get to actually interacting with DataFrames, though, we'll have to get one, and get one with data in it! There's one quick step that we have to do before that...  Pandas Import ```pythonimport pandas as pd  Standard import. ```Here I've shown how we get access to everything in the Pandas library - we import it! Also note the python comment `" Standard import"` out to the right of our import. This was to note that this is the standard way to import the Pandas library. We should always be sure that if we are importing the entire pandas library, we follow this syntax. It's common practice to use `pd` as the alias, and we tend to follow common practice whenever possible. This makes it easier for others to read our code.  Getting a DataFrame ObjectThere are two basic ways that we can get a Pandas DataFrame object to work with. The first is by using data that is already in our Python program, in conjuction with the `DataFrame` constructor. The second is by reading in external data through the pandas module (which we've imported and made accessible via `pd`). For reference, here are the [docs](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.html) for Pandas DataFrames.  Using data already in our Python programIf we are using data that is already in our Python program, then we are going to be passing that data to the `DataFrame` constructor. We typically do this in one of two ways. The first involves passing in a list of dictionaries, whereas the second involves passing in two lists. Let's dive into the first...<jupyter_code>import pandas as pd # We haven't actually done this in code yet. 
data_lst = [{'a': 1, 'b': 2, 'c':3}, {'a': 4, 'b':5, 'c':6, 'd':7}]
df = pd.DataFrame(data_lst) 
df<jupyter_output><empty_output><jupyter_text>DataFrames consist of **rows** and **columns**, where the columns will be denoted and accessed via their names, and the rows will be denoted and access via the indices of the DataFrame. Above, we can look at our columns and see that their names are `a`, `b`, `c`, and `d`. We can similiary look at our rows and see that they are indexed by `0` and `1`. These column names and indices are how we will access this data later. How did the `DataFrame` constructor take our list of dictionaries and put it into the DataFrame in that format, though?When the Pandas DataFrame constructor encounters a list of dictionaries like we gave it, it interprets each dictionary to be a row in the DataFrame. The keys are read as the column names and the values as the values for each column. By default, the DataFrame constructor will assign a column for **every** key that it sees in **any** dictionary in the list of dictionaries. If a particular dictionary in that list doesn't have a value for that key, then it assigns a `NaN` (stands for "not a number") value for that index-column pair. Therefore, when the Pandas DataFrame above got the list of dictionaries, it saw `a`, `b`, `c`, and `d` keys, and thus created those columns. It then filled in the values associated with those keys, filling in a `NaN` if it didn't find that key (like it didn't find `d` in the first dictionary in our list).<jupyter_code>data_lst = [{'a': 1}, {'b':5}, {'c': 4}]
df = pd.DataFrame(data_lst)<jupyter_output><empty_output><jupyter_text>**What do you expect our DataFrame to hold now?**<jupyter_code>df<jupyter_output><empty_output><jupyter_text>The second way of creating a dataframe from data that is already in our Python program is to pass in a list of lists as the `data` argument, and a list of strings as the `columns` argument. The `pd.DataFrame()` constructor will assume that each individual list in the `data` argument is one row (i.e. if you pass in a list of 5 lists, your dataframe will have 5 rows). Below, we're passing in a list of 2 lists to the `data` parameter, which means that our DataFrame will have two rows.<jupyter_code>data_vals = [[1, 2, 3], [4, 5, 6]]
data_cols = ['a', 'b', 'c']
df = pd.DataFrame(data=data_vals, columns=data_cols)
df<jupyter_output><empty_output><jupyter_text>It's important to note that this method is not quite as flexible as using a list of dictionaries. When passing in a list of lists via the `data` argument, we have to make sure that the greatest number of elements in any single list corresponds to the number of column names we are passing in via the `columns` argument (no more, no less). For example:<jupyter_code>data_vals = [[1, 2], [4, 5, 6]]
data_cols = ['a', 'b']
df = pd.DataFrame(data=data_vals, columns=data_cols)<jupyter_output><empty_output><jupyter_text>We do, however, have the flexibility of passing in some rows with 'missing' data. The key to note here, though, is that the last column name will become what is filled with a `NaN` if a list is missing a column value (it will not be based off a key name like in our list of dictionaries).<jupyter_code>data_vals = [[1, 2], [4, 5, 6]]
data_cols = ['a', 'b', 'c']
df = pd.DataFrame(data=data_vals, columns=data_cols)
df<jupyter_output><empty_output><jupyter_text>Reading External DataThere are many ways that we can read external data into a Pandas DataFrame, and they will be called as a function that is available via the `pandas` module. As a result of importing the `pandas` module and making it accessible via `pd`, this means that we will call all of these functions via `pd`. Each one of these functions will **return** back to us a Pandas DataFrame object, populated with the external data that we read in. The [Pandas documentation](https://pandas.pydata.org/pandas-docs/stable/user_guide/io.html) will show you all of the ways that you could load external data into a DataFrame. Basically, there is a way to load in data stored in any format (CSV, JSON, SQL, Excel, HTML). All of these take some form of a `read_{data_type}` function, which means that we will call them as `pd.read_{data_type}`.So, if we wanted to load data in from a CSV, we would simply use:```pythondf = pd.read_csv('my_data.csv')```Note: This assumes that we have the column names in the first row of your .csv.If we don't have the column names in the first row of our .csv, we could read in the .csv with the following:```pythondf = pd.read_csv('my_data.csv', header=None)```Note: This by default assigns numbers as column names (starting with 0).If we wanted to assign the column names as we read it in, we can pass in an additional `names` argument, where this `names` argument holds a list of the names we want to assign to the columns.```pythondf = pd.read_csv('my_data.csv', header=None, names=['col1', 'col2', ...., 'col12'])```  Looking at Our DataOur data is available in the `data` folder inside this repo. It's called `abalone.csv`. We will first read in the data, and then discuss what it's all about.<jupyter_code>df = pd.read_csv("data/abalone.csv")<jupyter_output><empty_output><jupyter_text>Given that our `DataFrame` is an object, we can imagine that it will have associated attributes and methods. There are a couple of each available to us to get a general sense of our data. We have two attributes that we will frequently use on our DataFrame - these will allow us to look at the shape of our data and the column names. We have four methods that are availiable on our dataframe for getting a general sense of our data: `info()`, `describe()`, `head()`, and `tail()`. Let's take a look at what these do.<jupyter_code>df.head()<jupyter_output><empty_output><jupyter_text>Which this data we are taking a trip under the sea...The data is all about abalones, which are very large sea snails.The data is direved from UCI Machine Learning Repository for open data sets. You can find [more informations](https://archive.ics.uci.edu/ml/datasets/Abalone) at the UCI page.The original goal is the find out the age of those sea snails through other easily obtainable physical measurements, without having to cutting them off and count the number of rings, which is pretty time-consuming.Below you see a table containing all attributes of the snais that have been examined. You found those in as column names in our newly created DataFrame as well.**Column Description:**Name |  Data Type | Measurement Unit | Description:-----|--------|--------|:--------Sex | nominal | -- | M, F, and I (infant)Length | continuous | mm | Longest shell measurementDiameter | continuous | mm | perpendicular to lengthHeight | continuous | mm | with meat in shellWhole weight | continuous | grams | whole abaloneShucked weight | continuous | grams | weight of meatViscera weight | continuous | grams | gut weight (after bleeding)Shell weight | continuous | grams | after being driedRings | integer | -- | +1.5 gives the age in yearsWe already saw the first rows of our DataFrame, when we executed the command `df.head()`. By default this command shows us 5 rows, but we can alter it to any number of rows we want to see:<jupyter_code>df.head(8) # or n=8<jupyter_output><empty_output><jupyter_text>Also, we can check out the last rows in our DataFrame:<jupyter_code>df.tail()<jupyter_output><empty_output><jupyter_text>Two attributes of our DataFrame are highly useful for us to look at:`shape`and `columns`.The `shape` will return us the number of rows and columns of our DataFrame<jupyter_code>df.shape
# gives us back a list of all of the column names
df.columns<jupyter_output><empty_output><jupyter_text>Now we know that we have 4177 individual observations (in this case individual sea snails). In terms of machine learning, we have 8 different features to predict the desired response (output), which in this case are the `n_rings` used to evaluate the age of the sea snails.In the column description above, the data types of each column is stated. To check whether the data was loaded correctly and the right data types were detected, we can have a look which data types were connected to the columns:<jupyter_code>df.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 4177 entries, 0 to 4176
Data columns (total 9 columns):
 #   Column          Non-Null Count  Dtype  
---  ------          --------------  -----  
 0   sex             4177 non-null   object 
 1   length          4177 non-null   float64
 2   diameter        4177 non-null   float64
 3   height          4177 non-null   float64
 4   weight_whole    4177 non-null   float64
 5   weight_shucked  4177 non-null   float64
 6   viscera         4177 non-null   float64
 7   shell           4177 non-null   float64
 8   n_rings         4177 non-null   int64  
dtypes: float64(7), int64(1), object(1)
memory usage: 293.8+ KB<jupyter_text>We see that the data was loaded in correctly. To get a feeling of the data, it is highly recomendet to get some statistics (better even, a summary of them). This helps us to evaulate the spread and central tendency of out data.Of course also for this task, there is a pretty neat function already implemented in pandas:<jupyter_code>df.describe()<jupyter_output><empty_output><jupyter_text>You see, that in this summary, the column `sex`is missing. `.describe()`will only show us numeric columns.The first row shows us how many observations are available for each feature. The same information was also stored at `.info()`. Luckily in our case, we have no missing data, as the count equals the the  of rows in our DataFrame. This would be different if we would have any `nan` in our DataFrame.The other values: Mean, Standard deviation, min, max and the quartiles should be familiar to all of you already.  Grabbing your data The BasicsWe now know how to look at our data. What if we wanted to grab certain parts to look at, or certain parts to play around with/transform? Say we wanted to grab an entire row, or an entire column... how do we do that? Let's dive in by starting off with some indexing. The format we use to index into our dataframe and grab data will depend on exactly what subset of the data that we want to grab. If we want to grab entire rows or columns, then we can use bracket notation to do that (just like we use bracket notation to index into lists). If we want an entire column, then we're going to place the **column name** in brackets (and multiple column names in a list inside those brackets). We can also sometimes access a column via dot notation on the dataframe, which we'll show in a second. If we want an entire row, then we have to place **both** a **beginning and ending index** inside the brackets (it won't work to just place a single index in the brackets).<jupyter_code># Grabs the "sex" column: bracket notation
df['sex']
# Grabs the "sex" column: dot notation
df.sex<jupyter_output><empty_output><jupyter_text>>Note: The dot notation won't work if the column names contain spaces! That's why it is convenient to check first, if column names contain them and change them into "_" (underscored).Luckyly, the columns in our DataFrame have already underscores.If not, here is a short code snippet, which you could use to change the column names:<jupyter_code># make a copy of your dataframe
df2 = df.copy()
# make a list column names
cols = df2.columns.tolist()
# replace space with _
cols = [col.replace(' ', '_') for col in cols]
# reassign new column names to dataframe
df2.columns = cols
df2<jupyter_output><empty_output><jupyter_text>We can also access multiple columns by passing in a list of column names<jupyter_code>df[['sex', 'length']]
df[df["sex"] == "M"]<jupyter_output><empty_output><jupyter_text>Indexing rows:<jupyter_code># This will grab from the beginning up to but not including the row at index 3. 
df[:3]
# This will not work because we didn't give it a starting **and** ending index.
df[0]
# This won't work because we are trying to access a subset of rows 
# **and** columns at the same time. 
df[:1, 'volatile acidity']<jupyter_output><empty_output><jupyter_text>What if we want to grab certain rows **and** certain columns, rather than just entire rows or entire columns?If we want to grab only certain rows and columns, there two **methods** that we can use to index into a Pandas DataFrame: `loc[]` and `iloc[]`. Note that these are **methods**, which means that we will call them via dot notation on our `DataFrame` object. The difference between these three has to do with how we use them. `loc[]` is a purely label-location based indexer, `iloc[]` is a purely integer-location based indexer.<jupyter_code># Loc is label based. All of these will work, because they are recognized as labels on the 
# rows (index labels) or columns (column name labels). 
df.loc[0, 'sex'] # 0 is one of the index labels, and 'fixed acidity' is a column label.
# Ranges on our index labels still work (as long as they're numeric).
df.loc[0:10, 'sex']
df.loc[10:15, ['sex', 'length']]
# These will all fail, because they attempt to access the columns by position integers, 
# and loc only takes labels. 
df.loc[0, 0]
df.loc[0:10, 0]
df.loc[10:15, [0, 4]]
# The above will all work with .iloc, though, since it takes integers (and not labels)
df.iloc[0, 0]
df.iloc[0:10, 0]
df.iloc[10:15, [0, 4]]
# Using labels, though, like we did with .loc, will NOT work.
df.iloc[0, 'sex']<jupyter_output><empty_output><jupyter_text>A little bit moreWe know how to grab certain rows or columns from dataframes, as well as a subet of rows and columns, and anything in between. But, this looks like it typically requires that the exact location of the data we want is known. What if we don't know that location? Is there a way to grab desired data by simply specifying some query parameters? Yes! There are a couple of ways that we can do this. The first way we'll look at is just through masking, whereas the second actually uses the `query()` method availiable on the Pandas DataFrame.<jupyter_code># This just gives us a mask - tells us True or False whether each row fit's the condition.
df['length'] <= 0.4
# To use a mask, we actually have to use it to index into the DataFrame (using square brackets). 
df[df['length'] <= 0.4]<jupyter_output><empty_output><jupyter_text>Notice how only the indices that were found to be True from the condition show up in this subset of the dataframe. We've "masked" off the rest of the indices that we're found to be False (hence the name **masking**).<jupyter_code># Okay, this is cool. What if I wanted a slightly more complicated query...
df[(df['length'] <= 0.4) & (df['sex'] == 'F')]
# So I could write an arbitrarily complicated query using that syntax... 
df[(df['length'] <= 0.4) & (df['length'] > 0.35) & (df['weight_whole'] > 0.25) & (df['weight_whole'] < 0.3)]
# Or I could use the query() method that is available on our dataframe object. 
df.query('length <= 0.4 and length > 0.35 and weight_whole > 0.25 and weight_whole < 0.3')<jupyter_output><empty_output><jupyter_text>In general, it's preferred to use the `query()` method, since it improves readability. It doesn't use loads of sets of brackets (`[]`) and parentheses (`()`), but rather just one set of parentheses. It also tends to follow the Python syntax a little more closely than the mask methods that we looked at above, using `and` instead of `&` to separate different specifications on our queries.  We're going to look at a couple of more things you can do with DataFrames, but to view all available attributes and methods of DataFrames, we can check out the [Pandas Docs](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.html). For practical examples of how DataFrames are used you can get a copy of [Python for Data Analysis](http://shop.oreilly.com/product/0636920023784.do) (it's written by <NAME>, the creator of Pandas).For those coming from R, know that the Pandas DataFrame was based off the R DataFrame, and most anything we can do with an R DataFrame, we can do with a Pandas DataFrame. For anybody coming from a SQL background, the methods available via DataFrame's give us much, if not all, of the functionality that we have available in your SQL environment.  GroupbyLet's start with `groupby`s...By “group by” we are referring to a process involving one or more of 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 structure[source](https://pandas.pydata.org/pandas-docs/stable/user_guide/groupby.html)<jupyter_code># first we try to groupby the sex, let's check how many different values this feature can have 
df['sex'].unique()
# we can also see the count of different values with this command
df['sex'].nunique()
df.groupby('sex') # Note that this returns back to us a groupby object. It doesn't actually 
                      # return to us anything useful until we perform some aggregation on it. 
# We have tons of aggregation metrics we can get from a groupby object. Note here that we 
# store the results of a groupby below to then perform all kinds of operations on it (this is 
# actually the preferred method if we're going to perform more than one calculation
# on it). We have tons of operations we can perform on it. 
groupby_obj = df.groupby('sex')
# 
groupby_obj.mean()
groupby_obj.max()

groupby_obj.count()
# The previous aggregation metrics gave us back a DataFrame with all of the columns minus what 
# we grouped on. Notice that what we grouped on becomes the index. What if I wanted only one 
# column back (especially with something like count, where it is the same
# for every column)? Well, we can do anything with this DataFrame that we did before...
df.groupby('sex').count()['length']
# Note we can also group by multilple columns by passing them in in a list. It will group by 
# the first column passed in first, and then the second after that (i.e. it will group by 
# the second within the group by of the first). 
df.groupby(['sex', 'n_rings']).count()['weight_whole']<jupyter_output><empty_output><jupyter_text>Check out the [Group By documentation](https://pandas.pydata.org/pandas-docs/stable/user_guide/groupby.html) to look at what all you can do with the Pandas `.groupby()`. Sorting Sorting is going to work much the same way as group bying. It is going to be available via a method that we call on the dataframe, `.sort_values()`, and we are going to pass it a column or columns to sort by.<jupyter_code>df.sort_values('length').head(15) # Note: this is ascending by default.
# df.sort_values('length', ascending=False) # here we change ascending to false.
# Note that we can sort by multiple columns by placing them in a list inside of the sort()
# method. It will sort by the first column passed in first, and then the second within the 
# sort of the first. 
df.sort_values(['length', 'diameter'], ascending=False).head(15) # ascending=False will apply to both columns.<jupyter_output><empty_output><jupyter_text>Creating and Dropping ColumnsCreating columns is done in one of two ways: 1. Using bracket notation2. Using the `eval()` method on the Pandas DataFrame. Dropping columns is done using the `df.drop()` method on the Pandas DataFrame. When dropping columns, we have to be careful to make sure to tell the DataFrame to drop them in place, or assign the DataFrame with dropped columns to a new variable. You also need to make sure to tell the `drop()` method what axis the thing you're trying to drop is on (rows are `axis=0`, and columns are `axis=1`).![](https://i.stack.imgur.com/DL0iQ.jpg)<jupyter_code># we can rename columns with `.rename()``
df.rename(columns={'n_rings': 'nr_rings'}, inplace=True)
df.head()
# with `.eval()` we can create new columns
# remember that in the description it is stated that the age of the snails can be calculated by adding 1.5 to the nr. of rings
df.eval('age = nr_rings + 1.5', inplace = True)
df.columns
df.head()
df.eval('weight_per_hight = weight_whole / diameter', inplace = True)
df.columns
df.head()<jupyter_output><empty_output><jupyter_text>Sometimes it is also necessary to delete columns, e.g. if they do not contain relevant data, or if engineered features contain the same information.<jupyter_code># the age is our newly created feature, therefore nr_rings are not containg any further information
# with axis=1 we define that we are droping a column, not a row
# by default it would be axis=0
df.drop('nr_rings', axis=1)
df.columns # Wait, the nr_rings column is still there... why? 
           # It's because we didn't tell it drop inplace. 
df.drop('nr_rings', inplace=True, axis=1) 
df.columns<jupyter_output><empty_output><jupyter_text>Dealing with NullsPandas has functions for both filling nulls (or N/As) with whatever value we want, or dropping nulls all together. To fill nulls, we use the `.fillna()` method on the DataFrame, and to drop nulls, we call the `.dropna()` method on the DataFrame. In terms of the `.fillna()` function, we can give it a default value to fill in, or a number of other methods to fill it in (padding, back filling, foward filling). You can read about dealing with missing data in the docs [here](https://pandas.pydata.org/pandas-docs/stable/user_guide/missing_data.html). We're not going to go into too much depth here, but want you to know that this functionality exists. As we already checked, in this dataset, there are not even missing data. So the next line of code will not do anything in this case. Just so you know how to fill nans or drop them with pandas.<jupyter_code># Notice the addition of the inplace argument here. 
# if you try to fill nans will some value: the first argument is the inserted value
df.fillna(-1, inplace=True)
# dropna() will drop the whole row, if nans are present
df.dropna(inplace=True)<jupyter_output><empty_output><jupyter_text>A quick aside on Pandas SeriesYou might have noticed that in a couple of places, when we asked for certain rows/columns of the data, we got back a 1-D array that had an index attached. These are examples of what Pandas calls `Series`. In the documentation for [Pandas Series](http://pandas.pydata.org/pandas-docs/version/0.15.2/dsintro.htmlseries), you can get an idea of what they can do. For the most part, we can kind of treat them like a mini DataFrame, as they have a lot of the same methods. However, there are some slight differences. Since we work with DataFrame's the majority of the time, we're not going to go into any real depth on Series. Here are some examples of things that returned series:<jupyter_code>df['length'] <= 0.3
type(df['length'] <= 0.3)
df.groupby('sex').count()['age']<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.08043231389748208, 'fraction_numerical': 0.009241492736030074, 'mean_word_length': 4.486143931256713, 'pattern_counts': {'":': 0, '<': 116, '<?xml version=': 0, '>': 112, 'https://': 8, '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': '26', 'dir': 'jupyter-structured-clean-dedup', 'id': '236053', 'license': 'MIT', 'n_tokens_mistral': 7324, 'n_tokens_neox': 6985, 'n_words': 3800, 'path': '01_pandas.ipynb', 'repo_name': 'Jaoaud/Numpy_Pandas', 'size': '25549'}