MT611 - Quantitative Research Methods

.title[
# MT611 - Quantitative Research Methods
]
.subtitle[
## Lecture 3: Collect, Clean, and Transform Data
]
.author[
### Damien Dupré
]
.date[
### 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, 3 steps are required:

1. Understanding how data have been collected
2. Cleaning the data from any noise
3. Transforming the data in order to obtain the relevant variables

---

# 1. Collecting Data

---

# Data Everywhere

What are Big Data?

- In general: Spreadsheet too large or too complex to be handled by conventional tools
- For me: Spreadsheet larger then Microsoft Excel’s Limits (2016)
    - Total number of rows: 1,048,576 rows
    - Total number columns: 16,384 columns

---

# Collecting Data

A tool is required to collect data, but there are tools to measure anything:
  
- Naturalistic Objects
- Economic Status
- Social Events
- Psychological States
- ...

---

# Focus on Psychometric Scales

**A score has to be calculated** from all the items to produce the variable to analyse:

- Average of all items (equal contribution to the latent variable score)
- Factor analysis (unequal contribution to the latent variable score)

**A scale should have been validated** by its authors:

- Don't change the items except when possible
- Don't change items' range of possibilities

To be analysed, **a scale has some requirements**:
- All the items must have the same range of possibilities/modalities
- All the items must be analysed in the same direction
- All the items must correlate together (scale reliability)
- A unique theoretical construct score has to be calculated from all the items

---

# Focus on Psychometric Scales

Online Survey Software are replacing the paper-pencil method. Here is a list of some:

- Google Form (from your google drive, DCU hosted)
- Qualtrics (https://dcubusinessschool.eu.qualtrics.com, DCU Business School account)
- Survey Monkey (https://www.surveymonkey.com/, 40 participants free)
- LimeSurvey (https://www.limesurvey.org/, 25 participants free/month )
- Zoho (https://www.zoho.com, 100 participants free)
- Gorilla Experiment (https://gorilla.sc, €1 per participants)

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

- Always ask for age and gender in a survey
- Participant recruitment by list of contact/email has between 15% and 10% of answer rate

Platforms to find and reward participants:
- Amazon MTurk (https://www.mturk.com/ mainly US and India)
- Prolific Academic (https://www.prolific.co/ world wide)
- Qualtrics (https://www.qualtrics.com/uk/research-services/online-sample/)
- Survey Monkey (https://www.surveymonkey.com/mp/find-survey-participants/)

---

# Latent Variables

Scale to measure the **"Perceived Ease-of-use" of MS EXCEL** in 4 items were measured from 1 "totally disagree" to 7 "totally agree":

.pull-left[
- **q1.** I think learning MS EXCEL is easy
- **q2.** Understanding MS EXCEL is easy
- **q3.** I am good at using MS EXCEL
- **q4.** I think using MS EXCEL is easy
]

.pull-right[
<div id="htmlwidget-9eceb20dc2a931752ae4" style="width:504px;height:200px;" class="grViz html-widget"></div>
<script type="application/json" data-for="htmlwidget-9eceb20dc2a931752ae4">{"x":{"diagram":"\ndigraph rmarkdown {\n  graph [rankdir = RL]\n  \n  node [shape = oval]\n  \"Perceived Ease-of-use\n  of MS EXCEL\"\n  \n  node [shape = box]\n  q1; q2; q3; q4\n  \n  \"Perceived Ease-of-use\n  of MS EXCEL\"-> {q1 q2 q3 q4}   \n\n}\n","config":{"engine":"dot","options":null}},"evals":[],"jsHooks":[]}</script>
]

Here are the results with 3 students. The score of the "Perceived Ease-of-use of MS EXCEL" latent variable is calculated using the average of all items.

|employee | q1| q2| q3| q4| peo_score|
|:--------|--:|--:|--:|--:|---------:|
|Sinead   |  7|  5|  7|  7|      6.50|
|Patrick  |  5|  4|  6|  6|      5.25|
|Damien   |  3|  1|  2|  3|      2.25|

---

# Validity and Reliability

**Validity = is my variable measuring the construct that I think I am measuring?**
- Does the measurement make sense? 
- Would the results be reproduced with another scale measuring the same latent variable?
- Are the results correlated to latent variables that are related?

Validity test is only performed when a scale is created (no need for existing scales)

**Reliability = consistency of items inside a measurement**
- Test-retest reliability
- Inter-rater reliability
- Correlation inter-item (Cronbach's alpha)

Reliability test is performed every time a scale is used but only using Cronbach's alpha

---

# Validity and Reliability

---

# Survey Example

**Perceived Usefulness:**
- *(PU1)* Using MS EXCEL would improve my performance in statistical analysis 
- *(PU2)* Using MS EXCEL would increase my productivity in statistical analysis
- *(PU3)* Using MS EXCEL would enhance my effectiveness in statistical analysis
- *(PU4)* Using MS EXCELR would make it easier for me to engage in statistical analysis 
- *(PU5)* I think using MS EXCEL is very useful for me to engage in statistical analysis

**Perceived Ease-Of-Use:**
- *(PEOU1)* I think learning to use MS EXCEL is easy 
- *(PEOU2)* I think doing what I want via MS EXCEL is easy 
- *(PEOU3)* I think becoming skilful at using MS EXCEL is easy 
- *(PEOU4)* I think using MS EXCEL is easy

**Behavioural Intention:**
- *(BI1)* Assuming I had access to MS EXCEL, I intend to use it 
- *(BI2)* Given that I had access to MS EXCEL, I predict that I would use it

---

# Survey Example

<div id="htmlwidget-f30d7598595affc4f25a" style="width:504px;height:400px;" class="grViz html-widget"></div>
<script type="application/json" data-for="htmlwidget-f30d7598595affc4f25a">{"x":{"diagram":"\ndigraph rmarkdown {\n  graph [rankdir = LR]\n  \n  node [shape = oval]\n  \"Perceived Ease-of-use\"; \"Perceived Usefulness\"; \"Intention to Use\"; \"Actual Use\"\n  \n  node [shape = box]\n  PU1; PU2; PU3; PU4; PU5; PEOU1; PEOU2; PEOU3; PEOU4; BI1; BI2; AU\n  \n  {PU1 PU2 PU3 PU4 PU5} -> \"Perceived Usefulness\" [arrowhead = none]\n  {PEOU1 PEOU2 PEOU3 PEOU4} -> \"Perceived Ease-of-use\" [arrowhead = none]\n  {BI1 BI2} -> \"Intention to Use\" [arrowhead = none]\n  {AU} -> \"Actual Use\" [arrowhead = none]\n  \n  \"Perceived Usefulness\" -> \"Intention to Use\"\n  \"Perceived Ease-of-use\" -> {\"Perceived Usefulness\" \"Intention to Use\"}\n  \"Intention to Use\" -> \"Actual Use\"\n  \n  subgraph {\n      rank = same; \"Perceived Usefulness\"; \"Perceived Ease-of-use\";\n  }\n  \n  subgraph {\n      rank = same; PU1; PU2; PU3; PU4; PU5; PEOU1; PEOU2; PEOU3; PEOU4; BI1; BI2;\n  }\n\n}\n","config":{"engine":"dot","options":null}},"evals":[],"jsHooks":[]}</script>

Google form: https://forms.gle/epRj45iR1jL7r3wV7

Qualtrics: https://dcubusinessschool.eu.qualtrics.com/jfe/form/SV_0JSAjOvXVxzJvgh

---

# Accessing your Data

From a Google Form
1. Login Google Account (be sure to use the DCU account)
2. Go to Drive
3. Click on your Survey form
4. Click on Response
5. Download Response Spreadsheet as .csv

From a Qualtrics survey
1. Login Qualtrics (DCU login/pwd) https://dcubusinessschool.eu.qualtrics.com
2. Click on Data & Analysis
3. Click on Export & Import > Export Data...> CSV (Use numeric values)

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

- Always save the unmodified raw version of your data on the cloud (e.g., google drive, dropbox, one drive, ...)
- Make sure you can download them as a .csv file.

---

# 2. 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

---
class: title-slide, middle

## Exercise: Clean unicef.xlsx

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

**Open and clean the 1st sheet of this file but only until column P, remove all other columns**

---

# 3. Transform Data

---

# 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 ( + - / *)
  - $ (freeze row and/or column)
  - 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 to look for in the first column of a table
- table: The table from which to retrieve a value
- col_index: The column in the table from which to retrieve a value
- range_lookup: TRUE = approximate match / FALSE = exact match

---
class: title-slide, middle

## 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 response to all the questions from the survey for each employee (q1 to q9)
2. **Reduction**: Filter employee’s 2019 salary to keep only employees with a salary higher than 30k
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  2017 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

---
class: title-slide, middle

## Exercise: Reshape chess_llm.csv

Researchers are measuring how good at chess are LLM Algorithms (ChatGPT, Gemini, ...), they want to test the following hypotheses:

> H1: The average number of move to win at a chess game is different for at least 1 of the LLM Algorithms

**Reshape the datafile to have 1 predictor variable called "llm_algorithm" and 1 outcome variable called n_move_to_win"**

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

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

[<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)