使用R解析Surveymonkey csv文件

a0x5cqrl  于 12个月前  发布在  其他
关注(0)|答案(8)|浏览(134)

我试图分析一个大型调查创建surveymonkey有数百列的CSV文件和输出格式是很难使用的标题超过两行。

  • 有没有人找到一种简单的方法来管理CSV文件中的头,以便分析是可管理的?
  • 其他人如何分析Surveymonkey的结果?

谢谢你,谢谢

5lwkijsr

5lwkijsr1#

您可以从Surveymonkey以适合R的方便形式导出它,请参阅“高级电子表格格式”中的下载回复


的数据

lnlaulya

lnlaulya2#

我最后所做的是使用libreoffice打印出标为V1,V2等的标题,然后我只是读取文件,

m1 <- read.csv('Sheet1.csv', header=FALSE, skip=1)

字符串
然后对m1$V10,m1$V23等进行分析...
为了避免多列的混乱,我使用了以下小函数

# function to merge columns into one with a space separator and then
# remove multiple spaces
mcols <- function(df, cols) {
    # e.g. mcols(df, c(14:18))
        exp <- paste('df[,', cols, ']', sep='', collapse=',' )
        # this creates something like...
        # "df[,14],df[,15],df[,16],df[,17],df[,18]"
        # now we just want to do a paste of this expression...
        nexp <- paste(" paste(", exp, ", sep=' ')")
        # so now nexp looks something like...
        # " paste( df[,14],df[,15],df[,16],df[,17],df[,18] , sep='')"
        # now we just need to parse this text... and eval() it...
        newcol <- eval(parse(text=nexp))
        newcol <- gsub('  *', ' ', newcol) # replace duplicate spaces by a single one
        newcol <- gsub('^ *', '', newcol) # remove leading spaces
        gsub(' *$', '', newcol) # remove trailing spaces
}
# mcols(df, c(14:18))


毫无疑问,有人会把它清理干净的!
为了整理类似李克特的量表,我使用了:

# function to tidy c('Strongly Agree', 'Agree', 'Disagree', 'Strongly Disagree')
tidylik4 <- function(x) {
  xlevels <- c('Strongly Disagree', 'Disagree', 'Agree', 'Strongly Agree')
  y <- ifelse(x == '', NA, x)
  ordered(y, levels=xlevels)
}

for (i in 44:52) {
  m2[,i] <- tidylik4(m2[,i])
}


请随意评论,因为毫无疑问,这将再次出现!

neekobn8

neekobn83#

我必须经常处理这个问题,并且在两列上有标题有点痛苦。这个函数解决了这个问题,所以你只有一行标题要处理。它还加入了多穿孔问题,所以你有top:bottom风格的命名。

#' @param x The path to a surveymonkey csv file
fix_names <- function(x) {
  rs <- read.csv(
    x,
    nrows = 2,
    stringsAsFactors = FALSE,
    header = FALSE,
    check.names = FALSE, 
    na.strings = "",
    encoding = "UTF-8"
  )

  rs[rs == ""] <- NA
  rs[rs == "NA"] <- "Not applicable"
  rs[rs == "Response"] <- NA
  rs[rs == "Open-Ended Response"] <- NA

  nms <- c()

  for(i in 1:ncol(rs)) {

    current_top <- rs[1,i]
    current_bottom <- rs[2,i]

    if(i + 1 < ncol(rs)) {
      coming_top <- rs[1, i+1]
      coming_bottom <- rs[2, i+1]
    }

    if(is.na(coming_top) & !is.na(current_top) & (!is.na(current_bottom) | grepl("^Other", coming_bottom)))
      pre <- current_top

    if((is.na(current_top) & !is.na(current_bottom)) | (!is.na(current_top) & !is.na(current_bottom)))
      nms[i] <- paste0(c(pre, current_bottom), collapse = " - ")

    if(!is.na(current_top) & is.na(current_bottom))
      nms[i] <- current_top

  }

  nms
}

字符串
如果你注意到,它只返回名称。我通常只是用...,skip=2, header = FALSE读取.csv,保存到一个变量并覆盖变量的名称。它也有助于设置你的na.stringsstringsAsFactor = FALSE

nms = fix_names("path/to/csv")
d = read.csv("path/to/csv", skip = 2, header = FALSE)
names(d) = nms

0kjbasz6

0kjbasz64#

