ISA 419: Data-Driven Security

class: center, middle, inverse, title-slide

.title[
# ISA 419: Data-Driven Security
]
.subtitle[
## 05: Cleaning and Combining Data in Pandas
]
.author[
### <br>Fadel M. Megahed, PhD <br><br> Professor <br> Farmer School of Business<br> Miami University<br><br> <a href="https://twitter.com/FadelMegahed"><svg viewBox="0 0 512 512" style="height:1em;position:relative;display:inline-block;top:.1em;fill:white;" xmlns="http://www.w3.org/2000/svg"> <path d="M459.37 151.716c.325 4.548.325 9.097.325 13.645 0 138.72-105.583 298.558-298.558 298.558-59.452 0-114.68-17.219-161.137-47.106 8.447.974 16.568 1.299 25.34 1.299 49.055 0 94.213-16.568 130.274-44.832-46.132-.975-84.792-31.188-98.112-72.772 6.498.974 12.995 1.624 19.818 1.624 9.421 0 18.843-1.3 27.614-3.573-48.081-9.747-84.143-51.98-84.143-102.985v-1.299c13.969 7.797 30.214 12.67 47.431 13.319-28.264-18.843-46.781-51.005-46.781-87.391 0-19.492 5.197-37.36 14.294-52.954 51.655 63.675 129.3 105.258 216.365 109.807-1.624-7.797-2.599-15.918-2.599-24.04 0-57.828 46.782-104.934 104.934-104.934 30.213 0 57.502 12.67 76.67 33.137 23.715-4.548 46.456-13.32 66.599-25.34-7.798 24.366-24.366 44.833-46.132 57.827 21.117-2.273 41.584-8.122 60.426-16.243-14.292 20.791-32.161 39.308-52.628 54.253z" /></svg> <span class="citation">@FadelMegahed</span></a> <br> <a href="https://github.com/fmegahed/"><svg viewBox="0 0 496 512" style="height:1em;position:relative;display:inline-block;top:.1em;fill:white;" xmlns="http://www.w3.org/2000/svg"> <path d="M165.9 397.4c0 2-2.3 3.6-5.2 3.6-3.3.3-5.6-1.3-5.6-3.6 0-2 2.3-3.6 5.2-3.6 3-.3 5.6 1.3 5.6 3.6zm-31.1-4.5c-.7 2 1.3 4.3 4.3 4.9 2.6 1 5.6 0 6.2-2s-1.3-4.3-4.3-5.2c-2.6-.7-5.5.3-6.2 2.3zm44.2-1.7c-2.9.7-4.9 2.6-4.6 4.9.3 2 2.9 3.3 5.9 2.6 2.9-.7 4.9-2.6 4.6-4.6-.3-1.9-3-3.2-5.9-2.9zM244.8 8C106.1 8 0 113.3 0 252c0 110.9 69.8 205.8 169.5 239.2 12.8 2.3 17.3-5.6 17.3-12.1 0-6.2-.3-40.4-.3-61.4 0 0-70 15-84.7-29.8 0 0-11.4-29.1-27.8-36.6 0 0-22.9-15.7 1.6-15.4 0 0 24.9 2 38.6 25.8 21.9 38.6 58.6 27.5 72.9 20.9 2.3-16 8.8-27.1 16-33.7-55.9-6.2-112.3-14.3-112.3-110.5 0-27.5 7.6-41.3 23.6-58.9-2.6-6.5-11.1-33.3 2.6-67.9 20.9-6.5 69 27 69 27 20-5.6 41.5-8.5 62.8-8.5s42.8 2.9 62.8 8.5c0 0 48.1-33.6 69-27 13.7 34.7 5.2 61.4 2.6 67.9 16 17.7 25.8 31.5 25.8 58.9 0 96.5-58.9 104.2-114.8 110.5 9.2 7.9 17 22.9 17 46.4 0 33.7-.3 75.4-.3 83.6 0 6.5 4.6 14.4 17.3 12.1C428.2 457.8 496 362.9 496 252 496 113.3 383.5 8 244.8 8zM97.2 352.9c-1.3 1-1 3.3.7 5.2 1.6 1.6 3.9 2.3 5.2 1 1.3-1 1-3.3-.7-5.2-1.6-1.6-3.9-2.3-5.2-1zm-10.8-8.1c-.7 1.3.3 2.9 2.3 3.9 1.6 1 3.6.7 4.3-.7.7-1.3-.3-2.9-2.3-3.9-2-.6-3.6-.3-4.3.7zm32.4 35.6c-1.6 1.3-1 4.3 1.3 6.2 2.3 2.3 5.2 2.6 6.5 1 1.3-1.3.7-4.3-1.3-6.2-2.2-2.3-5.2-2.6-6.5-1zm-11.4-14.7c-1.6 1-1.6 3.6 0 5.9 1.6 2.3 4.3 3.3 5.6 2.3 1.6-1.3 1.6-3.9 0-6.2-1.4-2.3-4-3.3-5.6-2z" /></svg> fmegahed</a> <br> <a href="mailto:fmegahed@miamioh.edu"><svg viewBox="0 0 512 512" style="height:1em;position:relative;display:inline-block;top:.1em;fill:white;" xmlns="http://www.w3.org/2000/svg"> <path d="M476 3.2L12.5 270.6c-18.1 10.4-15.8 35.6 2.2 43.2L121 358.4l287.3-253.2c5.5-4.9 13.3 2.6 8.6 8.3L176 407v80.5c0 23.6 28.5 32.9 42.5 15.8L282 426l124.6 52.2c14.2 6 30.4-2.9 33-18.2l72-432C515 7.8 493.3-6.8 476 3.2z" /></svg> fmegahed@miamioh.edu</a><br> <a href="https://calendly.com/fmegahed"><svg viewBox="0 0 384 512" style="height:1em;position:relative;display:inline-block;top:.1em;fill:white;" xmlns="http://www.w3.org/2000/svg"> <path d="M202.021 0C122.202 0 70.503 32.703 29.914 91.026c-7.363 10.58-5.093 25.086 5.178 32.874l43.138 32.709c10.373 7.865 25.132 6.026 33.253-4.148 25.049-31.381 43.63-49.449 82.757-49.449 30.764 0 68.816 19.799 68.816 49.631 0 22.552-18.617 34.134-48.993 51.164-35.423 19.86-82.299 44.576-82.299 106.405V320c0 13.255 10.745 24 24 24h72.471c13.255 0 24-10.745 24-24v-5.773c0-42.86 125.268-44.645 125.268-160.627C377.504 66.256 286.902 0 202.021 0zM192 373.459c-38.196 0-69.271 31.075-69.271 69.271 0 38.195 31.075 69.27 69.271 69.27s69.271-31.075 69.271-69.271-31.075-69.27-69.271-69.27z" /></svg> Automated Scheduler for Office Hours</a><br><br>
]
.date[
### Spring 2025
]

