{"id":317,"date":"2023-10-18T13:09:50","date_gmt":"2023-10-18T17:09:50","guid":{"rendered":"https:\/\/pressbooks.bccampus.ca\/businessanalytics\/chapter\/fun-soccer-worksheet\/"},"modified":"2023-10-18T17:09:49","modified_gmt":"2023-10-18T21:09:49","slug":"fun-soccer-worksheet","status":"publish","type":"chapter","link":"https:\/\/pressbooks.bccampus.ca\/businessanalytics\/chapter\/fun-soccer-worksheet\/","title":{"raw":"Data Wrangling: A case Study","rendered":"Data Wrangling: A case Study"},"content":{"raw":"
\r\n
\r\n

Case Study: What uniform pieces are we waiting for?<\/h1>\r\nA volunteer with a children's soccer team was trying to keep track of which child ordered which piece of equipment, what the size was, and whether the item was received.\u00a0 The team uses Excel, and the following Excel file was received:\r\n\r\n<\/div>\r\n \r\n
\r\n\r\n[embed]https:\/\/onedrive.live.com\/embed?resid=3700FC25C4CB2CE9%21753&authkey=%21AL3kRflgotiNEt8&em=2&wdAllowInteractivity=False&wdHideGridlines=True&wdHideHeaders=True&wdDownloadButton=True&wdInConfigurator=True&wdInConfigurator=True[\/embed]\r\n\r\n<\/div>\r\n
\r\n
<\/div>\r\n<\/div>\r\n
\r\n
\r\n\r\n \r\n\r\n<\/div>\r\n[NOTE: INSERT EXCEL SHEET]\r\n\r\n[caption id=\"attachment_318\" align=\"aligncenter\" width=\"357\"]\"screen This is the original wide data[\/caption]\r\n\r\nNote that this is a well organized table, but using it, it's difficult to see how many pairs of Youth Median (YM) black shorts are still outstanding.\u00a0 This is a job for data wrangling - organizing the data in a way that makes our work clearer.\r\n

Tables should be long - not wide<\/h2>\r\n<\/div>\r\nThis table is short and wide.\u00a0 There is a row for each child (identified by their jersey number) and columns for each item, item size, and status (received or not).\u00a0 The solution in this case was to wrangle this data in R, creating a long but narrow table.\r\n\r\n \r\n
\r\n
\r\n

Open the libraries we will need:<\/h2>\r\nIn the boxes you will find all of the R code needed for this project.\u00a0 Note that that this supposes that we have the `soccer.xlsx` file in our working directory\r\n
\u00a0<\/code><\/pre>\r\n
\r\n
\r\n
\r\nlibrary(dplyr) ## dplyr is for working with data frames \r\n<\/code>library(openxlsx) ## lets us liaise with Excel\r\nlibrary(stringr) ## Extract words to make it faster!\r\n\r\n## read in the data\u00a0\r\n<\/code>df <- read.xlsx(\"soccer.xlsx\", sheet = \"Detailorders\", startRow = 2)<\/pre>\r\n<\/div>\r\n<\/div>\r\n
<\/code><\/pre>\r\n
## Inspecting the data<\/h2>\r\nExploratory Data Analysis (EDA) is a fancy word for what we naturally do:\u00a0 look at the data and explore.\u00a0 Here's what happens when we look around.\u00a0 First we look at what the structure of the data is:\r\n\r\n<\/div>\r\n
\r\n
<\/code><\/pre>\r\n
\r\n
df %>% str()<\/span><\/code><\/span><\/p>\r\n\r\n<\/header>\r\n
\r\n
<\/code><\/pre>\r\n
<\/code><\/pre>\r\n
## 'data.frame':    12 obs. of  28 variables:\r\n##  $ Player               : chr  \"1\" \"3\" \"5\" \"8\" ...\r\n##  $ Training.Jersey.Size : chr  \"YL\" \"YM\" \"YL\" \"YL\" ...\r\n##  $ Jersey.Received      : chr  \"Yes\" \"Yes\" \"Yes\" \"Yes\" ...\r\n##  $ Training.Short       : chr  \"YL\" \"YL\" \"YL\" \"YM\" ...\r\n##  $ Shorts.Received      : chr  \"Yes\" \"Yes\" \"Yes\" \"Yes\" ...\r\n##  $ Training.Sock        : chr  \"SM\" \"XS\" \"SM\" \"SM\" ...\r\n##  $ Socks.Received       : chr  \"Yes\" \"Yes\" \"Yes\" \"Yes\" ...\r\n##  $ Jersey.Grey          : chr  NA \"YL\" \"YXL\" \"YL\" ...\r\n##  $ Jersey.Grey.Received : chr  NA \"No\" \"No\" \"No\" ...\r\n##  $ Jersey.White         : chr  NA \"YL\" \"YXL\" \"YL\" ...\r\n##  $ Jersey.White.Received: chr  NA \"No\" \"No\" \"No\" ...\r\n##  $ Short.Black          : chr  NA \"YL\" \"YL\" \"YM\" ...\r\n##  $ Short.Received       : chr  NA \"No\" \"No\" \"No\" ...\r\n##  $ Black.Sock           : chr  NA \"XS\" \"SM\" \"SM\" ...\r\n##  $ Black.Sock.Received  : chr  NA \"No\" \"No\" \"No\" ...\r\n##  $ Track.Jacket         : chr  \"YL\" \"YL\" \"YXL\" \"YL\" ...\r\n##  $ Track.Jacket.Received: chr  \"No\" \"No\" \"No\" \"No\" ...\r\n##  $ Track.Pant           : chr  \"YL\" \"YL\" \"YL\" \"YL\" ...\r\n##  $ Track.Pant.Received  : chr  \"No\" \"No\" \"No\" \"No\" ...\r\n##  $ Cinch.Pack           : chr  \"Ordered \" \"Ordered \" \"Ordered \" \"Ordered \" ...\r\n##  $ Cinch.Pack.Received  : chr  \"No\" \"No\" \"No\" \"No\" ...\r\n##  $ GK.Jersey            : chr  \"YL\" NA NA NA ...\r\n##  $ GK.Jersey.Received   : chr  \"No\" \"No\" \"No\" \"No\" ...\r\n##  $ GK.Short             : chr  \"YL\" NA NA NA ...\r\n##  $ GK.Short.Received    : chr  \"No\" \"No\" \"No\" \"No\" ...\r\n##  $ GK.Socks             : chr  \"SM\" NA NA NA ...\r\n##  $ GK.Sock.Received     : chr  \"No\" \"No\" \"No\" \"No\" ...\r\n##  $ Comments             : chr  NA NA NA \" \" ...<\/code><\/pre>\r\n
<\/code><\/pre>\r\n<\/div>\r\n<\/div>\r\n
<\/code><\/pre>\r\n\u00a0<\/code>\r\n\r\nWe will also take a minute to look at the data frame that we have:\r\n
\r\n
df<\/span> <\/code><\/p>\r\n\r\n<\/header>\r\n
\r\n
\r\n
\r\n
\r\n
##    Player Training.Jersey.Size Jersey.Received Training.Short Shorts.Received\r\n<\/code><\/div>\r\n
## 1       1                   YL             Yes             YL             Yes\r\n<\/code><\/div>\r\n
## 2       3                   YM             Yes             YL             Yes\r\n<\/code><\/div>\r\n
## 3       5                   YL             Yes             YL             Yes\r\n<\/code><\/div>\r\n
## 4       8                   YL             Yes             YM             Yes\r\n<\/code><\/div>\r\n
## 5       9                  YXL              No             YL             Yes\r\n<\/code><\/div>\r\n
## 6      10                   YM             Yes             YM             Yes\r\n<\/code><\/div>\r\n
## 7       2                   YL             Yes             YL             Yes\r\n<\/code><\/div>\r\n
## 8       4                  YXL             Yes            YXL             Yes\r\n<\/code><\/div>\r\n
## 9       6                   YL             Yes            YXL             Yes\r\n<\/code><\/div>\r\n
## 10      7                  YXL              No            YXL             Yes\r\n<\/code><\/div>\r\n
## 11     11                  YXL              No            YXL             Yes\r\n<\/code><\/div>\r\n
## 12     12                   YL             Yes             YL             Yes\r\n<\/code><\/div>\r\n
##    Training.Sock Socks.Received Jersey.Grey Jersey.Grey.Received Jersey.White\r\n## 1             SM            Yes        <NA>                 <NA>         <NA>\r\n## 2             XS            Yes          YL                   No           YL\r\n## 3             SM            Yes         YXL                   No          YXL\r\n<\/code><\/div>\r\n
## 4             SM            Yes          YL                   No           YL\r\n<\/code><\/div>\r\n
## 5             SM            Yes         YXL                   No          YXL\r\n<\/code><\/div>\r\n
## 6             XS            Yes          YL                   No           YL\r\n<\/code><\/div>\r\n
## 7            MED             No         YXL                   No          YXL\r\n<\/code><\/div>\r\n
## 8            MED             No         YXL                   No          YXL\r\n<\/code><\/div>\r\n
## 9            MED             No         YXL                   No          YXL\r\n<\/code><\/div>\r\n
## 10           MED             No         YXL                   No          YXL\r\n<\/code><\/div>\r\n
## 11           MED             No         YXL                   No          YXL\r\n<\/code><\/div>\r\n
## 12            SM             No          YL                   No           YL\r\n<\/code><\/div>\r\n
##    Jersey.White.Received Short.Black Short.Received Black.Sock\r\n<\/code><\/div>\r\n
## 1                   <NA>        <NA>           <NA>       <NA>\r\n<\/code><\/div>\r\n
## 2                     No          YL             No         XS\r\n<\/code><\/div>\r\n
## 3                     No          YL             No         SM\r\n<\/code><\/div>\r\n
## 4                     No          YM             No         SM\r\n<\/code><\/div>\r\n
## 5                     No          YL             No         SM\r\n<\/code><\/div>\r\n
## 6                     No          YM             No         XS\r\n<\/code><\/div>\r\n
## 7                     No          YL             No        MED\r\n<\/code><\/div>\r\n
## 8                     No         YXL             No        MED\r\n<\/code><\/div>\r\n
## 9                     No         YXL             No        MED\r\n<\/code><\/div>\r\n
## 10                    No         YXL             No        MED\r\n<\/code><\/div>\r\n
## 11                    No         YXL             No        MED\r\n<\/code><\/div>\r\n
## 12                    No          YL             No         SM\r\n##    Black.Sock.Received Track.Jacket Track.Jacket.Received Track.Pant\r\n## 1                 <NA>           YL                    No         YL\r\n## 2                   No           YL                    No         YL\r\n## 3                   No          YXL                    No         YL\r\n## 4                   No           YL                    No         YL\r\n## 5                   No           YL                    No         YL\r\n## 6                   No           YL                    No         YM\r\n## 7                   No           YL                    No         YL\r\n## 8                   No           YL                    No         YL\r\n## 9                   No          YXL                    No         YL\r\n## 10                  No          YXL                    No         YL\r\n## 11                  No          YXL                    No        YXL\r\n## 12                  No           YM                    No         YL\r\n##    Track.Pant.Received Cinch.Pack Cinch.Pack.Received GK.Jersey\r\n## 1                   No   Ordered                   No        YL\r\n## 2                   No   Ordered                   No      <NA>\r\n## 3                   No   Ordered                   No      <NA>\r\n## 4                   No   Ordered                   No      <NA>\r\n## 5                   No   Ordered                   No      <NA>\r\n## 6                   No   Ordered                   No      <NA>\r\n## 7                   No   Ordered                   No      <NA>\r\n## 8                   No   Ordered                   No      <NA>\r\n## 9                   No   Ordered                   No      <NA>\r\n## 10                  No   Ordered                   No      <NA>\r\n## 11                  No   Ordered                   No      <NA>\r\n## 12                  No   Ordered                   No      <NA>\r\n##    GK.Jersey.Received GK.Short GK.Short.Received GK.Socks GK.Sock.Received\r\n## 1                  No       YL                No       SM               No\r\n## 2                  No     <NA>                No     <NA>               No\r\n## 3                  No     <NA>                No     <NA>               No\r\n## 4                  No     <NA>                No     <NA>               No\r\n## 5                  No     <NA>                No     <NA>               No\r\n## 6                  No     <NA>                No     <NA>               No\r\n## 7                  No     <NA>                No     <NA>               No\r\n## 8                  No     <NA>                No     <NA>               No\r\n## 9                  No     <NA>                No     <NA>               No\r\n## 10                 No     <NA>                No     <NA>               No\r\n## 11                 No     <NA>                No     <NA>               No\r\n## 12                 No     <NA>                No     <NA>               No\r\n##                                    Comments\r\n## 1                                      <NA>\r\n## 2                                      <NA>\r\n## 3                                      <NA>\r\n## 4\r\n## 5                     Never received jersey\r\n## 6                                      <NA>\r\n## 7                         Need size 2 socks\r\n## 8                         Need size 2 socks\r\n## 9                         Need size 2 socks\r\n## 10  Never received jersey , Need Siz 2 sock\r\n## 11 Never received jersey, need size 2 socks\r\n## 12                        Need size 2 socks<\/code><\/div>\r\n<\/div>\r\n
\r\n
<\/code> <\/span><\/div>\r\n<\/div>\r\n<\/div>\r\n<\/div>\r\n
\r\n
<\/code> <\/span><\/div>\r\n<\/div>\r\n
\r\n
<\/div>\r\n<\/div>\r\n
<\/div>\r\n<\/div>\r\n<\/div>\r\n \r\n
<\/code><\/pre>\r\n<\/div>\r\n
\r\n
Reshape the data:<\/h2>\r\nWe want to create a table that is narrow (not too many columns) and long (many rows).\u00a0 The way to do this is to create a row for each piece of equipment ordered.\u00a0 In the end we should have data that looks like this:\r\n\r\n\r\n\r\n\r\n\r\n\r\n
Player<\/th>\r\nItem<\/th>\r\nSize<\/th>\r\nReceived<\/th>\r\n<\/tr>\r\n<\/thead>\r\n
1<\/td>\r\nJersey<\/td>\r\nYL<\/td>\r\nYes<\/td>\r\n<\/tr>\r\n
7<\/td>\r\nSocks<\/td>\r\nMed<\/td>\r\nNo<\/td>\r\n<\/tr>\r\n<\/tbody>\r\n<\/table>\r\nThere are many ways to do this, but here is one method:\r\n
<\/code><\/pre>\r\n
\r\n
## make a string of names<\/span> <\/code><\/span><\/h2>\r\n
col_names <- colnames(df)<\/span> <\/code><\/span><\/p>\r\n\r\n
## cut the entry level stuff:<\/span> <\/code><\/span><\/h2>\r\n
interm_list <- col_names[6:31]<\/span><\/code><\/span><\/p>\r\n
 interm_list<\/span><\/code><\/p>\r\n\r\n<\/header>\r\n
\r\n
\u00a0<\/code><\/pre>\r\n
##  [1] \"Training.Jersey.Size\"  \"Jersey.Received\"      \r\n##  [3] \"Training.Short\"        \"Shorts.Received\"      \r\n##  [5] \"Training.Sock\"         \"Socks.Received\"       \r\n##  [7] \"Jersey.Grey\"           \"Jersey.Grey.Received\" \r\n##  [9] \"Jersey.White\"          \"Jersey.White.Received\"\r\n## [11] \"Short.Black\"           \"Short.Received\"       \r\n## [13] \"Black.Sock\"            \"Black.Sock.Received\"  \r\n## [15] \"Track.Jacket\"          \"Track.Jacket.Received\"\r\n## [17] \"Track.Pant\"            \"Track.Pant.Received\"  \r\n## [19] \"Cinch.Pack\"            \"Cinch.Pack.Received\"  \r\n## [21] \"GK.Jersey\"             \"GK.Jersey.Received\"   \r\n## [23] \"GK.Short\"              \"GK.Short.Received\"    \r\n## [25] \"GK.Socks\"              \"GK.Sock.Received\"<\/code><\/pre>\r\n<\/div>\r\n<\/div>\r\n
<\/code><\/pre>\r\n \r\n
\u00a0<\/code><\/span><\/pre>\r\n
<\/code><\/span><\/pre>\r\n
<\/code><\/span><\/pre>\r\n
## What are the sizes?<\/span><\/code><\/span>\r\n
size_list <- c() <\/span><\/code><\/p>\r\n
rec_list <- c() <\/span><\/code><\/p>\r\n
for (i in 1:13){ <\/span><\/code><\/p>\r\n
\u00a0\u00a0\u00a0 size_list <- c(size_list,interm_list[2*i-1]) <\/span><\/code><\/p>\r\n
\u00a0 \u00a0\u00a0 \u00a0\u00a0 \u00a0\u00a0 rec_list <- c(rec_list,interm_list[2*i]) <\/span><\/code><\/p>\r\n
} <\/span><\/code><\/p>\r\n
item_list <- str_remove(rec_list,\".Received\") <\/span><\/code><\/p>\r\n\r\n
## Make a blank dataframe<\/span><\/code><\/h2>\r\n
Soccer_df <- data.frame(\"Player\"=numeric(), \"Item\"= character(), \"Size\"=character(), \"Recieved\"=character())<\/span><\/code><\/p>\r\nfor (i in 1:13){ <\/span><\/code><\/span>\r\n
newdf <- df %>% <\/span><\/code><\/span><\/p>\r\n
select(Player, size_list[i], rec_list[i]) <\/span><\/code><\/span><\/p>\r\n
names(newdf) <- c(\"Player\", \"Size\", \"Received\")<\/span> <\/code><\/span><\/p>\r\n
newdf$Item <- item_list[i] newdf <- newdf %>% <\/span><\/code><\/span><\/p>\r\n
select(Player, Item, Size, Received) <\/span><\/code><\/span><\/p>\r\n
Soccer_df <- rbind(Soccer_df, newdf) <\/span><\/code><\/span><\/p>\r\n
}<\/span><\/code><\/span><\/p>\r\n\r\n
Inspect the first 5 rows:\r\n<\/span><\/code><\/h2>\r\nSoccer_df %>% head()<\/span><\/code>\r\n\r\n \r\n\r\n<\/header>\r\n
\r\n
\r\n<\/code><\/pre>\r\n
##   Player   Item Size Received\r\n## 1      1 Jersey   YL      Yes\r\n## 2      3 Jersey   YM      Yes\r\n## 3      5 Jersey   YL      Yes\r\n## 4      8 Jersey   YL      Yes\r\n## 5      9 Jersey  YXL       No\r\n## 6     10 Jersey   YM      Yes<\/code><\/pre>\r\n<\/div>\r\n
<\/div>\r\n<\/div>\r\n \r\n
\u00a0<\/code>\r\n\r\n<\/code><\/code><\/pre>\r\nNow we will look at grouping the data.\r\n\r\nI filtered out anything where the size was \u201cNA\u201d, as we assume that means that there is no item ordered.\r\n
<\/code><\/pre>\r\n
\r\n
summary <- Soccer_df %>%\r\nfilter(!is.na(Size)) <\/span><\/code>\r\n\r\n
%>%\r\ngroup_by(Item, Size, Received) <\/span><\/code>\r\n\r\n
%>%\r\nsummarize(Count = n())<\/span><\/code>\r\n\r\n \r\n\r\n
to_save <- Soccer_df <\/span><\/code><\/p>\r\n
%>%\r\nfilter(!is.na(Size)) <\/span><\/code>\r\n\r\n
%>%\r\ngroup_by(Item, Size, Received) <\/span><\/code>\r\n\r\n
%>%\r\nsummarize(Count = n()) <\/span><\/code>\r\n\r\n
%>%\r\nfilter(Received == \"No\")<\/span><\/code>\r\n\r\n \r\n\r\n \r\n\r\n
to_save<\/span><\/code><\/p>\r\n\r\n<\/header>\r\n
\r\n
\u00a0<\/code><\/pre>\r\n
## # A tibble: 23 x 4\r\n## # Groups:   Item, Size [23]\r\n##    Item        Size       Received Count\r\n##    <chr>       <chr>      <chr>    <int>\r\n##  1 Black.Sock  MED        No           5\r\n##  2 Black.Sock  SM         No           4\r\n##  3 Black.Sock  XS         No           2\r\n##  4 Cinch.Pack  \"Ordered \" No          12\r\n##  5 GK.Jersey   YL         No           1\r\n##  6 GK.Short    YL         No           1\r\n##  7 GK.Sock     SM         No           1\r\n##  8 Jersey      YXL        No           3\r\n##  9 Jersey.Grey YL         No           4\r\n## 10 Jersey.Grey YXL        No           7\r\n## # ... with 13 more rows<\/code><\/pre>\r\n<\/div>\r\n<\/div>\r\n
<\/code><\/pre>\r\n \r\n
<\/code><\/pre>\r\n<\/div>\r\n
\r\n
Save<\/h2>\r\nFinally, we cansave that file as a new Excel sheet - or you could mkae it an old Excel Sheet, whatever!\r\n
\r\n
Learning Objectives<\/p>\r\n\r\n<\/header>\r\n
\r\n
file_name <- paste0(\"Soccer_\",Sys.Date(),\".xlsx\")\r\nfile_name<\/code><\/pre>\r\n
## [1] \"Soccer_2022-04-08.xlsx\"<\/code><\/pre>\r\n
wb <- createWorkbook(\"Soccer_Gear\")\r\n\r\naddWorksheet(wb, sheetName = \"original\")\r\nwriteData(wb, sheet = \"original\", x = df)\r\n\r\naddWorksheet(wb, sheetName = \"Not_Received\")\r\nwriteData(wb, sheet = \"Not_Received\", x = to_save)\r\n\r\naddWorksheet(wb, sheetName = \"Summary\")\r\nwriteData(wb, sheet = \"Summary\", x = summary)\r\n\r\n\r\n\r\nsaveWorkbook(wb, file_name, overwrite = TRUE)<\/code><\/pre>\r\n
<\/code><\/pre>\r\n<\/div>\r\n<\/div>\r\n \r\n\r\n<\/code>\r\n\r\n<\/div>\r\n<\/div>\r\n<\/div>","rendered":"
\n
\n
Case Study: What uniform pieces are we waiting for?<\/h1>\n
A volunteer with a children’s soccer team was trying to keep track of which child ordered which piece of equipment, what the size was, and whether the item was received.\u00a0 The team uses Excel, and the following Excel file was received:<\/p>\n<\/div>\n
 <\/p>\n
\n
https:\/\/onedrive.live.com\/embed?resid=3700FC25C4CB2CE9%21753&authkey=%21AL3kRflgotiNEt8&em=2&wdAllowInteractivity=False&wdHideGridlines=True&wdHideHeaders=True&wdDownloadButton=True&wdInConfigurator=True&wdInConfigurator=True<\/a><\/p>\n<\/div>\n
\n
<\/div>\n<\/div>\n
\n
\n
 <\/p>\n<\/div>\n
[NOTE: INSERT EXCEL SHEET]<\/p>\n
\"screen
This is the original wide data<\/figcaption><\/figure>\n
Note that this is a well organized table, but using it, it’s difficult to see how many pairs of Youth Median (YM) black shorts are still outstanding.\u00a0 This is a job for data wrangling – organizing the data in a way that makes our work clearer.<\/p>\n
Tables should be long – not wide<\/h2>\n<\/div>\n
This table is short and wide.\u00a0 There is a row for each child (identified by their jersey number) and columns for each item, item size, and status (received or not).\u00a0 The solution in this case was to wrangle this data in R, creating a long but narrow table.<\/p>\n
 <\/p>\n
\n
\n
Open the libraries we will need:<\/h2>\n
In the boxes you will find all of the R code needed for this project.\u00a0 Note that that this supposes that we have the `soccer.xlsx` file in our working directory<\/p>\n
\u00a0<\/code><\/pre>\n
\n
\n
\r\nlibrary(dplyr) ## dplyr is for working with data frames \r\n<\/code>library(openxlsx) ## lets us liaise with Excel\r\nlibrary(stringr) ## Extract words to make it faster!\r\n\r\n## read in the data\u00a0\r\n<\/code>df <- read.xlsx(\"soccer.xlsx\", sheet = \"Detailorders\", startRow = 2)<\/pre>\n<\/div>\n<\/div>\n
<\/code><\/pre>\n
## Inspecting the data<\/h2>\n
Exploratory Data Analysis (EDA) is a fancy word for what we naturally do:\u00a0 look at the data and explore.\u00a0 Here’s what happens when we look around.\u00a0 First we look at what the structure of the data is:<\/p>\n<\/div>\n
\n
<\/code><\/pre>\n
\n
\n
df %>% str()<\/span><\/code><\/span><\/p>\n<\/header>\n
\n
<\/code><\/pre>\n
<\/code><\/pre>\n
## 'data.frame':    12 obs. of  28 variables:\r\n##  $ Player               : chr  \"1\" \"3\" \"5\" \"8\" ...\r\n##  $ Training.Jersey.Size : chr  \"YL\" \"YM\" \"YL\" \"YL\" ...\r\n##  $ Jersey.Received      : chr  \"Yes\" \"Yes\" \"Yes\" \"Yes\" ...\r\n##  $ Training.Short       : chr  \"YL\" \"YL\" \"YL\" \"YM\" ...\r\n##  $ Shorts.Received      : chr  \"Yes\" \"Yes\" \"Yes\" \"Yes\" ...\r\n##  $ Training.Sock        : chr  \"SM\" \"XS\" \"SM\" \"SM\" ...\r\n##  $ Socks.Received       : chr  \"Yes\" \"Yes\" \"Yes\" \"Yes\" ...\r\n##  $ Jersey.Grey          : chr  NA \"YL\" \"YXL\" \"YL\" ...\r\n##  $ Jersey.Grey.Received : chr  NA \"No\" \"No\" \"No\" ...\r\n##  $ Jersey.White         : chr  NA \"YL\" \"YXL\" \"YL\" ...\r\n##  $ Jersey.White.Received: chr  NA \"No\" \"No\" \"No\" ...\r\n##  $ Short.Black          : chr  NA \"YL\" \"YL\" \"YM\" ...\r\n##  $ Short.Received       : chr  NA \"No\" \"No\" \"No\" ...\r\n##  $ Black.Sock           : chr  NA \"XS\" \"SM\" \"SM\" ...\r\n##  $ Black.Sock.Received  : chr  NA \"No\" \"No\" \"No\" ...\r\n##  $ Track.Jacket         : chr  \"YL\" \"YL\" \"YXL\" \"YL\" ...\r\n##  $ Track.Jacket.Received: chr  \"No\" \"No\" \"No\" \"No\" ...\r\n##  $ Track.Pant           : chr  \"YL\" \"YL\" \"YL\" \"YL\" ...\r\n##  $ Track.Pant.Received  : chr  \"No\" \"No\" \"No\" \"No\" ...\r\n##  $ Cinch.Pack           : chr  \"Ordered \" \"Ordered \" \"Ordered \" \"Ordered \" ...\r\n##  $ Cinch.Pack.Received  : chr  \"No\" \"No\" \"No\" \"No\" ...\r\n##  $ GK.Jersey            : chr  \"YL\" NA NA NA ...\r\n##  $ GK.Jersey.Received   : chr  \"No\" \"No\" \"No\" \"No\" ...\r\n##  $ GK.Short             : chr  \"YL\" NA NA NA ...\r\n##  $ GK.Short.Received    : chr  \"No\" \"No\" \"No\" \"No\" ...\r\n##  $ GK.Socks             : chr  \"SM\" NA NA NA ...\r\n##  $ GK.Sock.Received     : chr  \"No\" \"No\" \"No\" \"No\" ...\r\n##  $ Comments             : chr  NA NA NA \" \" ...<\/code><\/pre>\n
<\/code><\/pre>\n<\/div>\n<\/div>\n
<\/code><\/pre>\n
\u00a0<\/code><\/p>\n
We will also take a minute to look at the data frame that we have:<\/p>\n
\n
\n
df<\/span> <\/code><\/p>\n<\/header>\n
\n
\n
\n
\n
##    Player Training.Jersey.Size Jersey.Received Training.Short Shorts.Received
\n<\/code><\/div>\n
## 1       1                   YL             Yes             YL             Yes
\n<\/code><\/div>\n
## 2       3                   YM             Yes             YL             Yes
\n<\/code><\/div>\n
## 3       5                   YL             Yes             YL             Yes
\n<\/code><\/div>\n
## 4       8                   YL             Yes             YM             Yes
\n<\/code><\/div>\n
## 5       9                  YXL              No             YL             Yes
\n<\/code><\/div>\n
## 6      10                   YM             Yes             YM             Yes
\n<\/code><\/div>\n
## 7       2                   YL             Yes             YL             Yes
\n<\/code><\/div>\n
## 8       4                  YXL             Yes            YXL             Yes
\n<\/code><\/div>\n
## 9       6                   YL             Yes            YXL             Yes
\n<\/code><\/div>\n
## 10      7                  YXL              No            YXL             Yes
\n<\/code><\/div>\n
## 11     11                  YXL              No            YXL             Yes
\n<\/code><\/div>\n
## 12     12                   YL             Yes             YL             Yes
\n<\/code><\/div>\n
##    Training.Sock Socks.Received Jersey.Grey Jersey.Grey.Received Jersey.White
\n## 1             SM            Yes        <NA>                 <NA>         <NA>
\n## 2             XS            Yes          YL                   No           YL
\n## 3             SM            Yes         YXL                   No          YXL
\n<\/code><\/div>\n
## 4             SM            Yes          YL                   No           YL
\n<\/code><\/div>\n
## 5             SM            Yes         YXL                   No          YXL
\n<\/code><\/div>\n
## 6             XS            Yes          YL                   No           YL
\n<\/code><\/div>\n
## 7            MED             No         YXL                   No          YXL
\n<\/code><\/div>\n
## 8            MED             No         YXL                   No          YXL
\n<\/code><\/div>\n
## 9            MED             No         YXL                   No          YXL
\n<\/code><\/div>\n
## 10           MED             No         YXL                   No          YXL
\n<\/code><\/div>\n
## 11           MED             No         YXL                   No          YXL
\n<\/code><\/div>\n
## 12            SM             No          YL                   No           YL
\n<\/code><\/div>\n
##    Jersey.White.Received Short.Black Short.Received Black.Sock
\n<\/code><\/div>\n
## 1                   <NA>        <NA>           <NA>       <NA>
\n<\/code><\/div>\n
## 2                     No          YL             No         XS
\n<\/code><\/div>\n
## 3                     No          YL             No         SM
\n<\/code><\/div>\n
## 4                     No          YM             No         SM
\n<\/code><\/div>\n
## 5                     No          YL             No         SM
\n<\/code><\/div>\n
## 6                     No          YM             No         XS
\n<\/code><\/div>\n
## 7                     No          YL             No        MED
\n<\/code><\/div>\n
## 8                     No         YXL             No        MED
\n<\/code><\/div>\n
## 9                     No         YXL             No        MED
\n<\/code><\/div>\n
## 10                    No         YXL             No        MED
\n<\/code><\/div>\n
## 11                    No         YXL             No        MED
\n<\/code><\/div>\n
## 12                    No          YL             No         SM
\n##    Black.Sock.Received Track.Jacket Track.Jacket.Received Track.Pant
\n## 1                 <NA>           YL                    No         YL
\n## 2                   No           YL                    No         YL
\n## 3                   No          YXL                    No         YL
\n## 4                   No           YL                    No         YL
\n## 5                   No           YL                    No         YL
\n## 6                   No           YL                    No         YM
\n## 7                   No           YL                    No         YL
\n## 8                   No           YL                    No         YL
\n## 9                   No          YXL                    No         YL
\n## 10                  No          YXL                    No         YL
\n## 11                  No          YXL                    No        YXL
\n## 12                  No           YM                    No         YL
\n##    Track.Pant.Received Cinch.Pack Cinch.Pack.Received GK.Jersey
\n## 1                   No   Ordered                   No        YL
\n## 2                   No   Ordered                   No      <NA>
\n## 3                   No   Ordered                   No      <NA>
\n## 4                   No   Ordered                   No      <NA>
\n## 5                   No   Ordered                   No      <NA>
\n## 6                   No   Ordered                   No      <NA>
\n## 7                   No   Ordered                   No      <NA>
\n## 8                   No   Ordered                   No      <NA>
\n## 9                   No   Ordered                   No      <NA>
\n## 10                  No   Ordered                   No      <NA>
\n## 11                  No   Ordered                   No      <NA>
\n## 12                  No   Ordered                   No      <NA>
\n##    GK.Jersey.Received GK.Short GK.Short.Received GK.Socks GK.Sock.Received
\n## 1                  No       YL                No       SM               No
\n## 2                  No     <NA>                No     <NA>               No
\n## 3                  No     <NA>                No     <NA>               No
\n## 4                  No     <NA>                No     <NA>               No
\n## 5                  No     <NA>                No     <NA>               No
\n## 6                  No     <NA>                No     <NA>               No
\n## 7                  No     <NA>                No     <NA>               No
\n## 8                  No     <NA>                No     <NA>               No
\n## 9                  No     <NA>                No     <NA>               No
\n## 10                 No     <NA>                No     <NA>               No
\n## 11                 No     <NA>                No     <NA>               No
\n## 12                 No     <NA>                No     <NA>               No
\n##                                    Comments
\n## 1                                      <NA>
\n## 2                                      <NA>
\n## 3                                      <NA>
\n## 4
\n## 5                     Never received jersey
\n## 6                                      <NA>
\n## 7                         Need size 2 socks
\n## 8                         Need size 2 socks
\n## 9                         Need size 2 socks
\n## 10  Never received jersey , Need Siz 2 sock
\n## 11 Never received jersey, need size 2 socks
\n## 12                        Need size 2 socks<\/code><\/div>\n<\/div>\n
\n
<\/code> <\/span><\/div>\n<\/div>\n<\/div>\n<\/div>\n
\n
<\/code> <\/span><\/div>\n<\/div>\n
\n
<\/div>\n<\/div>\n
<\/div>\n<\/div>\n<\/div>\n
 <\/p>\n
<\/code><\/pre>\n<\/div>\n
\n
Reshape the data:<\/h2>\n
We want to create a table that is narrow (not too many columns) and long (many rows).\u00a0 The way to do this is to create a row for each piece of equipment ordered.\u00a0 In the end we should have data that looks like this:<\/p>\n\n\n\n\n\n\n
Player<\/th>\nItem<\/th>\nSize<\/th>\nReceived<\/th>\n<\/tr>\n<\/thead>\n
1<\/td>\nJersey<\/td>\nYL<\/td>\nYes<\/td>\n<\/tr>\n
7<\/td>\nSocks<\/td>\nMed<\/td>\nNo<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
There are many ways to do this, but here is one method:<\/p>\n
<\/code><\/pre>\n
\n
\n
## make a string of names<\/span> <\/code><\/span><\/h2>\n
col_names <- colnames(df)<\/span> <\/code><\/span><\/p>\n
## cut the entry level stuff:<\/span> <\/code><\/span><\/h2>\n
interm_list <- col_names[6:31]<\/span><\/code><\/span><\/p>\n
 interm_list<\/span><\/code><\/p>\n<\/header>\n
\n
\u00a0<\/code><\/pre>\n
##  [1] \"Training.Jersey.Size\"  \"Jersey.Received\"      \r\n##  [3] \"Training.Short\"        \"Shorts.Received\"      \r\n##  [5] \"Training.Sock\"         \"Socks.Received\"       \r\n##  [7] \"Jersey.Grey\"           \"Jersey.Grey.Received\" \r\n##  [9] \"Jersey.White\"          \"Jersey.White.Received\"\r\n## [11] \"Short.Black\"           \"Short.Received\"       \r\n## [13] \"Black.Sock\"            \"Black.Sock.Received\"  \r\n## [15] \"Track.Jacket\"          \"Track.Jacket.Received\"\r\n## [17] \"Track.Pant\"            \"Track.Pant.Received\"  \r\n## [19] \"Cinch.Pack\"            \"Cinch.Pack.Received\"  \r\n## [21] \"GK.Jersey\"             \"GK.Jersey.Received\"   \r\n## [23] \"GK.Short\"              \"GK.Short.Received\"    \r\n## [25] \"GK.Socks\"              \"GK.Sock.Received\"<\/code><\/pre>\n<\/div>\n<\/div>\n
<\/code><\/pre>\n
 <\/p>\n
\u00a0<\/code><\/span><\/pre>\n
<\/code><\/span><\/pre>\n
<\/code><\/span><\/pre>\n
\n
## What are the sizes?<\/span><\/code><\/span><\/p>\n
size_list <- c() <\/span><\/code><\/p>\n
rec_list <- c() <\/span><\/code><\/p>\n
for (i in 1:13){ <\/span><\/code><\/p>\n
\u00a0\u00a0\u00a0 size_list <- c(size_list,interm_list[2*i-1]) <\/span><\/code><\/p>\n
\u00a0 \u00a0\u00a0 \u00a0\u00a0 \u00a0\u00a0 rec_list <- c(rec_list,interm_list[2*i]) <\/span><\/code><\/p>\n
} <\/span><\/code><\/p>\n
item_list <- str_remove(rec_list,\".Received\") <\/span><\/code><\/p>\n
## Make a blank dataframe<\/span><\/code><\/h2>\n
Soccer_df <- data.frame(\"Player\"=numeric(), \"Item\"= character(), \"Size\"=character(), \"Recieved\"=character())<\/span><\/code><\/p>\n
for (i in 1:13){ <\/span><\/code><\/span><\/p>\n
newdf <- df %>% <\/span><\/code><\/span><\/p>\n
select(Player, size_list[i], rec_list[i]) <\/span><\/code><\/span><\/p>\n
names(newdf) <- c(\"Player\", \"Size\", \"Received\")<\/span> <\/code><\/span><\/p>\n
newdf$Item <- item_list[i] newdf <- newdf %>% <\/span><\/code><\/span><\/p>\n
select(Player, Item, Size, Received) <\/span><\/code><\/span><\/p>\n
Soccer_df <- rbind(Soccer_df, newdf) <\/span><\/code><\/span><\/p>\n
}<\/span><\/code><\/span><\/p>\n
Inspect the first 5 rows:
\n<\/span><\/code><\/h2>\n
Soccer_df %>% head()<\/span><\/code><\/p>\n
 <\/p>\n<\/header>\n
\n
\r\n<\/code><\/pre>\n
##   Player   Item Size Received\r\n## 1      1 Jersey   YL      Yes\r\n## 2      3 Jersey   YM      Yes\r\n## 3      5 Jersey   YL      Yes\r\n## 4      8 Jersey   YL      Yes\r\n## 5      9 Jersey  YXL       No\r\n## 6     10 Jersey   YM      Yes<\/code><\/pre>\n<\/div>\n
<\/div>\n<\/div>\n
 <\/p>\n
\u00a0<\/code>\r\n\r\n<\/code><\/code><\/pre>\n
Now we will look at grouping the data.<\/p>\n
I filtered out anything where the size was \u201cNA\u201d, as we assume that means that there is no item ordered.<\/p>\n
<\/code><\/pre>\n
\n
\n
summary <- Soccer_df %>%
\nfilter(!is.na(Size)) <\/span><\/code><\/p>\n
%>%
\ngroup_by(Item, Size, Received) <\/span><\/code><\/p>\n
%>%
\nsummarize(Count = n())<\/span><\/code><\/p>\n
 <\/p>\n
to_save <- Soccer_df <\/span><\/code><\/p>\n
%>%
\nfilter(!is.na(Size)) <\/span><\/code><\/p>\n
%>%
\ngroup_by(Item, Size, Received) <\/span><\/code><\/p>\n
%>%
\nsummarize(Count = n()) <\/span><\/code><\/p>\n
%>%
\nfilter(Received == \"No\")<\/span><\/code><\/p>\n
 <\/p>\n
 <\/p>\n
to_save<\/span><\/code><\/p>\n<\/header>\n
\n
\u00a0<\/code><\/pre>\n
## # A tibble: 23 x 4\r\n## # Groups:   Item, Size [23]\r\n##    Item        Size       Received Count\r\n##    <chr>       <chr>      <chr>    <int>\r\n##  1 Black.Sock  MED        No           5\r\n##  2 Black.Sock  SM         No           4\r\n##  3 Black.Sock  XS         No           2\r\n##  4 Cinch.Pack  \"Ordered \" No          12\r\n##  5 GK.Jersey   YL         No           1\r\n##  6 GK.Short    YL         No           1\r\n##  7 GK.Sock     SM         No           1\r\n##  8 Jersey      YXL        No           3\r\n##  9 Jersey.Grey YL         No           4\r\n## 10 Jersey.Grey YXL        No           7\r\n## # ... with 13 more rows<\/code><\/pre>\n<\/div>\n<\/div>\n
<\/code><\/pre>\n
 <\/p>\n
<\/code><\/pre>\n<\/div>\n
\n
Save<\/h2>\n
Finally, we cansave that file as a new Excel sheet – or you could mkae it an old Excel Sheet, whatever!<\/p>\n
\n
\n
Learning Objectives<\/p>\n<\/header>\n
\n
file_name <- paste0(\"Soccer_\",Sys.Date(),\".xlsx\")\r\nfile_name<\/code><\/pre>\n
## [1] \"Soccer_2022-04-08.xlsx\"<\/code><\/pre>\n
wb <- createWorkbook(\"Soccer_Gear\")\r\n\r\naddWorksheet(wb, sheetName = \"original\")\r\nwriteData(wb, sheet = \"original\", x = df)\r\n\r\naddWorksheet(wb, sheetName = \"Not_Received\")\r\nwriteData(wb, sheet = \"Not_Received\", x = to_save)\r\n\r\naddWorksheet(wb, sheetName = \"Summary\")\r\nwriteData(wb, sheet = \"Summary\", x = summary)\r\n\r\n\r\n\r\nsaveWorkbook(wb, file_name, overwrite = TRUE)<\/code><\/pre>\n
<\/code><\/pre>\n<\/div>\n<\/div>\n
 <\/p>\n
<\/code><\/p>\n<\/div>\n<\/div>\n<\/div>\n","protected":false},"author":883,"menu_order":11,"template":"","meta":{"pb_show_title":"on","pb_short_title":"","pb_subtitle":"","pb_authors":[],"pb_section_license":""},"chapter-type":[],"contributor":[],"license":[],"class_list":["post-317","chapter","type-chapter","status-publish","hentry"],"part":98,"_links":{"self":[{"href":"https:\/\/pressbooks.bccampus.ca\/businessanalytics\/wp-json\/pressbooks\/v2\/chapters\/317","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/pressbooks.bccampus.ca\/businessanalytics\/wp-json\/pressbooks\/v2\/chapters"}],"about":[{"href":"https:\/\/pressbooks.bccampus.ca\/businessanalytics\/wp-json\/wp\/v2\/types\/chapter"}],"author":[{"embeddable":true,"href":"https:\/\/pressbooks.bccampus.ca\/businessanalytics\/wp-json\/wp\/v2\/users\/883"}],"version-history":[{"count":12,"href":"https:\/\/pressbooks.bccampus.ca\/businessanalytics\/wp-json\/pressbooks\/v2\/chapters\/317\/revisions"}],"predecessor-version":[{"id":332,"href":"https:\/\/pressbooks.bccampus.ca\/businessanalytics\/wp-json\/pressbooks\/v2\/chapters\/317\/revisions\/332"}],"part":[{"href":"https:\/\/pressbooks.bccampus.ca\/businessanalytics\/wp-json\/pressbooks\/v2\/parts\/98"}],"metadata":[{"href":"https:\/\/pressbooks.bccampus.ca\/businessanalytics\/wp-json\/pressbooks\/v2\/chapters\/317\/metadata\/"}],"wp:attachment":[{"href":"https:\/\/pressbooks.bccampus.ca\/businessanalytics\/wp-json\/wp\/v2\/media?parent=317"}],"wp:term":[{"taxonomy":"chapter-type","embeddable":true,"href":"https:\/\/pressbooks.bccampus.ca\/businessanalytics\/wp-json\/pressbooks\/v2\/chapter-type?post=317"},{"taxonomy":"contributor","embeddable":true,"href":"https:\/\/pressbooks.bccampus.ca\/businessanalytics\/wp-json\/wp\/v2\/contributor?post=317"},{"taxonomy":"license","embeddable":true,"href":"https:\/\/pressbooks.bccampus.ca\/businessanalytics\/wp-json\/wp\/v2\/license?post=317"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}