Search code examples
rmatrixtidy

How to translate an untidy list into a tidy data frame?

The data that I scraped online is structured in an odd way. I'm having trouble tidying this vector in to a data frame.

Below is a a chart depicting how the data flows through the vector, indicated by the arrows.

data structure

When converting the vector into a matrix, I'm struggling to think of an efficient way to do this. I can get the desired outcome by subsetting every 20 entries.

Reproducible code

c("+282", "-331", "+295", "-325", "+283", "-352", "+270", "-325", 
"+260", "-320", "+270", "-330", "+275", "-340", "+265", "-325", 
"+283", "-352", "+270", "-325", "+266", "-311", "+280", "-310", 
"+267", "-330", "+260", "-310", "+275", "-350", "+265", "-325", 
"+270", "-330", "+250", "-320", "+267", "-330", "+260", "-310")

** Working code to get desired outcome

as.data.frame(matrix(odds[1:20], ncol = 10, byrow = F))
as.data.frame(matrix(odds[21:40], ncol = 10, byrow = F))

In theory I could just bind these two dataframes together, however in my larger dataset I have a significant amount of subsets that would need to be done to complete this. What is the most efficient way to tackle this problem?

Solution

  • You could create a dataframe of every 20 values and combine them :

    n <- 20
do.call(rbind, by(odds, ceiling(seq_along(odds)/n), function(x) 
               data.frame(matrix(x, ncol = n/2))))

#      X1   X2   X3   X4   X5   X6   X7   X8   X9  X10
#1.1 +282 +295 +283 +270 +260 +270 +275 +265 +283 +270
#1.2 -331 -325 -352 -325 -320 -330 -340 -325 -352 -325
#2.1 +266 +280 +267 +260 +275 +265 +270 +250 +267 +260
#2.2 -311 -310 -330 -310 -350 -325 -330 -320 -330 -310