从2013年11月开始,网页布局似乎发生了变化。选择Analyze results > Export All > All Responses Data > Original View > XLS+ (Open in advanced statistical and analytical software)。然后转到导出并下载文件。您将获得原始数据,第一行=问题标题/后面的每一行= 1个响应,如果您有许多响应/问题,可能会在多个文件之间分割。
x1c 0d1x的数据

of1yzvn4

of1yzvn45#

标题的问题是,“选择所有适用的”列将有一个空白的顶行,列标题将是下面的一行。这只是这些类型的问题的问题。
考虑到这一点,我编写了一个循环来遍历所有列,如果列名为空(其字符长度为1),则将列名替换为第二行的值。
然后,您可以删除第二行数据,并拥有一个整洁的 Dataframe 。

for(i in 1:ncol(df)){
newname <- colnames(df)[i]
if(nchar(newname) < 2){
colnames(df)[i] <- df[1,i]
} 

df <- df[-1,]

字符串

wgeznvg7

wgeznvg76#

参加聚会很晚,但这仍然是一个问题,我发现的最好的解决方法是使用一个函数将列名和子列名粘贴在一起,基于重复的值。
例如,如果导出到.csv,重复的列名将自动替换为RStudio中的X。如果导出到.xlsx,重复的值将是...
以下是base R解决方案:

sm_header_function <- function(x, rep_val){
  
  orig <- x
  
  sv <- x
  sv <- sv[1,]
  sv <- sv[, sapply(sv, Negate(anyNA)), drop = FALSE]
  sv <- t(sv)
  sv <- cbind(rownames(sv), data.frame(sv, row.names = NULL))
  names(sv)[1] <- "name"
  names(sv)[2] <- "value"
  sv$grp <- with(sv, ave(name, FUN = function(x) cumsum(!startsWith(name, rep_val))))
  sv$new_value <- with(sv, ave(name, grp, FUN = function(x) head(x, 1)))
  sv$new_value <- paste0(sv$new_value, " ", sv$value)
  new_names <- as.character(sv$new_value)
  colnames(orig)[which(colnames(orig) %in% sv$name)] <- sv$new_value
  orig <- orig[-c(1),]
  return(orig)
}

sm_header_function(df, "X")
sm_header_function(df, "...")

字符串
对于一些示例数据,列名的更改如下所示:

SurveyMonkey的原始导出:

> colnames(sample)
 [1] "Respondent ID"                                 "Please provide your contact information:"      "...11"                                        
 [4] "...12"                                         "...13"                                         "...14"                                        
 [7] "...15"                                         "...16"                                         "...17"                                        
[10] "...18"                                         "...19"                                         "I wish it would have snowed more this winter."

已从SurveyMonkey清理导出:

> colnames(sample_clean)
 [1] "Respondent ID"                                            "Please provide your contact information: Name"           
 [3] "Please provide your contact information: Company"         "Please provide your contact information: Address"        
 [5] "Please provide your contact information: Address 2"       "Please provide your contact information: City/Town"      
 [7] "Please provide your contact information: State/Province"  "Please provide your contact information: ZIP/Postal Code"
 [9] "Please provide your contact information: Country"         "Please provide your contact information: Email Address"  
[11] "Please provide your contact information: Phone Number"    "I wish it would have snowed more this winter. Response"


示例数据:

structure(list(`Respondent ID` = c(NA, 11385284375, 11385273621, 
11385258069, 11385253194, 11385240121, 11385226951, 11385212508
), `Please provide your contact information:` = c("Name", "Benjamin Franklin", 
"Mae Jemison", "Carl Sagan", "W. E. B. Du Bois", "Florence Nightingale", 
"Galileo Galilei", "Albert Einstein"), ...11 = c("Company", "Poor Richard's", 
"NASA", "Smithsonian", "NAACP", "Public Health Co", "NASA", "ThinkTank"
), ...12 = c("Address", NA, NA, NA, NA, NA, NA, NA), ...13 = c("Address 2", 
NA, NA, NA, NA, NA, NA, NA), ...14 = c("City/Town", "Philadelphia", 
"Decatur", "Washington", "Great Barrington", "Florence", "Pisa", 
"Princeton"), ...15 = c("State/Province", "PA", "Alabama", "D.C.", 
"MA", "IT", "IT", "NJ"), ...16 = c("ZIP/Postal Code", "19104", 
"20104", "33321", "1230", "33225", "12345", "8540"), ...17 = c("Country", 
NA, NA, NA, NA, NA, NA, NA), ...18 = c("Email Address", "[email protected]", 
"[email protected]", "[email protected]", "[email protected]", 
"[email protected]", "[email protected]", "[email protected]"
), ...19 = c("Phone Number", "215-555-4444", "221-134-4646", 
"999-999-4422", "999-000-1234", "123-456-7899", "111-888-9944", 
"215-999-8877"), `I wish it would have snowed more this winter.` = c("Response", 
"Strongly disagree", "Strongly agree", "Neither agree nor disagree", 
"Strongly disagree", "Disagree", "Agree", "Strongly agree")), row.names = c(NA, 
-8L), class = c("tbl_df", "tbl", "data.frame"))

polkgigr

polkgigr7#

在2023年可以工作的两个解决方案:
1.使用haven()
1.用我的酷R包!

1.使用haven

Haven是导入和导出Stata、SPSS和SAS文件的R软件包。如果您在从SurveyMonkey导出时选择了“SPSS”选项,则可以使用Haven读取导出文件,该文件将是.sav文件:

导出接口:

x1c 0d1x的数据

haven::read_sav()在R中阅读文件

library(haven)
read_sav("your-file-here.sav")

字符串
生成的文件将是一个tibble(一种类型的数组)。列用'q 000 N'语法标记,并且可能包含haven包特有的文件类型,例如类为haven_labelled的数字向量,类似于因子。

2.使用我的酷包

我已经创建了一个one-function package,它使用tidyverse函数来读取和清理SM结果,这些SM结果在默认情况下以奇怪的格式导出。
如果你愿意,你可以这样做:

devtools::install_github("church-army/monkeyreadr")
library(monkeyreadr)
read_sm("your-survey-monkey-data.csv")


为了方便后人,我将在下面包含函数的主体:

read_sm()函数

default_cols <- c("Respondent ID", "Collector ID", "IP Address", 
                  "Email Address",  "First Name", "Last Name",
                  "Custom Data 1")

read_sm <- function(x, clean_names = TRUE, drop_surplus_cols = TRUE,
                    ...){

  ## determine cleaning function from clean_names -------------------
  stopifnot(length(clean_names) == 1)

  if(!is.function(clean_names)){
    name_cleaner <- ifelse(clean_names, janitor::make_clean_names, identity)
  } else name_cleaner <- clean_names

  ## read sm_data ---------------------------------------------------
  suppressMessages({
    sm_data <- vroom::vroom(x, show_col_types = FALSE, ...)
    })

  missing_names <- stringr::str_detect(names(sm_data), "^\\.\\.\\.\\d+$")

  sm_data <- dplyr::rename_with(sm_data, name_cleaner, everything())

  ## Assign correct types (where known) ----------------------------------------

  default_cols <- name_cleaner(default_cols)

  sm_data <-
    dplyr::mutate(
      sm_data,
      dplyr::across(
        dplyr::any_of(default_cols), as.character)
      )

  sm_data <-
    dplyr::mutate(
      sm_data,
      dplyr::across(any_of(name_cleaner(c("Start Date", "End Date"))),
                    lubridate::mdy_hms
      )
    )

  ## Replace missing names w/ values from first row ----------------------------
  first_row <- unlist(sm_data[1, ])
  sm_data <- sm_data[-1, ]

  repaired_names <- 
    name_cleaner(paste(first_row[missing_names], which(missing_names)))
  
old_names <- names(sm_data)[missing_names]

  names(sm_data)[missing_names] <- repaired_names

  if(length(repaired_names) > 0){

    repaired_names_to_print <-
      paste(old_names, "->", repaired_names, sep = " ")

    rlang::inform(message = "Repaired names:",
                  class = "sm_name_repair",
                  body = repaired_names_to_print
                  )
  }

  ## Drop surplus columns ------------------------
  if(drop_surplus_cols){

    all_na <- \(x) all(is.na(x))

    sm_data <- dplyr::select(sm_data, -(any_of(default_cols) & where(all_na)))

  }

  sm_data
}

pkbketx9

pkbketx98#

下面的例子如何:使用read.csv()header=FALSE。做两个数组,一个是两行标题,一个是调查的答案。然后paste()两行/两个句子在一起。最后,使用colnames()

相关问题