Cleaning
## APP COLUMN CLEANING
ps_clean <- ps
# whitespace
ps_clean$App <- str_squish(ps_clean$App)
# remove stray quote characters
ps_clean$App <- str_remove_all(ps_clean$App, '"')
# Reviews to numeric (for correct sorting)
ps_clean$Reviews <- as.numeric(ps_clean$Reviews)
sum(is.na(ps_clean$Reviews))
## [1] 1
# removing duplicates
sum(duplicated(ps_clean$App))
## [1] 1181
ps_clean %>%
count(App, sort = TRUE) %>%
filter(n > 1) %>%
head(20) %>%
knitr::kable(caption = "The Most Repeated Apps")
The Most Repeated Apps
| ROBLOX |
9 |
| CBS Sports App - Scores, News, Stats & Watch
Live |
8 |
| 8 Ball Pool |
7 |
| Candy Crush Saga |
7 |
| Duolingo: Learn Languages Free |
7 |
| ESPN |
7 |
| Bleacher Report: sports news, scores, &
highlights |
6 |
| Bowmasters |
6 |
| Bubble Shooter |
6 |
| Helix Jump |
6 |
| Nick |
6 |
| Sniper 3D Gun Shooter: Free Shooting Games - FPS |
6 |
| Subway Surfers |
6 |
| Temple Run 2 |
6 |
| Zombie Catchers |
6 |
| slither.io |
6 |
| Angry Birds Classic |
5 |
| BeautyPlus - Easy Photo Editor & Selfie Camera |
5 |
| Calorie Counter - MyFitnessPal |
5 |
| Farm Heroes Saga |
5 |
ps_clean <- ps_clean %>%
arrange(App, desc(Reviews)) %>%
distinct(App, .keep_all = TRUE)
nrow(ps) # row count: before
## [1] 10841
nrow(ps_clean) # row count: after
## [1] 9660
sum(duplicated(ps_clean$App))
## [1] 0
# empty app names
sum(ps_clean$App == "" | is.na(ps_clean$App))
## [1] 0
ps_clean %>% filter(App == "" | is.na(App))
## [1] App Category Rating Reviews Size
## [6] Installs Type Price Content.Rating Genres
## [11] Last.Updated Current.Ver Android.Ver
## <0 rows> (or 0-length row.names)
# encoding check
ps_clean %>%
filter(str_detect(App, "[^\x01-\x7F]")) %>%
select(App) %>%
distinct() %>%
head(20)
## App
## 1 1. FC Köln App
## 2 1LINE – One Line with One Touch
## 3 2018Emoji Keyboard 😂 Emoticons Lite -sticker&gif
## 4 3D DJ – DJ Mixer 2018
## 5 3D DJ – Music Mixer with Virtual DJ
## 6 ABC – Live TV & Full Episodes
## 7 AC - Tips & News for Android™
## 8 AJ렌터카 법인 카셰어링
## 9 AP App for Android™
## 10 AP® Guide
## 11 AS - Diario online deportivo. Fútbol, motor y más
## 12 AS Guía de las Ligas 2017-2018
## 13 ASCP® CG Exam Flashcards 2018
## 14 AT&T FamilyMap®
## 15 AT&T Smart Limits℠
## 16 AT&T THANKS®
## 17 AVG Cleaner – Speed, Battery & Memory Booster
## 18 Agoda – Hotel Booking Deals
## 19 Ak Parti Yardım Toplama
## 20 Al Quran Free - القرآن (Islam)
# app name length
ps_clean <- ps_clean %>%
mutate(App_Name_Length = nchar(App))
ps_clean %>%
arrange(desc(App_Name_Length)) %>%
select(App, App_Name_Length) %>%
head(10)
## App
## 1 591 housing transactions - renting houses, middle-class houses, new cases, real-time registration, villas through the sky, apartment suites, MRT, buying a house selling prices, housing mortgages
## 2 Traditional Chinese Medicine Fangfang Liangfang Daquan - Practical and ancient Chinese medicine and old prescriptions for the treatment of various incurable diseases
## 3 104 Looking for a job - looking for a job, looking for a job, looking for a part-time job, health checkup, resume, treatment room
## 4 Truck Car Navi by Navitime Large size car, traffic jam, traffic closure, live camera, typhoon / precipitation map
## 5 All of the parking lot - National Park application (parking lot search / parking sharing / discount payment)
## 6 Japanese / English one-shop search dictionary - Free Japanese - English - Japanese dictionary application
## 7 Yoriza Pension - travel, lodging, pension, camping, caravan, pool villas accommodation discount
## 8 Wemep - Special price representative (special / shopping / shopping app / coupon / shipping)
## 9 Fines of the State Traffic Safety Inspectorate are official: inspection, payment of fines
## 10 Sky People (SPI): A secure blind date through authentication by Seoul National University
## App_Name_Length
## 1 194
## 2 165
## 3 129
## 4 113
## 5 108
## 6 105
## 7 95
## 8 92
## 9 89
## 10 89
## CATEGORY COLUMN CLEANING
# format
ps_clean %>% filter(!str_detect(Category, "^[A-Z_]+$"))
## App Category Rating Reviews Size
## 1 Life Made WI-Fi Touchscreen Photo Frame 1.9 19 NA 1,000+
## Installs Type Price Content.Rating Genres Last.Updated
## 1 Free 0 Everyone February 11, 2018 1.0.19
## Current.Ver Android.Ver App_Name_Length
## 1 4.0 and up 39
ps_clean <- ps_clean %>% filter(str_detect(Category, "^[A-Z_]+$"))
# whitespace
ps_clean$Category <- str_trim(ps_clean$Category)
# NA/empty values
sum(is.na(ps_clean$Category) | ps_clean$Category == "")
## [1] 0
# unique categories
unique(ps_clean$Category)
## [1] "SOCIAL" "COMICS" "TOOLS"
## [4] "COMMUNICATION" "NEWS_AND_MAGAZINES" "SPORTS"
## [7] "HEALTH_AND_FITNESS" "FAMILY" "BOOKS_AND_REFERENCE"
## [10] "LIFESTYLE" "BUSINESS" "SHOPPING"
## [13] "MEDICAL" "GAME" "FINANCE"
## [16] "PERSONALIZATION" "PHOTOGRAPHY" "TRAVEL_AND_LOCAL"
## [19] "DATING" "PRODUCTIVITY" "ART_AND_DESIGN"
## [22] "FOOD_AND_DRINK" "VIDEO_PLAYERS" "HOUSE_AND_HOME"
## [25] "MAPS_AND_NAVIGATION" "EVENTS" "EDUCATION"
## [28] "AUTO_AND_VEHICLES" "WEATHER" "BEAUTY"
## [31] "ENTERTAINMENT" "LIBRARIES_AND_DEMO" "PARENTING"
table(ps_clean$Category)
##
## ART_AND_DESIGN AUTO_AND_VEHICLES BEAUTY BOOKS_AND_REFERENCE
## 61 85 53 222
## BUSINESS COMICS COMMUNICATION DATING
## 420 56 315 170
## EDUCATION ENTERTAINMENT EVENTS FAMILY
## 107 87 64 1874
## FINANCE FOOD_AND_DRINK GAME HEALTH_AND_FITNESS
## 345 112 946 288
## HOUSE_AND_HOME LIBRARIES_AND_DEMO LIFESTYLE MAPS_AND_NAVIGATION
## 73 84 369 131
## MEDICAL NEWS_AND_MAGAZINES PARENTING PERSONALIZATION
## 395 254 60 376
## PHOTOGRAPHY PRODUCTIVITY SHOPPING SOCIAL
## 281 374 202 239
## SPORTS TOOLS TRAVEL_AND_LOCAL VIDEO_PLAYERS
## 325 829 219 164
## WEATHER
## 79
## RATING COLUMN CLEANING
class(ps_clean$Rating)
## [1] "numeric"
# range check (must be between 0-5)
summary(ps_clean$Rating)
## Min. 1st Qu. Median Mean 3rd Qu. Max. NA's
## 1.000 4.000 4.300 4.173 4.500 5.000 1463
ps_clean %>% filter(Rating > 5 | Rating < 0)
## [1] App Category Rating Reviews
## [5] Size Installs Type Price
## [9] Content.Rating Genres Last.Updated Current.Ver
## [13] Android.Ver App_Name_Length
## <0 rows> (or 0-length row.names)
# fix NaN values
sum(is.nan(ps_clean$Rating))
## [1] 1463
ps_clean$Rating[is.nan(ps_clean$Rating)] <- NA
# NA count & rate
sum(is.na(ps_clean$Rating))
## [1] 1463
mean(is.na(ps_clean$Rating)) * 100
## [1] 15.1465
# new column with category-median imputation
ps_clean <- ps_clean %>%
group_by(Category) %>%
mutate(Rating_imputed = ifelse(is.na(Rating), median(Rating, na.rm = TRUE), Rating)) %>%
ungroup()
sum(is.na(ps_clean$Rating_imputed))
## [1] 0
## SIZE COLUMN CLEANING
class(ps_clean$Size)
## [1] "character"
unique(ps_clean$Size) %>% head(20)
## [1] "22M" "9.1M" "203k" "53M" "14M" "41M" "3.8M" "48M" "3.5M" "1.6M"
## [11] "5.0M" "25M" "20M" "15M" "29M" "1.4M" "26M" "10M" "31M" "13M"
sum(ps_clean$Size == "Varies with device")
## [1] 1228
ps_clean <- ps_clean %>%
mutate(
Size_MB = case_when(
str_detect(Size, "M$") ~ as.numeric(str_remove(Size, "M")),
str_detect(Size, "k$") ~ as.numeric(str_remove(Size, "k")) / 1024,
TRUE ~ NA_real_
),
Size_Varies = Size == "Varies with device"
)
summary(ps_clean$Size_MB)
## Min. 1st Qu. Median Mean 3rd Qu. Max. NA's
## 0.0083 4.6000 12.0000 20.3981 28.0000 100.0000 1228
sum(is.na(ps_clean$Size_MB))
## [1] 1228
mean(is.na(ps_clean$Size_MB)) * 100
## [1] 12.71353
## INSTALLS COLUMN CLEANING
class(ps_clean$Installs)
## [1] "character"
unique(ps_clean$Installs)
## [1] "1,000,000+" "10,000+" "100+" "100,000+"
## [5] "500,000+" "500+" "10,000,000+" "5,000+"
## [9] "50,000+" "5+" "1,000+" "10+"
## [13] "50,000,000+" "100,000,000+" "5,000,000+" "50+"
## [17] "0+" "1+" "500,000,000+" "0"
## [21] "1,000,000,000+"
ps_clean <- ps_clean %>%
mutate(Installs_num = str_remove_all(Installs, "[,+]") %>% as.numeric())
summary(ps_clean$Installs_num)
## Min. 1st Qu. Median Mean 3rd Qu. Max.
## 0.000e+00 1.000e+03 1.000e+05 7.798e+06 1.000e+06 1.000e+09
sum(is.na(ps_clean$Installs_num))
## [1] 0
## REVIEWS - LOGICAL CONSISTENCY CHECK (needs Installs_num, run here)
class(ps_clean$Reviews)
## [1] "numeric"
summary(ps_clean$Reviews)
## Min. 1st Qu. Median Mean 3rd Qu. Max.
## 0 25 969 216804 29454 78158306
# negative values
ps_clean %>% filter(Reviews < 0)
## # A tibble: 0 × 18
## # ℹ 18 variables: App <chr>, Category <chr>, Rating <dbl>, Reviews <dbl>,
## # Size <chr>, Installs <chr>, Type <chr>, Price <chr>, Content.Rating <chr>,
## # Genres <chr>, Last.Updated <chr>, Current.Ver <chr>, Android.Ver <chr>,
## # App_Name_Length <int>, Rating_imputed <dbl>, Size_MB <dbl>,
## # Size_Varies <lgl>, Installs_num <dbl>
# missing values
sum(is.na(ps_clean$Reviews))
## [1] 0
ps_clean %>% filter(is.na(Reviews))
## # A tibble: 0 × 18
## # ℹ 18 variables: App <chr>, Category <chr>, Rating <dbl>, Reviews <dbl>,
## # Size <chr>, Installs <chr>, Type <chr>, Price <chr>, Content.Rating <chr>,
## # Genres <chr>, Last.Updated <chr>, Current.Ver <chr>, Android.Ver <chr>,
## # App_Name_Length <int>, Rating_imputed <dbl>, Size_MB <dbl>,
## # Size_Varies <lgl>, Installs_num <dbl>
# extreme values
ps_clean %>% arrange(desc(Reviews)) %>% select(App, Reviews) %>% head(10)
## # A tibble: 10 × 2
## App Reviews
## <chr> <dbl>
## 1 Facebook 78158306
## 2 WhatsApp Messenger 69119316
## 3 Instagram 66577446
## 4 Messenger – Text and Video Chat for Free 56646578
## 5 Clash of Clans 44893888
## 6 Clean Master- Space Cleaner & Antivirus 42916526
## 7 Subway Surfers 27725352
## 8 YouTube 25655305
## 9 Security Master - Antivirus, VPN, AppLock, Booster 24900999
## 10 Clash Royale 23136735
ps_clean %>% arrange(Reviews) %>% select(App, Reviews) %>% head(10)
## # A tibble: 10 × 2
## App Reviews
## <chr> <dbl>
## 1 14thStreetVet 0
## 2 1st Fed CI Mobile Banking 0
## 3 23rd QM BDE EO 0
## 4 25 Mins Ako ay may lobo Etc Pinoy Kid Song Offline 0
## 5 25WPM Amateur ham radio Koch CW Morse code trainer 0
## 6 4-T's Bar-BQ & Catering 0
## 7 4Eternity EO 0
## 8 A.J. Green Wallpapers 4 Fans 0
## 9 ABAI CE Scanner 0
## 10 AC-BL 0
# logical consistency: reviews should not exceed installs
ps_clean %>% filter(Reviews > Installs_num) %>% select(App, Reviews, Installs_num)
## # A tibble: 11 × 3
## App Reviews Installs_num
## <chr> <dbl> <dbl>
## 1 AX Watch for WatchMaker 2 1
## 2 Alarmy (Sleep If U Can) - Pro 10249 10000
## 3 Brick Breaker BR 7 5
## 4 DN Blog 20 10
## 5 DZ Puzzle 14 10
## 6 KBA-EZ Health Guide 4 1
## 7 Mu.F.O. 2 1
## 8 RMEduS - 음성인식을 활용한 R 프로그래밍 실습 시스템 4 1
## 9 Ra Ga Ba 2 1
## 10 Sam.BN Pro 11 10
## 11 Trovami se ci riesci 11 10
## TYPE COLUMN CLEANING
class(ps_clean$Type)
## [1] "character"
table(ps_clean$Type, useNA = "always")
##
## Free NaN Paid <NA>
## 8904 1 754 0
# missing values
ps_clean %>% filter(is.na(Type)) %>% select(App, Type, Price)
## # A tibble: 0 × 3
## # ℹ 3 variables: App <chr>, Type <chr>, Price <chr>
# fixing missing values using Price
ps_clean <- ps_clean %>%
mutate(Type = case_when(
is.na(Type) & str_detect(Price, "^\\$?0$") ~ "Free",
is.na(Type) & !str_detect(Price, "^\\$?0$") ~ "Paid",
TRUE ~ Type
))
# to factor
ps_clean$Type <- factor(ps_clean$Type, levels = c("Free", "Paid"))
table(ps_clean$Type, useNA = "always")
##
## Free Paid <NA>
## 8904 754 1
## PRICE COLUMN CLEANING
class(ps_clean$Price)
## [1] "character"
unique(ps_clean$Price) %>% head(20)
## [1] "0" "$1.49" "$0.99" "$3.08" "$16.99" "$9.99" "$33.99" "$2.00"
## [9] "$3.04" "$8.99" "$1.99" "$2.49" "$5.00" "$4.99" "$2.99" "$7.99"
## [17] "$29.99" "$3.99" "$19.90" "$2.60"
ps_clean <- ps_clean %>%
mutate(Price_num = str_remove(Price, "\\$") %>% as.numeric())
summary(ps_clean$Price_num)
## Min. 1st Qu. Median Mean 3rd Qu. Max.
## 0.000 0.000 0.000 1.097 0.000 400.000
sum(is.na(ps_clean$Price_num))
## [1] 0
# outliers
ps_clean %>% arrange(desc(Price_num)) %>% select(App, Price_num) %>% head(10)
## # A tibble: 10 × 2
## App Price_num
## <chr> <dbl>
## 1 I'm Rich - Trump Edition 400
## 2 I AM RICH PRO PLUS 400.
## 3 I Am Rich Premium 400.
## 4 I Am Rich Pro 400.
## 5 I am Rich 400.
## 6 I am Rich Plus 400.
## 7 I am Rich! 400.
## 8 I am rich 400.
## 9 I am rich (Most expensive app) 400.
## 10 I am rich(premium) 400.
# logical consistency with Type
ps_clean %>%
filter((Type == "Free" & Price_num > 0) | (Type == "Paid" & Price_num == 0)) %>%
select(App, Type, Price_num)
## # A tibble: 0 × 3
## # ℹ 3 variables: App <chr>, Type <fct>, Price_num <dbl>
## CONTENT.RATING COLUMN CLEANING
class(ps_clean$Content.Rating)
## [1] "character"
unique(ps_clean$Content.Rating)
## [1] "Everyone" "Mature 17+" "Everyone 10+" "Teen"
## [5] "Unrated" "Adults only 18+"
# missing values
sum(is.na(ps_clean$Content.Rating) | ps_clean$Content.Rating == "")
## [1] 0
# whitespace
ps_clean$Content.Rating <- str_trim(ps_clean$Content.Rating)
# to ordered factor
ps_clean <- ps_clean %>%
mutate(Content.Rating = factor(Content.Rating,
levels = c("Everyone", "Everyone 10+",
"Teen", "Mature 17+",
"Adults only 18+", "Unrated"),
ordered = TRUE))
table(ps_clean$Content.Rating, useNA = "always")
##
## Everyone Everyone 10+ Teen Mature 17+ Adults only 18+
## 7903 322 1036 393 3
## Unrated <NA>
## 2 0
## GENRES COLUMN CLEANING
class(ps_clean$Genres)
## [1] "character"
length(unique(ps_clean$Genres))
## [1] 118
# missing values
sum(is.na(ps_clean$Genres) | ps_clean$Genres == "")
## [1] 0
# whitespace
ps_clean$Genres <- str_trim(ps_clean$Genres)
# multi-genre apps
sum(str_detect(ps_clean$Genres, ";"))
## [1] 392
# primary genre
ps_clean <- ps_clean %>%
mutate(Primary_Genre = str_split(Genres, ";", simplify = TRUE)[, 1])
length(unique(ps_clean$Primary_Genre))
## [1] 48
table(ps_clean$Primary_Genre) %>% sort(decreasing = TRUE) %>% head(10)
##
## Tools Entertainment Education Business Medical
## 829 592 580 420 395
## Personalization Productivity Lifestyle Finance Sports
## 376 374 370 345 335
# long format for multi-genre analysis
ps_genres_long <- ps_clean %>% separate_rows(Genres, sep = ";")
## LAST.UPDATED COLUMN CLEANING
class(ps$Last.Updated)
## [1] "character"
head(unique(ps$Last.Updated), 10)
## [1] "January 7, 2018" "January 15, 2018" "August 1, 2018"
## [4] "June 8, 2018" "June 20, 2018" "March 26, 2017"
## [7] "April 26, 2018" "June 14, 2018" "September 20, 2017"
## [10] "July 3, 2018"
# to Date (locale already set to English at the top of the script)
ps_clean <- ps_clean %>%
mutate(Last.Updated = as.Date(Last.Updated, format = "%B %d, %Y"))
sum(is.na(ps_clean$Last.Updated))
## [1] 0
range(ps_clean$Last.Updated, na.rm = TRUE)
## [1] "2010-05-21" "2018-08-08"
# derived variables
ps_clean <- ps_clean %>%
mutate(
Update_Year = year(Last.Updated),
Update_Month = month(Last.Updated, label = TRUE),
Days_Since_Update = as.numeric(Sys.Date() - Last.Updated)
)
## CURRENT.VER COLUMN CLEANING
class(ps_clean$Current.Ver)
## [1] "character"
sum(is.na(ps_clean$Current.Ver))
## [1] 0
sum(ps_clean$Current.Ver == "Varies with device", na.rm = TRUE)
## [1] 1055
# whitespace
ps_clean$Current.Ver <- str_trim(ps_clean$Current.Ver)
# flag "Varies with device"
ps_clean <- ps_clean %>%
mutate(Current_Ver_Varies = Current.Ver == "Varies with device")
# major version number
ps_clean <- ps_clean %>%
mutate(Current_Ver_Major = str_extract(Current.Ver, "^[0-9]+"))
## ANDROID.VER COLUMN CLEANING
class(ps_clean$Android.Ver)
## [1] "character"
sum(is.na(ps_clean$Android.Ver))
## [1] 0
sum(ps_clean$Android.Ver == "Varies with device", na.rm = TRUE)
## [1] 991
# missing values
ps_clean %>% filter(is.na(Android.Ver) | Android.Ver == "") %>% select(App, Android.Ver)
## # A tibble: 0 × 2
## # ℹ 2 variables: App <chr>, Android.Ver <chr>
# whitespace
ps_clean$Android.Ver <- str_trim(ps_clean$Android.Ver)
# flag "Varies with device"
ps_clean <- ps_clean %>%
mutate(Android_Ver_Varies = Android.Ver == "Varies with device")
# minimum version
ps_clean <- ps_clean %>%
mutate(Android_Ver_Min = str_extract(Android.Ver, "^[0-9]+\\.?[0-9]*"))
colSums(is.na(ps_clean)) %>% sort(decreasing = TRUE)
## Rating Size_MB Current_Ver_Major Android_Ver_Min
## 1463 1228 1205 993
## Type App Category Reviews
## 1 0 0 0
## Size Installs Price Content.Rating
## 0 0 0 0
## Genres Last.Updated Current.Ver Android.Ver
## 0 0 0 0
## App_Name_Length Rating_imputed Size_Varies Installs_num
## 0 0 0 0
## Price_num Primary_Genre Update_Year Update_Month
## 0 0 0 0
## Days_Since_Update Current_Ver_Varies Android_Ver_Varies
## 0 0 0
ps_clean %>% filter(is.na(Type)) %>% select(App, Type, Price, Price_num)
## # A tibble: 1 × 4
## App Type Price Price_num
## <chr> <fct> <chr> <dbl>
## 1 Command & Conquer: Rivals <NA> 0 0
ps_clean <- ps_clean %>%
mutate(Type = case_when(
is.na(Type) & Price_num == 0 ~ "Free",
is.na(Type) & Price_num > 0 ~ "Paid",
TRUE ~ Type
))
table(ps_clean$Type, useNA = "always") # NA = 0
##
## Free Paid <NA>
## 8905 754 0
data.frame(
Column = c("Rating", "Size_MB", "Current_Ver_Major", "Android_Ver_Min"),
NA_Count = c(1463, 1228, 1205, 993),
Reason = c("App hasn't received enough ratings yet",
"Size listed as 'Varies with device'",
"Version listed as 'Varies with device' or non-standard format",
"Version listed as 'Varies with device' or missing")
) %>% knitr::kable(caption = "Remaining Missing Values — Explained")
Remaining Missing Values — Explained
| Rating |
1463 |
App hasn’t received enough ratings yet |
| Size_MB |
1228 |
Size listed as ‘Varies with device’ |
| Current_Ver_Major |
1205 |
Version listed as ‘Varies with device’ or non-standard
format |
| Android_Ver_Min |
993 |
Version listed as ‘Varies with device’ or missing |
saveRDS(ps_clean, "ps_clean.rds")