BAA1030 - Data Analytics and Story Telling

.title[
# BAA1030 - Data Analytics and Story Telling
]
.subtitle[
## Lecture 2: Data Collection, Storage and Access
]
.author[
### Damien Dupré - Dublin City University
]

---

# Always Prepare the Data

.center[**To efficiently perform any kind of analysis on data, it is essential to format the data file in the most suitable way.**]

To do so, 2 steps are required:

1. Cleaning the data from any noise
2. Transforming the data in order to obtain the relevant variables

---

# 1. Cleaning Data

---

# An Essential Step

- If you don't have big data... then use Microsoft Excel!

- If you do have big data, then use R or Python.

---

# Question from Kareem

---

# Exercise

## Find what is going wrong with this table

---

# Answer from Michael

---

# Use a Name Convention

An addition to Michael's list would be to transform headers with a proper naming convention

My suggestion is **snake_case**: all small letter and words separated by "_"

---

# To Clean Data ...

### 1. Ditch the chart and all non values
Charts can mess up with other software

### 2. Column headings in row 1
No more than 1 heading row and remove blanks

### 3. Columns start at column A
Remove blanks before data

### 4. Use a naming convention
snake_case is preferable but any would do

### 5. Save as .csv file
Better format and keeps only the current sheet

---

## Exercise: Clean unicef.xlsx

---

# Exercise: Clean unicef.xlsx

Unicef collects a lot of data but they don't know either how to process them or how to visualise them. The following file is coming from their official website and was the data used for the EDHEC European Data Visualisation Challenge: https://www.edhec.edu/en/student-dataviz-challenge-2022

### <span><i class="fas  fa-arrow-circle-right faa-horizontal animated faa-slow " style=" color:blue;"></i></span> On the module Loop page, got to "Lecture Data" and download the document called "unicef.xlsx"

### <span><i class="fas  fa-arrow-circle-right faa-horizontal animated faa-slow " style=" color:blue;"></i></span> Open and clean the 1st sheet of this file

---

# 2. Data Transformation

---

# Master the Key Transformations

Most important work is to tidy your data: 
- Takes time to saves time and solves problems
- Only 5 key transformations need to be mastered

## 2. Reduction

## 3. Direction

## 4. Aggregation

## 5. Combination
]

.pull-right[
<img src="https://raw.githubusercontent.com/damien-dupre/img/main/key_movements.png" width="70%" style="display: block; margin: auto;" />
]

---

# Extension

In MS Excel:
  - First row is row name (name convention)
  - Second row is the function (starts with = sign)
  - Following rows are applying the function (double click bottom right corner of the cell)

---

# Excel Functions

### For numeric values
  - Numeric operator ( + - / *)
  - $ (fixed parameter)
  - COUNT(), MIN(), MAX(), SUM(), AVERAGE (), STDEV()

### For character strings
  - LEFT()
  - CONCATENATE()

### Extra function
  - IF(condition, value if true, value if false)

---

# Reduction

In MS Excel:
  - Select header row
  - In Data tab, use Filter
  - Click the drop-down arrow for the column you want to filter
  - Choose values to filter

---

# Excel Filters

<span><i class="fas  fa-exclamation-triangle faa-flash animated faa-slow " style=" color:red;"></i></span> Remember:

- Rows already filtered have a row index are coloured in blue
- Copy-Paste filtered table in a new document if you want to work only on these values

<div class="figure" style="text-align: center">
<img src="https://www.excel-easy.com/data-analysis/images/filter/filter-result.png" alt="Example of data filtered buy the column Country to keep only values corresponding to USA" width="50%" />
<p class="caption">Example of data filtered buy the column Country to keep only values corresponding to USA</p>
</div>

---

# Direction

- Direction = Arrange Row Order

- In MS Excel:
  - Select table
  - In Data tab, use Sort
  - Choose column to sort and how to sort

Be careful of taking into account all the table (all rows & all columns)

Double check if all columns changed!

---

# Aggregation

- Aggregation = Summary of Column

- In MS Excel:
  - Simple = use function at the end of a table
  - Complex = use pivot table
  
<img src="https://raw.githubusercontent.com/damien-dupre/img/main/aggregation_img.png" width="50%" style="display: block; margin: auto;" />

---

# Pivot Table in Excel

1. Select data
2. In Insert, use Pivot Table
3. Drag columns to sort by row/column
4. Choose value column to be aggregated
5. Choose type of aggregation

If you want to use the Pivot Table for further analysis:

- Copy-Paste it in another document
- Paste as value (removes dynamic link)

---

# Combination

- Combination = Join two tables

- In MS Excel:
  - One Column = vlookup function
  - Multiple Columns = Power Query (Windows only)
  
<img src="https://raw.githubusercontent.com/damien-dupre/img/main/combination_img.png" width="50%" style="display: block; margin: auto;" />

### =VLOOKUP(value, table, col_index, [range_lookup])

- value: The value the common column between the 2 tables
- table: The 2nd table containing the value to import in the 1st table
- col_index: Column number of the 2nd table containing the value to import
- range_lookup: TRUE = approximate match / FALSE = exact match

<span><i class="fas  fa-exclamation-triangle faa-flash animated faa-slow " style=" color:red;"></i></span> Remember to use `$` in the table coordinates if you want to apply the function on multiple cells.

---

## Exercise: Transformations

---

# Exercise: Transformations

On the module Loop page, got to "Lecture Data" and download the document called "organisation_alpha.xls"

1. **Extension**: Create a new variable/column which is the average sales per quarter in 2021 and 2022 for each employee
2. **Reduction**: Filter employee’s 2022 salary to keep only employees with a salary higher than 30k and revert back to the full dataset
3. **Aggregation**: Calculate the average salary by gender and by location
4. **Combination**: Using the VLOOKUP function, add to the table a column corresponding to the  2020 salary located in the 2nd sheet

