How to convert untidy survey tables to dataframes in R

Question

I often work with survey data at my job that comes in horribly formatted excel files that have been designed for readability and not for any data analysis. I'm looking for a way to clean the data in R and knock it into a dataframe format of variables and observations.

I know there's a load of tutorials on data cleaning in R but from my experience, they mostly deal with data that is already in a machine-readable format, so any help with this would be appreciated!

Here's a dummy example of the raw survey that has this shape:

Are you male or female?

           Variable1 Variable2 Variable3 Variable4
Male       n%        n%        n%        n%
Female     n%        n%        n%        n%


How old are you?

           Variable1 Variable2 Variable3 Variable4
18-34      n%        n%        n%        n%
35+        n%        n%        n%        n%

And so on, with blank space being empty cells/rows, the whole of each survey question being in column A a couple rows above it's corresponding data table and all questions/datatables being on one worksheet.

Is there a way to convert to this with R code?

Question                Response Variable1 Variable2 Variable3 Variable4
Are you male or female? Male     n%        n%        n%        n%
Are you male or female? Female   n%        n%        n%        n%
How old are you?        18-34    n%        n%        n%        n%
How old are you?        35+      n%        n%        n%        n%

At the moment, I'm using some VBA code to do this in excel then reading in to R for further analysis/visualisation, but it would be nice be able to skip the excel phase and go straight to R.

Thanks!

Answer 1

Here's a rough way of dealing with badly collated data. I'd made up one in csv format and hosted it on a miscellaneous repo:

file <- "https://raw.githubusercontent.com/minerva79/woodpecker/master/data/example.csv"
survey <- readLines(file)

(1) strip all white lines:

white.lines <- nchar(gsub(",", "", survey))==0
survey <- survey[!white.lines]

[1] "Are you male or female?,,,,"              ",Variable1,Variable2,Variable3,Variable4" "Male,0.5,0.6,0.7,0.8"                    

[4] "Female,0.5,0.4,0.3,0.2"                   "How old are you?,,,,"                     ",Variable1,Variable2,Variable3,Variable4"
[7] "18-34,0.4,0.5,0.7,0.1"                    "35+,0.6,0.5,0.3,0.9" 

(2) identify header position

headers <- substring(survey, 1,1) == ","
survey[headers]

[1] ",Variable1,Variable2,Variable3,Variable4" ",Variable1,Variable2,Variable3,Variable4"

(3) find the question position based on the headers position

header_pos <- (1:length(survey))[headers]
qn_pos <- header_pos - 1 

qn <- survey[qn_pos] %>% gsub(",", "", .)
qn

[1] "Are you male or female?" "How old are you?" 

(4) identify the lines for tables (from header_pos to qn_pos-1 or length(survey):

qn_pos <- c(qn_pos - 1, length(survey))
tabs <- lapply(1:length(qn), function(x)survey[header_pos[x]:qn_pos[x+1]])
tabs

[[1]]
[1] ",Variable1,Variable2,Variable3,Variable4" "Male,0.5,0.6,0.7,0.8"                     "Female,0.5,0.4,0.3,0.2"                  

[[2]]
[1] ",Variable1,Variable2,Variable3,Variable4" "18-34,0.4,0.5,0.7,0.1"                    "35+,0.6,0.5,0.3,0.9" 

(5) read each of the list object as table:

tabs <- lapply(tabs, function(x)read.table(text=x, sep=",", header=T, row.names=1))
tabs

[[1]]
       Variable1 Variable2 Variable3 Variable4
Male         0.5       0.6       0.7       0.8
Female       0.5       0.4       0.3       0.2

[[2]]
      Variable1 Variable2 Variable3 Variable4
18-34       0.4       0.5       0.7       0.1
35+         0.6       0.5       0.3       0.9

(6) mutate question and response, and rbind:

tabs <- lapply(1:length(tabs), function(x) tabs[[x]] %>% mutate(Question= qn[x], Response=row.names(.)))
do.call(rbind, tabs)

  Variable1 Variable2 Variable3 Variable4                Question Response
1       0.5       0.6       0.7       0.8 Are you male or female?     Male
2       0.5       0.4       0.3       0.2 Are you male or female?   Female
3       0.4       0.5       0.7       0.1        How old are you?    18-34
4       0.6       0.5       0.3       0.9        How old are you?      35+

== Edit: I'd pushed the old answer below as the question wasn't clear before.

Assume that you have 2 survey questions as follow:

set.seed(4)
sq_1 <- data.frame(V1 = rnorm(2, .5, .1), V2 = rnorm(2, .5, .1),V3 = rnorm(2, .5, .1),V4 = rnorm(2, .5, .1), row.names=paste0("response",1:2))
sq_2 <- data.frame(V1 = rnorm(2, .5, .1), V2 = rnorm(2, .5, .1),V3 = rnorm(2, .5, .1),V4 = rnorm(2, .5, .1), row.names=paste0("response",1:2))
write.csv(sq_1, "survey_question_1.csv")
write.csv(sq_2, "survey_question_2.csv")

To read them into R as a list:

files <- list.files(pattern="\\.csv")
survey <- lapply(files, read.csv, header=T, row.names=1)

Inserting question and response columns using dplyr:

library(dplyr)    
survey <- lapply(1:length(survey), function(x) survey[[x]] %>% 
               mutate(Question=paste0("Q",x), Response = rownames(.)))
do.call(rbind, survey)


         V1        V2        V3        V4 Question  Response
1 0.5216755 0.5891145 0.6635618 0.3718753       Q1 response1
2 0.4457507 0.5595981 0.5689275 0.4786855       Q1 response2
3 0.6896540 0.5566604 0.5383057 0.5034352       Q2 response1
4 0.6776863 0.5015719 0.4954863 0.5169027       Q2 response2