---

## Quick Refresher of Last Class

✅ Describe and create data frames

✅ Import data using pandas

✅ Utilize the `head()`, `tail()`, `shape()`, `info()`, `dtypes()`, `count()`, `value_count()`, `describe()`, `isnull().sum()` functions to quickly explore and summarize your dataset

✅ Use the `loc[]` and `iloc[]` to select rows and columns

✅ Use `assign()` to create new columns on your existing data frame.

---

## Learning Objectives for Today's Class

- Ensure that your imported data is **technically correct** (rename columns and fix `dtypes`)

- Clean data to ensure that your data is consistent

- Understand the difference between `concatenate`, `merge`, and `join`.

---
class: inverse, center, middle

# Technically Correct Data

---

## The Data Analysis Value Chain

.footnote[
<html>
<hr>
</html>
**Source:** [De Jonge and Van Der Loo (2013), An introduction to data cleaning with R](https://cran.r-project.org/doc/contrib/de_Jonge+van_der_Loo-Introduction_to_data_cleaning_with_R.pdf). Please click on the link and read p. 7-8.
]

---

## Technically Correct Data

> Raw data files may lack headers, contain wrong data types (e.g. numbers stored as strings), wrong category labels, unknown or unexpected character encoding and so on.

> **Technically correct data** is the state in which data can be read into an R data.frame, with correct **name**, **types** and **labels**, without further trouble. However, that **does not mean that the values are error-free or complete**. --- [De Jonge and Van Der Loo (2013)](https://cran.r-project.org/doc/contrib/de_Jonge+van_der_Loo-Introduction_to_data_cleaning_with_R.pdf)

---

## Functions for Cleaning/Renaming Variables

<div id="mgytmksalo" style="padding-left:0px;padding-right:0px;padding-top:10px;padding-bottom:10px;overflow-x:auto;overflow-y:auto;width:auto;height:auto;">
  
  <table class="gt_table" data-quarto-disable-processing="false" data-quarto-bootstrap="false" style="-webkit-font-smoothing: antialiased; -moz-osx-font-smoothing: grayscale; font-family: system-ui, 'Segoe UI', Roboto, Helvetica, Arial, sans-serif, 'Apple Color Emoji', 'Segoe UI Emoji', 'Segoe UI Symbol', 'Noto Color Emoji'; display: table; border-collapse: collapse; line-height: normal; margin-left: auto; margin-right: auto; color: #333333; font-size: 16px; font-weight: normal; font-style: normal; background-color: #FFFFFF; width: auto; border-top-style: solid; border-top-width: 2px; border-top-color: #A8A8A8; border-right-style: none; border-right-width: 2px; border-right-color: #D3D3D3; border-bottom-style: solid; border-bottom-width: 2px; border-bottom-color: #A8A8A8; border-left-style: none; border-left-width: 2px; border-left-color: #D3D3D3;" bgcolor="#FFFFFF">
  <thead style="border-style: none;">
    <tr class="gt_col_headings" style="border-style: none; border-top-style: solid; border-top-width: 2px; border-top-color: #D3D3D3; border-bottom-style: solid; border-bottom-width: 2px; border-bottom-color: #D3D3D3; border-left-style: none; border-left-width: 1px; border-left-color: #D3D3D3; border-right-style: none; border-right-width: 1px; border-right-color: #D3D3D3;">
      <th class="gt_col_heading gt_columns_bottom_border gt_left" rowspan="1" colspan="1" scope="col" id="type" style="border-style: none; color: #333333; background-color: #FFFFFF; font-size: 100%; font-weight: normal; text-transform: inherit; border-left-style: none; border-left-width: 1px; border-left-color: #D3D3D3; border-right-style: none; border-right-width: 1px; border-right-color: #D3D3D3; vertical-align: bottom; padding-top: 5px; padding-bottom: 6px; padding-left: 5px; padding-right: 5px; overflow-x: hidden; text-align: left;" bgcolor="#FFFFFF" valign="bottom" align="left"><span class="gt_from_md"><code style="margin-top: 0; margin-bottom: 0;">type</code></span></th>
      <th class="gt_col_heading gt_columns_bottom_border gt_left" rowspan="1" colspan="1" scope="col" id="package_name" style="border-style: none; color: #333333; background-color: #FFFFFF; font-size: 100%; font-weight: normal; text-transform: inherit; border-left-style: none; border-left-width: 1px; border-left-color: #D3D3D3; border-right-style: none; border-right-width: 1px; border-right-color: #D3D3D3; vertical-align: bottom; padding-top: 5px; padding-bottom: 6px; padding-left: 5px; padding-right: 5px; overflow-x: hidden; text-align: left;" bgcolor="#FFFFFF" valign="bottom" align="left"><span class="gt_from_md"><code style="margin-top: 0; margin-bottom: 0;">package</code></span></th>
      <th class="gt_col_heading gt_columns_bottom_border gt_left" rowspan="1" colspan="1" scope="col" id="fun()" style="border-style: none; color: #333333; background-color: #FFFFFF; font-size: 100%; font-weight: normal; text-transform: inherit; border-left-style: none; border-left-width: 1px; border-left-color: #D3D3D3; border-right-style: none; border-right-width: 1px; border-right-color: #D3D3D3; vertical-align: bottom; padding-top: 5px; padding-bottom: 6px; padding-left: 5px; padding-right: 5px; overflow-x: hidden; text-align: left;" bgcolor="#FFFFFF" valign="bottom" align="left"><span class="gt_from_md"><code style="margin-top: 0; margin-bottom: 0;">function()</code></span></th>
      <th class="gt_col_heading gt_columns_bottom_border gt_left" rowspan="1" colspan="1" scope="col" id="description" style="border-style: none; color: #333333; background-color: #FFFFFF; font-size: 100%; font-weight: normal; text-transform: inherit; border-left-style: none; border-left-width: 1px; border-left-color: #D3D3D3; border-right-style: none; border-right-width: 1px; border-right-color: #D3D3D3; vertical-align: bottom; padding-top: 5px; padding-bottom: 6px; padding-left: 5px; padding-right: 5px; overflow-x: hidden; text-align: left;" bgcolor="#FFFFFF" valign="bottom" align="left"><span class="gt_from_md"><code style="margin-top: 0; margin-bottom: 0;">description</code></span></th>
    </tr>
  </thead>
  <tbody class="gt_table_body" style="border-style: none; border-top-style: solid; border-top-width: 2px; border-top-color: #D3D3D3; border-bottom-style: solid; border-bottom-width: 2px; border-bottom-color: #D3D3D3;">
    <tr style="border-style: none;"><td headers="type" class="gt_row gt_left" style="border-style: none; padding-top: 8px; padding-bottom: 8px; padding-left: 5px; padding-right: 5px; margin: 10px; border-top-style: solid; border-top-width: 1px; border-top-color: #D3D3D3; border-left-style: none; border-left-width: 1px; border-left-color: #D3D3D3; border-right-style: none; border-right-width: 1px; border-right-color: #D3D3D3; vertical-align: middle; overflow-x: hidden; text-align: left;" valign="middle" align="left"><span class="gt_from_md"><strong style="margin-top: 0; margin-bottom: 0;">cleaning names</strong></span></td>
<td headers="package_name" class="gt_row gt_left" style="border-style: none; padding-top: 8px; padding-bottom: 8px; padding-left: 5px; padding-right: 5px; margin: 10px; border-top-style: solid; border-top-width: 1px; border-top-color: #D3D3D3; border-left-style: none; border-left-width: 1px; border-left-color: #D3D3D3; border-right-style: none; border-right-width: 1px; border-right-color: #D3D3D3; vertical-align: middle; overflow-x: hidden; text-align: left;" valign="middle" align="left"><span class="gt_from_md">janitor</span></td>
<td headers="fun()" class="gt_row gt_left" style="border-style: none; padding-top: 8px; padding-bottom: 8px; padding-left: 5px; padding-right: 5px; margin: 10px; border-top-style: solid; border-top-width: 1px; border-top-color: #D3D3D3; border-left-style: none; border-left-width: 1px; border-left-color: #D3D3D3; border-right-style: none; border-right-width: 1px; border-right-color: #D3D3D3; vertical-align: middle; overflow-x: hidden; text-align: left;" valign="middle" align="left"><span class="gt_from_md">clean_names()</span></td>
<td headers="description" class="gt_row gt_left" style="border-style: none; padding-top: 8px; padding-bottom: 8px; padding-left: 5px; padding-right: 5px; margin: 10px; border-top-style: solid; border-top-width: 1px; border-top-color: #D3D3D3; border-left-style: none; border-left-width: 1px; border-left-color: #D3D3D3; border-right-style: none; border-right-width: 1px; border-right-color: #D3D3D3; vertical-align: middle; overflow-x: hidden; text-align: left;" valign="middle" align="left"><span class="gt_from_md">Resulting names are unique, consisting only <br style="margin-top: 0; margin-bottom: 0;"> of the _ character, numbers, and letters</span></td></tr>
    <tr style="border-style: none;"><td headers="type" class="gt_row gt_left" style="border-style: none; padding-top: 8px; padding-bottom: 8px; padding-left: 5px; padding-right: 5px; margin: 10px; border-top-style: solid; border-top-width: 1px; border-top-color: #D3D3D3; border-left-style: none; border-left-width: 1px; border-left-color: #D3D3D3; border-right-style: none; border-right-width: 1px; border-right-color: #D3D3D3; vertical-align: middle; overflow-x: hidden; text-align: left;" valign="middle" align="left"><span class="gt_from_md"><strong style="margin-top: 0; margin-bottom: 0;">renaming</strong></span></td>
<td headers="package_name" class="gt_row gt_left" style="border-style: none; padding-top: 8px; padding-bottom: 8px; padding-left: 5px; padding-right: 5px; margin: 10px; border-top-style: solid; border-top-width: 1px; border-top-color: #D3D3D3; border-left-style: none; border-left-width: 1px; border-left-color: #D3D3D3; border-right-style: none; border-right-width: 1px; border-right-color: #D3D3D3; vertical-align: middle; overflow-x: hidden; text-align: left;" valign="middle" align="left"><span class="gt_from_md">pandas</span></td>
<td headers="fun()" class="gt_row gt_left" style="border-style: none; padding-top: 8px; padding-bottom: 8px; padding-left: 5px; padding-right: 5px; margin: 10px; border-top-style: solid; border-top-width: 1px; border-top-color: #D3D3D3; border-left-style: none; border-left-width: 1px; border-left-color: #D3D3D3; border-right-style: none; border-right-width: 1px; border-right-color: #D3D3D3; vertical-align: middle; overflow-x: hidden; text-align: left;" valign="middle" align="left"><span class="gt_from_md">df.rename(columns={‘dept2’: ‘econ’, ‘dept3’: ‘isa’})</span></td>
<td headers="description" class="gt_row gt_left" style="border-style: none; padding-top: 8px; padding-bottom: 8px; padding-left: 5px; padding-right: 5px; margin: 10px; border-top-style: solid; border-top-width: 1px; border-top-color: #D3D3D3; border-left-style: none; border-left-width: 1px; border-left-color: #D3D3D3; border-right-style: none; border-right-width: 1px; border-right-color: #D3D3D3; vertical-align: middle; overflow-x: hidden; text-align: left;" valign="middle" align="left"><span class="gt_from_md">Will rename the columns dept2 and dept3 to econ and isa</span></td></tr>
  </tbody>
  
  
</table>
</div>

---

## An Example of Cleaning and Renaming Names

``` python
import pandas as pd
import janitor

# Create a dataframe
df = pd.DataFrame(
  {'dept 1': [1, 2, 3], 'dept 2': [4, 5, 6], 'dept 3': [7, 8, 9]}
  )

df = (
  df
  # clean the column names
  .clean_names()
  # note that the column names are now separated with an underscore
  .rename(columns={'dept_2': 'econ', 'dept_3': 'isa'}) 
  )

df.head(n=2)
```

```
##    dept_1  econ  isa
## 0       1     4    7
## 1       2     5    8
```

---

## Changing Column/Series Data Types

- **Pandas** has a function called `astype()` that can be used to change the data type of a column. To convert into a different data type, you can use the following syntax:  
    + **Int:**  `df['column_name'] = df['column_name'].astype('int')`  
    + **Categorical:**  `df['column_name'] = df['column_name'].astype('category')`  
    + **Datetime:**  `df['column_name'] = df['column_name'].astype('datetime64')`
    + **Numeric:**  `df['column_name'] = df['column_name'].astype('float')`
    + **String:**  `df['column_name'] = df['column_name'].astype('str')`
    + **Boolean:**  `df['column_name'] = df['column_name'].astype('bool')`

- Obviously, these can also be passed via the `assign()` function in **Pandas**. For example:   
`df = df.assign(column_name = df['column_name'].astype('int'))`.

---
class: inverse, center, middle

# Data Cleaning

---

## My Opinionated Perspective on Data Cleaning

.footnote[
<html>
<hr>
</html>

**Reminders:** Please do not use mean/median imputation (they are awful). Also, please do not impute or `.dropna()` until you understand the nature of your missing data. 
]
    
---

## Additional Thoughts on Data Cleaning

- **Data cleaning** is the process of ensuring that your data is consistent and correct. This involves checking for errors and inconsistencies, handling missing data, and ensuring the final dataset is consistent and ready for analysis.

- **Data cleaning** is a critical step in the data analysis process. If you don't clean your data, you can't trust your results.

- **Data cleaning** is a time-consuming process. It's not uncommon for data scientists to spend 80% of their time cleaning data and only 20% of their time analyzing it.

- **Data cleaning** is an iterative process. You'll often find that you need to go back and clean your data again after you've started analyzing it.

---

## Data Cleaning in Python

- **Pandas** has a number of functions that can be used to clean data. Some of the most common ones include:  
    + `drop_duplicates()`: Remove duplicate rows from a DataFrame.  
    + `dropna()`: Remove missing values from a DataFrame.  
    + `fillna()`: Fill missing values in a DataFrame (use with caution).  
    + `str.replace()`: Replace values in a Series.  
    + `str.strip()`: Remove leading and trailing whitespace from a Series.  
    + `str.extract()`: Extract values from a Series using a regular expression.  
    + `str.get_dummies()`: Convert a Series to dummy variables.  
    + `str.pad()`: Pad strings in a Series.   
    + `str.zfill()`: Pad strings in a Series with zeros.

---
class: inverse, center, middle

# Combining Multiple DataFrames

---

## A Motivating Example for `merge()`

.pull-left[

A **df of IP addresses and counts**:

.font90[

``` python
df1 = pd.DataFrame(
  {'ip': ['134.53.10.20', '134.53.355.200', '129.171.123.45'],
   'count': [10, 20, 30]}
  )

print(df1)
```

```
##                ip  count
## 0    134.53.10.20     10
*## 1  134.53.355.200     20
## 2  129.171.123.45     30
```
]
]

.pull-right[

A **df of IP addresses and cities**:

.font90[

``` python
df2 = pd.DataFrame(
  {'ip': ['134.53.10.20', '69.12.18.100', '129.171.123.45'],
   'city': ['Oxford', 'Buffalo', 'Coral Gables']}
  )

print(df2)
```

```
##                ip          city
## 0    134.53.10.20        Oxford
*## 1    69.12.18.100       Buffalo
## 2  129.171.123.45  Coral Gables
```
]
]

**What are the differences between the two data frames**

.can-edit.key-activity9[
Edit me to answer the question.
]

---

## `merge()`: API Reference

---

## `merge()`: Syntax

- The `merge()` function in **Pandas** is used to merge two DataFrames. It is similar to the `join()` function in SQL.

- The `merge()` function takes the following arguments:  
    + `left`: The first DataFrame to merge.  
    + `right`: The second DataFrame to merge.  
    + `on`: The column to merge on.   
       * `left_on` and `right_on` can be used if the column names are different.  
    + `how`: The type of join to perform (e.g., 'inner', 'outer', 'left', 'right').  
    + `suffixes`: A tuple of strings to append to overlapping column names.  
    + `indicator`: If `True`, adds a column to the merged DataFrame indicating the source of each row.  
    + `validate`: If `True`, checks that the merge is unique.

---

## `merge()`

.pull-left[

**Default** `merge()`:
.font90[

``` python
df3 = pd.merge(df1, df2, on='ip')
print(df3)
```

```
##                ip  count          city
## 0    134.53.10.20     10        Oxford
## 1  129.171.123.45     30  Coral Gables
```
]
]

.pull-right[

**Changing** the `how` to **left**:
.font90[

``` python
df4 = pd.merge(df1, df2, on='ip', how='left')
print(df4)
```

```
##                ip  count          city
*## 0    134.53.10.20     10        Oxford
## 1  134.53.355.200     20           NaN
## 2  129.171.123.45     30  Coral Gables
```
]
]

**What are the differences between the two data frames?**

.can-edit.key-activity10[
Edit me to answer the question.
]

---

## `join()`: API Reference

---

## `join()`: Syntax

- The `join()` function is similar to the `merge()` function, but it is used to join DataFrames on their **indices**. The syntax for the `join()` function is as follows:  
    + `df1.join(df2)`: Join `df1` and `df2` on their indices.  
    + `df1.join(df2, how='left')`: Join `df1` and `df2` on their indices using a left join.  
    + `df1.join(df2, how='right')`: Join `df1` and `df2` on their indices using a right join.  
    + `df1.join(df2, how='inner')`: Join `df1` and `df2` on their indices using an inner join.  
    + `df1.join(df2, how='outer')`: Join `df1` and `df2` on their indices using an outer join.

---

## `concat()`: API Reference

---

## `concat()`: Syntax

- The `concat()` function can be used to concatenate DataFrames along rows or columns. The syntax for the `concat()` function is as follows:  
    + `pd.concat([df1, df2], axis=0)`: Concatenate DataFrames along rows, i.e., stack them on top of each other.  
    + `pd.concat([df1, df2], axis=1)`: Concatenate DataFrames along columns, i.e., stack them side by side.    
    + `pd.concat([df1, df2], axis=0, ignore_index=True)`: Concatenate DataFrames along rows, ignoring the index.  
    + `pd.concat([df1, df2], axis=1, join='inner')`: Concatenate DataFrames along columns, using the intersection of the columns.  
    + `pd.concat([df1, df2], axis=1, join='outer')`: Concatenate DataFrames along columns, using the union of the columns.

---

## Summary: Merge, Concat, and Join

- **Pandas** has a number of functions that can be used to combine DataFrames. Some of the most common ones include:  
    + `merge()`: Merge DataFrames using a database-style join. `merge()` can be used to perform inner, outer, left, and right joins on one or more common columns.   
    + `concat()`: Concatenate (stack) DataFrames along rows or columns.
    + `join()`: Join DataFrames using their indexes.

---
class: inverse, center, middle

# A Demonstration of Combining Data Frames in a Cyber Security Context

---

## An Overview of our Two Datasets

.font90[
Our [Toxic IP Addresses dataset](https://raw.githubusercontent.com/fmegahed/isa419/main/data/listed_ip_90_all.csv) comes from [Stop Forum Spam](https://www.stopforumspam.com/downloads) and contains a list of IP addresses that have been reported as toxic. The dataset contains the following columns:  
  - `ip`: The IP address.  
  - `frequency`: The number of times that the IP address number has been reported during the 90 day period.  
  - `lastseen`: The date/time (UTC) when the IP address was last seen.

I used the `geoip2` library to geolocate the IP addresses in the dataset based on the [Maxmind GeoLite2 City Database](https://www.maxmind.com/en/accounts/974136/geoip/downloads). I saved the results in a CSV file called [ip_geolocation.csv](https://raw.githubusercontent.com/fmegahed/isa419/main/data/ip_geolocation.csv), containing five columns:  
  - `IP`: The IP address.  
  - `Country`: The country where the IP address is located.  
  - `City`: The city where the IP address is located.  
  - `Latitude`: The latitude of the IP address.  
  - `Longitude`: The longitude of the IP address.  
]

---

## Analyzing Toxic IPs and their Geolocations

.panelset[

.panel[.panel-name[Task]

- Load the [Toxic IP Addresses dataset](https://raw.githubusercontent.com/fmegahed/isa419/main/data/listed_ip_90_all.csv) into a DataFrame called `toxic_ips`.

- Load the [IP Geolocation dataset](https://raw.githubusercontent.com/fmegahed/isa419/main/data/ip_geolocation.csv) into a DataFrame called `geolocation`.

- Answer the questions in the next tabs.

- You can work in groups of 2-3 students.  
]

.panel[.panel-name[Task 1]

- Combine the `toxic_ips` and `geolocation` DataFrames using an appropriate function.

- Drop any rows with missing values.

- What are the number of columns and rows of the resulting DataFrame?  
]

.panel[.panel-name[Task 2]

- Ensure that the resulting DataFrame is technically correct.

]

.panel[.panel-name[Task 3]

- Compute the number of unique countries from which the toxic IP addresses originate.

]

.panel[.panel-name[Task 4]

- Compute the mean number of reports per IP address.

]

.panel[.panel-name[Task 5]

- Compute the number of unique IP addresses reported since the beginning of the year.

]

---
class: inverse, center, middle

# Recap

---

## Summary of Main Points

By now, you should be able to do the following:

- Ensure that your imported data is **technically correct** (rename columns and fix `dtypes`)

- Clean data to ensure that your data is consistent

- Understand the difference between `concatenate`, `merge`, and `join`.

---

## 📝 Review and Clarification 📝

1. **Class Notes**: Take some time to revisit your class notes for key insights and concepts.
2. **Zoom Recording**: The recording of today's class will be made available on Canvas approximately 3-4 hours after the end of class.
3. **Questions**: Please don't hesitate to ask for clarification on any topics discussed in class. It's crucial not to let questions accumulate.