IS 607 - Project 2 The goal of this assignment is to give you practice in preparing different datasets for downstream analysis work. Your task is to:
Choose any three of the “wide” datasets identified in the Week 6 Discussion items. (You may use your own dataset; please don’t use my Sample Post dataset, since that was used in your Week 6 assignment!) For each of the three chosen datasets: ??? Create a .CSV file (or optionally, a MySQL database!) that includes all of the information included in the dataset. You’re encouraged to use a “wide” structure similar to how the information appears in the discussion item, so that you can practice tidying and transformations as described below. ??? Read the information from your .CSV file into R, and use tidyr and dplyr as needed to tidy and transform your data. [Most of your grade will be based on this step!] ??? Perform the analysis requested in the discussion item. ??? Your code should be in an R Markdown file, posted to rpubs.com, and should include narrative descriptions of your data cleanup work, analysis, and conclusions. Please include in your homework submission, for each of the three chosen datasets: ??? The URL to the .Rmd file in your GitHub repository, and ??? The URL for your rpubs.com web page.
Data : https://en.wikipedia.org/wiki/Lionel_Messi
Editing the data for Messi and analyzing goals by year.
library(tidyr)
library(dplyr)##
## Attaching package: 'dplyr'## The following objects are masked from 'package:stats':
##
## filter, lag## The following objects are masked from 'package:base':
##
## intersect, setdiff, setequal, unionlibrary(stringr)Rawwide_data <- read.csv(paste0("https://raw.githubusercontent.com/Fyoun123/Data607/master/Project%202/messi.csv"),stringsAsFactors = F)
Rawwide_data## Club Season Division League.Apps League.Goals
## 1 Barcelona C 2003–04[497] Tercera División 10 5
## 2 Barcelona B 2003–04[41] Segunda División B 5 0
## 3 Barcelona B 2004–05[42] Segunda División B 17 6
## 4 Barcelona 2004–05[42] La Liga 7 1
## 5 Barcelona 2005–06[56] La Liga 17 6
## 6 Barcelona 2006–07[59] La Liga 26 14
## 7 Barcelona 2007–08[74] La Liga 28 10
## 8 Barcelona 2008–09[82] La Liga 31 23
## 9 Barcelona 2009–10[95] La Liga 35 34
## 10 Barcelona 2010–11[111] La Liga 33 31
## 11 Barcelona 2011–12[118] La Liga 37 50
## 12 Barcelona 2012–13[160] La Liga 32 46
## 13 Barcelona 2013–14[168] La Liga 31 28
## 14 Barcelona 2014–15[185] La Liga 38 43
## 15 Barcelona 2015–16[498] La Liga 33 26
## 16 Barcelona 2016–17[499] La Liga 34 37
## 17 Barcelona 2017–18[500] La Liga 36 34
## 18 Barcelona 2018–19[501] La Liga 7 5
## Copa.del.Rey.Apps Copa.del.Rey.Goals Champions.League.Apps
## 1 — NA —
## 2 — NA —
## 3 — NA —
## 4 1 0 1
## 5 2 1 6
## 6 2 2 5
## 7 3 0 9
## 8 8 6 12
## 9 3 1 11
## 10 7 7 13
## 11 7 3 11
## 12 5 4 11
## 13 6 5 7
## 14 6 5 13
## 15 5 5 7
## 16 7 5 9
## 17 6 4 10
## 18 0 0 2
## Champions.League.Goals Other.Apps Other.Goals Total.Apps Total.Goals
## 1 NA — NA 10 5
## 2 NA — NA 5 0
## 3 NA — NA 17 6
## 4 0 — NA 9 1
## 5 1 0 0 25 8
## 6 1 3[a] 0 36 17
## 7 6 — NA 40 16
## 8 9 — NA 51 38
## 9 8 4[b] 4 53 47
## 10 12 2[c] 3 55 53
## 11 14 5[d] 6 60 73
## 12 8 2[c] 2 50 60
## 13 8 2[c] 0 46 41
## 14 10 — NA 57 58
## 15 6 4[e] 4 49 41
## 16 11 2[c] 1 52 54
## 17 6 2[c] 1 54 45
## 18 5 1[c] 0 10 10DF2 <- Rawwide_data %>%
select(Season,League.Goals,Copa.del.Rey.Goals,Champions.League.Goals,Other.Goals);DF2## Season League.Goals Copa.del.Rey.Goals Champions.League.Goals
## 1 2003–04[497] 5 NA NA
## 2 2003–04[41] 0 NA NA
## 3 2004–05[42] 6 NA NA
## 4 2004–05[42] 1 0 0
## 5 2005–06[56] 6 1 1
## 6 2006–07[59] 14 2 1
## 7 2007–08[74] 10 0 6
## 8 2008–09[82] 23 6 9
## 9 2009–10[95] 34 1 8
## 10 2010–11[111] 31 7 12
## 11 2011–12[118] 50 3 14
## 12 2012–13[160] 46 4 8
## 13 2013–14[168] 28 5 8
## 14 2014–15[185] 43 5 10
## 15 2015–16[498] 26 5 6
## 16 2016–17[499] 37 5 11
## 17 2017–18[500] 34 4 6
## 18 2018–19[501] 5 0 5
## Other.Goals
## 1 NA
## 2 NA
## 3 NA
## 4 NA
## 5 0
## 6 0
## 7 NA
## 8 NA
## 9 4
## 10 3
## 11 6
## 12 2
## 13 0
## 14 NA
## 15 4
## 16 1
## 17 1
## 18 0DF3<- DF2 %>% gather(Type,Number,League.Goals:Other.Goals)
DF3## Season Type Number
## 1 2003–04[497] League.Goals 5
## 2 2003–04[41] League.Goals 0
## 3 2004–05[42] League.Goals 6
## 4 2004–05[42] League.Goals 1
## 5 2005–06[56] League.Goals 6
## 6 2006–07[59] League.Goals 14
## 7 2007–08[74] League.Goals 10
## 8 2008–09[82] League.Goals 23
## 9 2009–10[95] League.Goals 34
## 10 2010–11[111] League.Goals 31
## 11 2011–12[118] League.Goals 50
## 12 2012–13[160] League.Goals 46
## 13 2013–14[168] League.Goals 28
## 14 2014–15[185] League.Goals 43
## 15 2015–16[498] League.Goals 26
## 16 2016–17[499] League.Goals 37
## 17 2017–18[500] League.Goals 34
## 18 2018–19[501] League.Goals 5
## 19 2003–04[497] Copa.del.Rey.Goals NA
## 20 2003–04[41] Copa.del.Rey.Goals NA
## 21 2004–05[42] Copa.del.Rey.Goals NA
## 22 2004–05[42] Copa.del.Rey.Goals 0
## 23 2005–06[56] Copa.del.Rey.Goals 1
## 24 2006–07[59] Copa.del.Rey.Goals 2
## 25 2007–08[74] Copa.del.Rey.Goals 0
## 26 2008–09[82] Copa.del.Rey.Goals 6
## 27 2009–10[95] Copa.del.Rey.Goals 1
## 28 2010–11[111] Copa.del.Rey.Goals 7
## 29 2011–12[118] Copa.del.Rey.Goals 3
## 30 2012–13[160] Copa.del.Rey.Goals 4
## 31 2013–14[168] Copa.del.Rey.Goals 5
## 32 2014–15[185] Copa.del.Rey.Goals 5
## 33 2015–16[498] Copa.del.Rey.Goals 5
## 34 2016–17[499] Copa.del.Rey.Goals 5
## 35 2017–18[500] Copa.del.Rey.Goals 4
## 36 2018–19[501] Copa.del.Rey.Goals 0
## 37 2003–04[497] Champions.League.Goals NA
## 38 2003–04[41] Champions.League.Goals NA
## 39 2004–05[42] Champions.League.Goals NA
## 40 2004–05[42] Champions.League.Goals 0
## 41 2005–06[56] Champions.League.Goals 1
## 42 2006–07[59] Champions.League.Goals 1
## 43 2007–08[74] Champions.League.Goals 6
## 44 2008–09[82] Champions.League.Goals 9
## 45 2009–10[95] Champions.League.Goals 8
## 46 2010–11[111] Champions.League.Goals 12
## 47 2011–12[118] Champions.League.Goals 14
## 48 2012–13[160] Champions.League.Goals 8
## 49 2013–14[168] Champions.League.Goals 8
## 50 2014–15[185] Champions.League.Goals 10
## 51 2015–16[498] Champions.League.Goals 6
## 52 2016–17[499] Champions.League.Goals 11
## 53 2017–18[500] Champions.League.Goals 6
## 54 2018–19[501] Champions.League.Goals 5
## 55 2003–04[497] Other.Goals NA
## 56 2003–04[41] Other.Goals NA
## 57 2004–05[42] Other.Goals NA
## 58 2004–05[42] Other.Goals NA
## 59 2005–06[56] Other.Goals 0
## 60 2006–07[59] Other.Goals 0
## 61 2007–08[74] Other.Goals NA
## 62 2008–09[82] Other.Goals NA
## 63 2009–10[95] Other.Goals 4
## 64 2010–11[111] Other.Goals 3
## 65 2011–12[118] Other.Goals 6
## 66 2012–13[160] Other.Goals 2
## 67 2013–14[168] Other.Goals 0
## 68 2014–15[185] Other.Goals NA
## 69 2015–16[498] Other.Goals 4
## 70 2016–17[499] Other.Goals 1
## 71 2017–18[500] Other.Goals 1
## 72 2018–19[501] Other.Goals 0DF4 <- DF3 %>% filter(Number>=0) ; DF4## Season Type Number
## 1 2003–04[497] League.Goals 5
## 2 2003–04[41] League.Goals 0
## 3 2004–05[42] League.Goals 6
## 4 2004–05[42] League.Goals 1
## 5 2005–06[56] League.Goals 6
## 6 2006–07[59] League.Goals 14
## 7 2007–08[74] League.Goals 10
## 8 2008–09[82] League.Goals 23
## 9 2009–10[95] League.Goals 34
## 10 2010–11[111] League.Goals 31
## 11 2011–12[118] League.Goals 50
## 12 2012–13[160] League.Goals 46
## 13 2013–14[168] League.Goals 28
## 14 2014–15[185] League.Goals 43
## 15 2015–16[498] League.Goals 26
## 16 2016–17[499] League.Goals 37
## 17 2017–18[500] League.Goals 34
## 18 2018–19[501] League.Goals 5
## 19 2004–05[42] Copa.del.Rey.Goals 0
## 20 2005–06[56] Copa.del.Rey.Goals 1
## 21 2006–07[59] Copa.del.Rey.Goals 2
## 22 2007–08[74] Copa.del.Rey.Goals 0
## 23 2008–09[82] Copa.del.Rey.Goals 6
## 24 2009–10[95] Copa.del.Rey.Goals 1
## 25 2010–11[111] Copa.del.Rey.Goals 7
## 26 2011–12[118] Copa.del.Rey.Goals 3
## 27 2012–13[160] Copa.del.Rey.Goals 4
## 28 2013–14[168] Copa.del.Rey.Goals 5
## 29 2014–15[185] Copa.del.Rey.Goals 5
## 30 2015–16[498] Copa.del.Rey.Goals 5
## 31 2016–17[499] Copa.del.Rey.Goals 5
## 32 2017–18[500] Copa.del.Rey.Goals 4
## 33 2018–19[501] Copa.del.Rey.Goals 0
## 34 2004–05[42] Champions.League.Goals 0
## 35 2005–06[56] Champions.League.Goals 1
## 36 2006–07[59] Champions.League.Goals 1
## 37 2007–08[74] Champions.League.Goals 6
## 38 2008–09[82] Champions.League.Goals 9
## 39 2009–10[95] Champions.League.Goals 8
## 40 2010–11[111] Champions.League.Goals 12
## 41 2011–12[118] Champions.League.Goals 14
## 42 2012–13[160] Champions.League.Goals 8
## 43 2013–14[168] Champions.League.Goals 8
## 44 2014–15[185] Champions.League.Goals 10
## 45 2015–16[498] Champions.League.Goals 6
## 46 2016–17[499] Champions.League.Goals 11
## 47 2017–18[500] Champions.League.Goals 6
## 48 2018–19[501] Champions.League.Goals 5
## 49 2005–06[56] Other.Goals 0
## 50 2006–07[59] Other.Goals 0
## 51 2009–10[95] Other.Goals 4
## 52 2010–11[111] Other.Goals 3
## 53 2011–12[118] Other.Goals 6
## 54 2012–13[160] Other.Goals 2
## 55 2013–14[168] Other.Goals 0
## 56 2015–16[498] Other.Goals 4
## 57 2016–17[499] Other.Goals 1
## 58 2017–18[500] Other.Goals 1
## 59 2018–19[501] Other.Goals 0SeasonY <- c(str_sub(DF4$Season, 1, 7))
Test <- data.frame(SeasonY,DF4$Type,DF4$Number)
Test## SeasonY DF4.Type DF4.Number
## 1 2003–04 League.Goals 5
## 2 2003–04 League.Goals 0
## 3 2004–05 League.Goals 6
## 4 2004–05 League.Goals 1
## 5 2005–06 League.Goals 6
## 6 2006–07 League.Goals 14
## 7 2007–08 League.Goals 10
## 8 2008–09 League.Goals 23
## 9 2009–10 League.Goals 34
## 10 2010–11 League.Goals 31
## 11 2011–12 League.Goals 50
## 12 2012–13 League.Goals 46
## 13 2013–14 League.Goals 28
## 14 2014–15 League.Goals 43
## 15 2015–16 League.Goals 26
## 16 2016–17 League.Goals 37
## 17 2017–18 League.Goals 34
## 18 2018–19 League.Goals 5
## 19 2004–05 Copa.del.Rey.Goals 0
## 20 2005–06 Copa.del.Rey.Goals 1
## 21 2006–07 Copa.del.Rey.Goals 2
## 22 2007–08 Copa.del.Rey.Goals 0
## 23 2008–09 Copa.del.Rey.Goals 6
## 24 2009–10 Copa.del.Rey.Goals 1
## 25 2010–11 Copa.del.Rey.Goals 7
## 26 2011–12 Copa.del.Rey.Goals 3
## 27 2012–13 Copa.del.Rey.Goals 4
## 28 2013–14 Copa.del.Rey.Goals 5
## 29 2014–15 Copa.del.Rey.Goals 5
## 30 2015–16 Copa.del.Rey.Goals 5
## 31 2016–17 Copa.del.Rey.Goals 5
## 32 2017–18 Copa.del.Rey.Goals 4
## 33 2018–19 Copa.del.Rey.Goals 0
## 34 2004–05 Champions.League.Goals 0
## 35 2005–06 Champions.League.Goals 1
## 36 2006–07 Champions.League.Goals 1
## 37 2007–08 Champions.League.Goals 6
## 38 2008–09 Champions.League.Goals 9
## 39 2009–10 Champions.League.Goals 8
## 40 2010–11 Champions.League.Goals 12
## 41 2011–12 Champions.League.Goals 14
## 42 2012–13 Champions.League.Goals 8
## 43 2013–14 Champions.League.Goals 8
## 44 2014–15 Champions.League.Goals 10
## 45 2015–16 Champions.League.Goals 6
## 46 2016–17 Champions.League.Goals 11
## 47 2017–18 Champions.League.Goals 6
## 48 2018–19 Champions.League.Goals 5
## 49 2005–06 Other.Goals 0
## 50 2006–07 Other.Goals 0
## 51 2009–10 Other.Goals 4
## 52 2010–11 Other.Goals 3
## 53 2011–12 Other.Goals 6
## 54 2012–13 Other.Goals 2
## 55 2013–14 Other.Goals 0
## 56 2015–16 Other.Goals 4
## 57 2016–17 Other.Goals 1
## 58 2017–18 Other.Goals 1
## 59 2018–19 Other.Goals 0Performance <- Test %>% group_by(SeasonY) %>% summarise(Total = sum(DF4.Number));Performance## # A tibble: 16 x 2
## SeasonY Total
## <fct> <int>
## 1 2003–04 5
## 2 2004–05 7
## 3 2005–06 8
## 4 2006–07 17
## 5 2007–08 16
## 6 2008–09 38
## 7 2009–10 47
## 8 2010–11 53
## 9 2011–12 73
## 10 2012–13 60
## 11 2013–14 41
## 12 2014–15 58
## 13 2015–16 41
## 14 2016–17 54
## 15 2017–18 45
## 16 2018–19 10