我有一些数据是从我合作的公司收到的，但不知道如何将其转换为可用于分析的宽数据格式。数据框为 15,800,000 行，只有 5 个变量。然而，第 4 个和第 5 个变量是我必须使用的 (~90) 变量之一的名称和响应。为了使事情变得更加复杂，这些问题被问了不止一次，所以有多个答案。
但是，如果有超过 1 个可能的响应，则响应会转到下一行(见下文)。
id date answer_instance pdl_variable_name answer_option
1 25839 2014-02-01 4 discretspend (25228) 14
2 25839 2014-02-05 11 legal_services (25495) [99]
3 25839 2014-12-07 6 comppen_company (706) [97]
4 25837 2014-12-15 2 Affluence_V2_P_2014 (34264) 8
5 25837 2015-01-20 5 study_qualification_children (35100) [98]
6 25837 2015-08-05 4 overall_debt (27281) [99]
7 25837 2015-09-03 3 benefits_received (25465) [98]
8 25834 2015-09-13 5 privpen_company (707) [96]
9 25834 2015-11-12 3 pocket_money_frequency (27076) 10
10 25835 2016-01-18 4 unemployment_status (21922) 6
11 25835 2016-02-05 8 legal_services (25495) [99]
12 25822 2016-02-11 3 assets_total_investable (26413) 3
13 25822 2016-03-03 2 disability_benefits_received (25055) [99]
14 25822 2018-04-01 1 insurance_held_2018 (58085) [1
15 4]
16 25811 2018-04-13 1 insurance_held (615) [1
17 4 11 20]
18 25811 2018-04-26 2 profile_work_stat (25617) 5
理想情况下，我想将其转换为可用于分析的长/宽格式。
如果我们忽略多个答案的数据格式，您可以像这样dcast数据：
library(data.table)
dt <- data.table(df)
dt.wide <- dcast(
formula = date + answer_instance ~ pdl_variable_name,
data = dt,
value.var = "answer_option"
)
对于多个选项，如果要使用 R，则需要以可以读入data.frame的格式请求数据。
在多行上具有单个单元格并不是交换数据的最佳方式。如果这是您可以要求将值括在引号中的唯一方法。
由于您的文件相对较大，我建议使用具有快速fread功能的data.table。
OP 提供的数据中的主要问题似乎是单个记录溢出到下一行。因此，一旦行排列正确，就很容易转换任何形式的数据进行分析。
]的正面前瞻^(?=.*])和[的负面前瞻(?!.*\[)已用于确定某一行是否为部分行，是否为前一行的第二部分。
pdl_variable_name列的space和(已更改为_(，以便可以使用read.table将其读取为单列
library(tidyverse)
library(splitstackshape)
# Read from text file linewise
df_line <- data.frame(fileText = readLines("Answer.txt"), stringsAsFactors = FALSE)
tidy_text <- df_line %>% mutate(rn = row_number()) %>% # To merge partial row
mutate(rn = ifelse(grepl("^(?=.*])(?!.*\[)",df$fileText, perl = TRUE),lag(rn), rn)) %>% #doesnot contain [ but contains ]
group_by(rn) %>%
summarise(fileText = paste0(trimws(fileText), collapse=" ")) %>%
ungroup() %>%
mutate(fileText = gsub("\s(\()", "_\1", fileText)) %>%
mutate(fileText = gsub("\[|]", "\'", fileText)) # [1 4] is changed to '1 4'
# Concatenate rows prepared above separated by 'n' so that it read as dataframe
tidy_data <- read.table(text = paste0(trimws(tidy_text$fileText), collapse="n"), header = TRUE, stringsAsFactors = FALSE)
#Use cSplit to split answers in multiple columns
tidy_data <- tidy_data %>%
mutate(pdl_variable_name = gsub("_(\()", " \1", pdl_variable_name)) %>%
cSplit("answer_option", sep=" ")
结果：
tidy_data
# id date answer_instance pdl_variable_name answer_option_1 answer_option_2 answer_option_3 answer_option_4
# 1: 25839 2014-02-01 4 discretspend (25228) 14 NA NA NA
# 2: 25839 2014-02-05 11 legal_services (25495) 99 NA NA NA
# 3: 25839 2014-12-07 6 comppen_company (706) 97 NA NA NA
# 4: 25837 2014-12-15 2 Affluence_V2_P_2014 (34264) 8 NA NA NA
# 5: 25837 2015-01-20 5 study_qualification_children (35100) 98 NA NA NA
# 6: 25837 2015-08-05 4 overall_debt (27281) 99 NA NA NA
# 7: 25837 2015-09-03 3 benefits_received (25465) 98 NA NA NA
# 8: 25834 2015-09-13 5 privpen_company (707) 96 NA NA NA
# 9: 25834 2015-11-12 3 pocket_money_frequency (27076) 10 NA NA NA
# 10: 25835 2016-01-18 4 unemployment_status (21922) 6 NA NA NA
# 11: 25835 2016-02-05 8 legal_services (25495) 99 NA NA NA
# 12: 25822 2016-02-11 3 assets_total_investable (26413) 3 NA NA NA
# 13: 25822 2016-03-03 2 disability_benefits_received (25055) 99 NA NA NA
# 14: 25822 2018-04-01 1 insurance_held_2018 (58085) 1 4 NA NA
# 15: 25811 2018-04-13 1 insurance_held (615) 1 4 11 20
# 16: 25811 2018-04-26 2 profile_work_stat (25617) 5 NA NA NA
原始数据：
OP提供的answer.txt内容：
id date answer_instance pdl_variable_name answer_option
25839 2014-02-01 4 discretspend (25228) 14
25839 2014-02-05 11 legal_services (25495) [99]
25839 2014-12-07 6 comppen_company (706) [97]
25837 2014-12-15 2 Affluence_V2_P_2014 (34264) 8
25837 2015-01-20 5 study_qualification_children (35100) [98]
25837 2015-08-05 4 overall_debt (27281) [99]
25837 2015-09-03 3 benefits_received (25465) [98]
25834 2015-09-13 5 privpen_company (707) [96]
25834 2015-11-12 3 pocket_money_frequency (27076) 10
25835 2016-01-18 4 unemployment_status (21922) 6
25835 2016-02-05 8 legal_services (25495) [99]
25822 2016-02-11 3 assets_total_investable (26413) 3
25822 2016-03-03 2 disability_benefits_received (25055) [99]
25822 2018-04-01 1 insurance_held_2018 (58085) [1
4]
25811 2018-04-13 1 insurance_held (615) [1
4 11 20]
25811 2018-04-26 2 profile_work_stat (25617) 5
library(readr)
library(tidyverse)
library(splitstackshape)
#read file
txt <- read_lines(file = "file_path/test.txt")
#identify continuation of previous row and add it to the previous row
idx <- which(grepl('\]\s+$', txt))
txt <- gsub("^\d+\s+", "", txt) #remove row number from each row
txt[idx-1] <- paste(txt[idx-1], trimws(txt[idx]))
txt <- txt[-c(1,idx)]
#add a separator ";" to identify different columns in each row
txt <- gsub("(\S+)\s+(\S+)\s+(\S+)\s+(\S+\s+\S+)\s+(.*)", "\1;\2;\3;\4;\5", txt)
#prepare data for analysis
df <- as.data.frame(txt) %>%
cSplit("txt", sep = ";") %>%
`colnames<-`(c("id", "date", "answer_instance", "pdl_variable_name", "answer_option")) %>%
mutate(answer_option = gsub("\[|\]|(\s{2})+", "", answer_option)) %>%
separate_rows("answer_option", sep=" ")
df
输出为：
id date answer_instance pdl_variable_name answer_option
1 25839 2014-02-01 4 discretspend (25228) 14
2 25839 2014-02-05 11 legal_services (25495) 99
3 25839 2014-12-07 6 comppen_company (706) 97
4 25837 2014-12-15 2 Affluence_V2_P_2014 (34264) 8
5 25837 2015-01-20 5 study_qualification_children (35100) 98
6 25837 2015-08-05 4 overall_debt (27281) 99
7 25837 2015-09-03 3 benefits_received (25465) 98
8 25834 2015-09-13 5 privpen_company (707) 96
9 25834 2015-11-12 3 pocket_money_frequency (27076) 10
10 25835 2016-01-18 4 unemployment_status (21922) 6
11 25835 2016-02-05 8 legal_services (25495) 99
12 25822 2016-02-11 3 assets_total_investable (26413) 3
13 25822 2016-03-03 2 disability_benefits_received (25055) 99
14 25822 2018-04-01 1 insurance_held_2018 (58085) 1
15 25822 2018-04-01 1 insurance_held_2018 (58085) 4
16 25811 2018-04-13 1 insurance_held (615) 1
17 25811 2018-04-13 1 insurance_held (615) 4
18 25811 2018-04-13 1 insurance_held (615) 11
19 25811 2018-04-13 1 insurance_held (615) 20
20 25811 2018-04-26 2 profile_work_stat (25617) 5
示例数据：
id date answer_instance pdl_variable_name answer_option
1 25839 2014-02-01 4 discretspend (25228) 14
2 25839 2014-02-05 11 legal_services (25495) [99]
3 25839 2014-12-07 6 comppen_company (706) [97]
4 25837 2014-12-15 2 Affluence_V2_P_2014 (34264) 8
5 25837 2015-01-20 5 study_qualification_children (35100) [98]
6 25837 2015-08-05 4 overall_debt (27281) [99]
7 25837 2015-09-03 3 benefits_received (25465) [98]
8 25834 2015-09-13 5 privpen_company (707) [96]
9 25834 2015-11-12 3 pocket_money_frequency (27076) 10
10 25835 2016-01-18 4 unemployment_status (21922) 6
11 25835 2016-02-05 8 legal_services (25495) [99]
12 25822 2016-02-11 3 assets_total_investable (26413) 3
13 25822 2016-03-03 2 disability_benefits_received (25055) [99]
14 25822 2018-04-01 1 insurance_held_2018 (58085) [1
15 4]
16 25811 2018-04-13 1 insurance_held (615) [1
17 4 11 20]
18 25811 2018-04-26 2 profile_work_stat (25617) 5
在test.txt