---

# Extra Analytic Tips

---

# Tidy Data

- Each variable has its own column 
- Each observation is placed in its own row 
- Each value is placed in its own cell

---

# Long or Wide?

## Long Format

## Wide Format

---

# Long or Wide?

---

# Reshape Table in Excel

In Data tab:

1. Get Data/New Query> From File > From [Workbook/CSV]
2. Select your file > Edit
3. Select columns to be reshaped
4. Transform
  - Pivot Columns: from long table to wide table
  - Unpivot Columns: from wide to long table
  
<img src="https://raw.githubusercontent.com/damien-dupre/img/main/reshape_excel.gif" width="50%" style="display: block; margin: auto;" />

---

# Repeat Action Automatically

In Excel, the Macro button allows to record a sequence of  actions and to reproduce these actions:

- VBA Code automatically recorded
- Useful to process similar data files

Recording a Macro:

1. View Tab
2. Use Macros > Record Macro
3. Do your actions
4. Save the macro with a keyboard shortcut
5. Use the macro again to reproduce your actions

---

# Online Trainings

## Mandatory Online Trainings on Kubicle

Excel Essentials
- [Data Manipulation and Formatting](https://app.kubicle.com/courses/data-manipulation-and-formatting) (150 min)
- [Formulas and Functions](https://app.kubicle.com/courses/formulas-and-functions) (210 min)

Data Analysis
- [Lookups and Database Functions](https://app.kubicle.com/courses/lookups-and-database-functions) (150 min)
- [Pivot Tables](https://app.kubicle.com/courses/pivot-tables) (150 min)

## Optional Free Online Trainings

More about MS EXCEL with free online learning on Udemy.com (needs free registration):

- [Excel Quick Start Tutorial (36 min)](https://www.udemy.com/course/excel_quickstart/) 
- [Ten Excel Features Every Analyst Should Know (30 min)](https://www.udemy.com/course/ten-excel-features-every-analyst-should-know/) 
- [Excel Dashboards (60 min)](https://www.udemy.com/course/excel-dashboards-in-an-hour/)

---
class: inverse, mline, left, middle

# Thanks for your attention and don't hesitate to ask if you have any question!

[<svg aria-hidden="true" role="img" viewBox="0 0 512 512" style="height:1em;width:1em;vertical-align:-0.125em;margin-left:auto;margin-right:auto;font-size:inherit;fill:currentColor;overflow:visible;position:relative;"><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> @damien_dupre](http://twitter.com/damien_dupre)  
[<svg aria-hidden="true" role="img" viewBox="0 0 496 512" style="height:1em;width:0.97em;vertical-align:-0.125em;margin-left:auto;margin-right:auto;font-size:inherit;fill:currentColor;overflow:visible;position:relative;"><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> @damien-dupre](http://github.com/damien-dupre)  
[<svg aria-hidden="true" role="img" viewBox="0 0 640 512" style="height:1em;width:1.25em;vertical-align:-0.125em;margin-left:auto;margin-right:auto;font-size:inherit;fill:currentColor;overflow:visible;position:relative;"><path d="M579.8 267.7c56.5-56.5 56.5-148 0-204.5c-50-50-128.8-56.5-186.3-15.4l-1.6 1.1c-14.4 10.3-17.7 30.3-7.4 44.6s30.3 17.7 44.6 7.4l1.6-1.1c32.1-22.9 76-19.3 103.8 8.6c31.5 31.5 31.5 82.5 0 114L422.3 334.8c-31.5 31.5-82.5 31.5-114 0c-27.9-27.9-31.5-71.8-8.6-103.8l1.1-1.6c10.3-14.4 6.9-34.4-7.4-44.6s-34.4-6.9-44.6 7.4l-1.1 1.6C206.5 251.2 213 330 263 380c56.5 56.5 148 56.5 204.5 0L579.8 267.7zM60.2 244.3c-56.5 56.5-56.5 148 0 204.5c50 50 128.8 56.5 186.3 15.4l1.6-1.1c14.4-10.3 17.7-30.3 7.4-44.6s-30.3-17.7-44.6-7.4l-1.6 1.1c-32.1 22.9-76 19.3-103.8-8.6C74 372 74 321 105.5 289.5L217.7 177.2c31.5-31.5 82.5-31.5 114 0c27.9 27.9 31.5 71.8 8.6 103.9l-1.1 1.6c-10.3 14.4-6.9 34.4 7.4 44.6s34.4 6.9 44.6-7.4l1.1-1.6C433.5 260.8 427 182 377 132c-56.5-56.5-148-56.5-204.5 0L60.2 244.3z"/></svg> damien-datasci-blog.netlify.app](https://damien-datasci-blog.netlify.app)  
[<svg aria-hidden="true" role="img" viewBox="0 0 512 512" style="height:1em;width:1em;vertical-align:-0.125em;margin-left:auto;margin-right:auto;font-size:inherit;fill:currentColor;overflow:visible;position:relative;"><path d="M16.1 260.2c-22.6 12.9-20.5 47.3 3.6 57.3L160 376V479.3c0 18.1 14.6 32.7 32.7 32.7c9.7 0 18.9-4.3 25.1-11.8l62-74.3 123.9 51.6c18.9 7.9 40.8-4.5 43.9-24.7l64-416c1.9-12.1-3.4-24.3-13.5-31.2s-23.3-7.5-34-1.4l-448 256zm52.1 25.5L409.7 90.6 190.1 336l1.2 1L68.2 285.7zM403.3 425.4L236.7 355.9 450.8 116.6 403.3 425.4z"/></svg> damien.dupre@dcu.ie](mailto:damien.dupre@dcu.ie)