Most Time-Consuming Data Science Task: Data Cleaning

80% of the work - Data preparation accounts for about 80% of the work of data scientists

Data Cleaning: ensure \(\color{red}{\text{reliability}}\) and \(\color{red}{\text{quality}}\)

In business analytics, data comes from various sources such as databases, spreadsheets, and external sources. These sources often contain missing values, duplicate entries, outliers, and formatting inconsistencies, which can strongly skew the analysis results and lead to incorrect business decisions if not addressed properly.

Data cleaning is a crucial step in the process of preparing data for analysis in the field of business analytics. It involves identifying and correcting errors, inconsistencies, and inaccuracies in the dataset to ensure its reliability and quality.

Do you enjoy it?

It is important and time-consuming,

but, do you enjoy data cleaning?

Examples: read.csv() and read_excel()

We focus on read.csv() function to read in .csv files, and read_excel() to read in .xlsx files.

1. Example 1: Importing data from a CSV file (Auto.csv)

Auto <- read.csv("data/Auto.csv")
# View(Auto)
head(Auto)

##   mpg cylinders displacement horsepower weight acceleration year origin
## 1  18         8          307        130   3504         12.0   70      1
## 2  15         8          350        165   3693         11.5   70      1
## 3  18         8          318        150   3436         11.0   70      1
## 4  16         8          304        150   3433         12.0   70      1
## 5  17         8          302        140   3449         10.5   70      1
## 6  15         8          429        198   4341         10.0   70      1
##                        name
## 1 chevrolet chevelle malibu
## 2         buick skylark 320
## 3        plymouth satellite
## 4             amc rebel sst
## 5               ford torino
## 6          ford galaxie 500

2. Example 2: Importing data from an Excel spreadsheet

(RetailSales2018.xlsx); (RetailSales2018_clean.xlsx)

library(readxl)
Retail <- read_xlsx(path = "data/RetailSales2018.xlsx")

## New names:
## • `` -> `...2`
## • `` -> `...3`
## • `` -> `...4`
## • `` -> `...5`

head(Retail)

## # A tibble: 6 × 5
##   RETAIL & FOOD SERVICES(http://www.census.gov/retail/…¹ ...2  ...3  ...4  ...5 
##   <chr>                                                  <chr> <chr> <chr> <chr>
## 1 SEASONAL FACTORS(http://www.census.gov/retail/marts/w… <NA>  <NA>  <NA>  <NA> 
## 2 CPI Table 24(http://www.bls.gov/cpi)                   <NA>  <NA>  <NA>  <NA> 
## 3 <NA>                                                   <NA>  <NA>  <NA>  <NA> 
## 4 <NA>                                                   <NA>  <NA>  <NA>  <NA> 
## 5 Year                                                   Month Sales S_Fa… CPI  
## 6 1992                                                   JAN   1471… 0.89… 138.1
## # ℹ abbreviated name:
## #   ¹​`RETAIL & FOOD SERVICES(http://www.census.gov/retail/marts/www/timeseries.html)`

Data Transformation

Ability to transform data by creating new variables, reshaping data from wide to long format (and vice versa), and summarizing data using functions like mutate(), select(), filter(), arrange(), group_by(), summarize() from the dplyr package, and functions like pivot_longer() and pivot_wider() from the tidyr package.

Data Manipulation

Skills to perform various data manipulation tasks such as merging/joining datasets, splitting data into subsets, and reshaping data using functions like merge(), join(), split(), reshape(), rbind(), cbind().

Data Visualization

For quick data exploration, base R plotting functions can provide an expeditious and straightforward approach to understanding your data. Data visualization skills are the abilities to visualize data to explore patterns, relationships, and trends using functions like ggplot2 for creating sophisticated and customizable plots, plot() for basic plots, and ggplotly() from the plotly package for interactive plots.

Handling Dates and Times

Skills to work with date and time data, including parsing, formatting, extracting components (e.g., year, month, day), and performing date arithmetic using functions like as.Date(), as.POSIXct(), strftime(), strptime(), lubridate package.

Handling Strings

Ability to manipulate and process character strings, including pattern matching, substring extraction, and string manipulation using functions like grep(), sub(), gsub(), strsplit(), and stringr package.

Looping and Conditional Statements

Understanding of control structures like loops (for, while) and conditional statements (if, else, ifelse) to automate repetitive tasks and apply conditional operations on data.

Efficient Coding Practices

Familiarity with efficient coding practices such as vectorization, using R’s apply family of functions (apply(), lapply(), sapply(), vapply(), mapply()), and leveraging the capabilities of packages like dplyr and tidyr for faster and more concise code.

These skills are crucial for effectively managing and preparing data for analysis and visualization tasks in R. Continuous practice and exploration of R packages and functions related to data wrangling will enhance proficiency in handling diverse datasets and extracting meaningful insights.

Time to flex our new knowledge muscles!

Let’s apply what you’ve learned to a real case.

go to top

go back to course website