Geek Answers Handbook

Import dirty data using R

Does anyone have an idea about importing data below into R in the appropriate form? I tried the strsplit function as: test <- strsplit(test,"[[:space:]]+") where test is the file name, including the following messy data. Somehow, I only had one character variable. I would like to have eight different variables in the appropriate form. Could you help me?

 Black Eagles 01/12 - 12/11 1500 W 7.0 420 48 Away +3 Blue State 02/18 - 04/21 1293 L 8.0 490 48 Home +1 Hawks 01/13 - 02/17 1028 L 4.0 46 460 Away New Apple 09/23 - 11/23 563 L 3.0 470 47 Home +2 Black White 07/05 - 09/26 713 L 5.2 500 45 Home +4 PBO 10/24 - 10/30 1495 L 1.9 47 410 Away
+6
source share
1 answer

Like this?

 > nicelyFormatted [,1] [,2] [,3] [,4] [,5] [,6] [,7] [,8] [,9] [,10] [1,] "Black Eagles" "01/12" "12/11" "1500" "W" "7.0" "420" "48" "Away" "+3" [2,] "Blue State" "02/18" "04/21" "1293" "L" "8.0" "490" "48" "Home" "+1" [3,] "Hawks" "01/13" "02/17" "1028" "L" "4.0" "46" "460" "Away" NA [4,] "New Apple" "09/23" "11/23" "563" "L" "3.0" "470" "47" "Home" "+2" [5,] "Black White" "07/05" "09/26" "713" "L" "5.2" "500" "45" "Home" "+4" [6,] "PBO" "10/24" "10/30" "1495" "L" "1.9" "47" "410" "Away" NA



Here is the code that was used to get the above table:

 library(stringr) # Open Connection to file pathToFile <- path.expand("~/path/to/file/myfile.txt") f <- file(pathToFile, "rb") # Read in lines rawText <- readLines(f) # Find the dahses dsh <- str_locate_all(rawText, " - ") # Splice, using the dashes as a guide lng <- length(rawText) spliced <- sapply(1:lng, function(i) spliceOnDash(rawText[[i]], dsh[[c(i, 1)]], dsh[[c(i, 2)]]) ) # make it purtty nicelyFormatted <- formatNicely(spliced) nicelyFormatted #-------------------# # FUNCTIONS # #-------------------# spliceOnDash <- function(strn, start, end) { # split around the date pre <- substr(strn, 1, start-6) dates <- substr(strn, start-5, end+5) post <- substr(strn, end+6, str_length(strn)) # Clean up pre <- str_trim(pre) # replace all double spaces with single spaces while(str_detect(post, " ")) { post <- str_replace_all(str_trim(post), " ", " ") } # splice on space post <- str_split(post, " ") # if dates are one field, remove this next line dates <- str_split(dates, " - ") # return c(unlist(pre), unlist(dates), unlist(post)) } # Function to clean up the list into a nice table formatNicely <- function(spliced) { lngst <- max(sapply(spliced, length)) t(sapply(spliced, function(x) if(length(x) < lngst) c(x, rep(NA, lngst-length(x))) else x )) }
+9
source

More articles:


All Articles