使用可变字段重新排列 Excel/R中的多个单元格内容

我对这个问题有点数据争论头痛。我在 Excel 中有一个通过从网站复制粘贴生成的表,其中每一行代表一个样本。在这一行中有一个包含可变数量单元格的特定字段。我附上一个屏幕截图,以便您可以轻松理解我的意思:

我对这个问题有点数据争论头痛。我在 Excel 中有一个通过从网站复制粘贴生成的表,其中每一行代表一个样本。在这一行中有一个包含可变数量单元格的特定字段。我附上一个屏幕截图,以便您可以轻松理解我的意思:

excel screenshot 1

现在,我想做的是将这些字段中的每一个都放在一个单独的列中。如果这是固定数量的行,我将简单地通过“特殊粘贴”进行转置,然后在固定数量的字段处中断结果行,以便以整洁的方式处理所有内容。但是,每行的字段数量会发生变化;并非所有移动的样本都具有相同数量的属性,这意味着转置整个列的属性是不够的。

excel screenshot 2

然而,这是乏味的,耗时的,因为我有超过 500 个条目将永远。

我一点也不流利的 Excel 脚本,但我有一个半体面的知识在基地 R。我必须解决的主要问题是,如果我导入这个表作为 R 中的文本文件,该列中的每个单元格将被分配一行,使折叠相当复杂。

这是我到目前为止提出的:

#This is the data I need to wrangle
tmp <- read.delim("pdata.txt", header = T, stringsAsFactors = F)
> head(tmp)
            Title             Source.name             Disease.state    Sex Age
1 LT000842RU_CTRL Flash frozen whole lung                   Control 1-Male  75
2                                                                           NA
3                                                                           NA
4                                                                           NA
5  LT001600RL_ILD Flash frozen whole lung Interstitial lung disease 1-Male  54
6                                                                           NA
  Gold.stage              Characteristics Ild.subtype Pneumocystis.colonization
1  0-At Risk       smoker?: 2-Ever (>100)
2            %predicted fev1 (pre-bd): 96
3             %predicted fvc (pre-bd): 97
4                     %predicted dlco: 78
5               %emphysema (f-950): 1.903   2-UIP/IPF
6                  smoker?: 2-Ever (>100)

如您所见,“标题”列中有空格,这是由“特征”列具有相同标题的更多条目引起的。

#Extremely ugly series of gsub to make for compatible colnames (no spaces, no dashes, etc)
d = sapply(tmp[,7], function(x) {gsub(x, pattern = " ", replacement = "_", fixed = T)})
dd = sapply(d, function(x) {gsub(x, pattern = "%", replacement = "", fixed = T)})
dd = sapply(dd, function(x) {gsub(x, pattern = "_(f-950):_", replacement = " ", fixed = T)})
dd = sapply(dd, function(x) {gsub(x, pattern = "?:", replacement = "", fixed = T)})
dd = sapply(dd, function(x) {gsub(x, pattern = "smoker_", replacement = "smoker ", fixed = T)})
dd = sapply(dd, function(x) {gsub(x, pattern = ":_", replacement = " ", fixed = T)})
dd = sapply(dd, function(x) {gsub(x, pattern = "(", replacement = "", fixed = T)})
dd = sapply(dd, function(x) {gsub(x, pattern = ")", replacement = "", fixed = T)})
dd = sapply(dd, function(x) {gsub(x, pattern = "-", replacement = "_", fixed = T)})
  #Use the character vector that has been gsubbed as the attributes column in the df
tmp[,7] = dd
#Take the rows that are not empty, i.e. those that have the name of the sample and will be the starting rows for the attributes
    nonempty = which(tmp[,1] != "")
    jumps = nonempty[2:length(nonempty)]-nonempty[1:length(nonempty)-1]
    jumps = c(jumps, 0)
#Make dummy columns with the same names as the gsubbed attributes
tmp$emphysema = tmp[,1]
tmp$smoker = tmp[,1]
tmp$predicted_fcv_pre_bd = tmp[,1]
tmp$predicted_fev1_pre_bd = tmp[,1]
tmp$predicted_fev1_post_bd = tmp[,1]
tmp$predicted_fcv_post_bd = tmp[,1]
tmp$predicted_dlco = tmp[,1]
#This is a loop to fill in the columns with the values extracted from the gsubbed attributes column
for(i in 1:length(nonempty))
{
    a = as.data.frame(tmp[seq(nonempty[i], (nonempty[i]+jumps[i]-1),by = 1),7])
    chars = colnames(tmp[,10:ncol(tmp)])
    for (j in chars)
    {
        gg = as.character(a[grep(pattern = j, x = a[,1]),1])
        if(length(gg) != 0) tmp[nonempty[i],j] = as.character(unlist(strsplit(gg, split = " "))[2]) else tmp[nonempty[i],j] = NA
    }
}
# Make the new df by only taking the rows with samples
tmp2 = tmp[nonempty,]
#This is the resulting data frame:
> head(tmp2)
                          Title             Source.name
LT000216LL_ILD   LT000216LL_ILD Flash frozen whole lung
LT000379LU_ILD   LT000379LU_ILD Flash frozen whole lung
LT000842RU_CTRL LT000842RU_CTRL Flash frozen whole lung
LT001600RL_ILD   LT001600RL_ILD Flash frozen whole lung
LT001796RU_CTRL LT001796RU_CTRL Flash frozen whole lung
LT002410RM_ILD   LT002410RM_ILD Flash frozen whole lung
                            Disease.state      Sex Age Gold.stage
LT000216LL_ILD  Interstitial lung disease 2-Female  70
LT000379LU_ILD  Interstitial lung disease   1-Male  63
LT000842RU_CTRL                   Control   1-Male  75  0-At Risk
LT001600RL_ILD  Interstitial lung disease   1-Male  54
LT001796RU_CTRL                   Control   1-Male  48  0-At Risk
LT002410RM_ILD  Interstitial lung disease   1-Male  52
                   Characteristics                       Ild.subtype
LT000216LL_ILD     emphysema 0.051                         2-UIP/IPF
LT000379LU_ILD  smoker 2_Ever_>100 9-Hypersensitive Pneumonitis (HP)
LT000842RU_CTRL smoker 2_Ever_>100
LT001600RL_ILD     emphysema 1.903                         2-UIP/IPF
LT001796RU_CTRL smoker 2_Ever_>100
LT002410RM_ILD      emphysema 0.03                         2-UIP/IPF
                Pneumocystis.colonization emphysema      smoker
LT000216LL_ILD                                0.051     3_Never
LT000379LU_ILD                                 <NA> 2_Ever_>100
LT000842RU_CTRL                                <NA> 2_Ever_>100
LT001600RL_ILD                                1.903 2_Ever_>100
LT001796RU_CTRL                                <NA> 2_Ever_>100
LT002410RM_ILD                                 0.03 2_Ever_>100
                predicted_fcv_pre_bd predicted_fev1_pre_bd
LT000216LL_ILD                  <NA>                    56
LT000379LU_ILD                  <NA>                    67
LT000842RU_CTRL                 <NA>                    96
LT001600RL_ILD                  <NA>                    40
LT001796RU_CTRL                 <NA>                   107
LT002410RM_ILD                  <NA>                    56
                predicted_fev1_post_bd predicted_fcv_post_bd predicted_dlco
LT000216LL_ILD                    <NA>                  <NA>             36
LT000379LU_ILD                    <NA>                  <NA>             42
LT000842RU_CTRL                   <NA>                  <NA>             78
LT001600RL_ILD                    <NA>                  <NA>             16
LT001796RU_CTRL                    110                  <NA>            107
LT002410RM_ILD                      60                  <NA>             63

现在,虽然我对这个结果相当满意,但我花了一些时间做饭,而且它相当不灵活(非常适合这个特定的数据集),所以我想知道以下内容:

对于这种情况,Excel 中是否有快速修复?

或者,或者实际上更好:是否有一个基本 / tidyverse 的方式来处理这个 R 的方式,而不是像我想出的那样愚蠢?

提前感谢!

1

假设原始数据中的大单元格是合并单元格,这在openxlsxtidyr的帮助下非常简单。无论如何,我会第二个 @ RonRosenfeld 的建议(在评论中)设置到远程源的数据连接,而不是复制粘贴。

我创建了一个简化的示例文件 messy.xlsx(请参阅文章末尾的代码)来演示数据整理过程的一个选项:

Messy Excel data.

您可以首先读取 Excel 文件read.xlsx(),保留合并单元格所隐含的结构,然后separate()spread()将不同的特征放入自己的列中:

library(openxlsx)
library(tidyr)
# Repeat merged cell value accross all cells
messy <- read.xlsx("messy.xlsx", fillMergedCells = TRUE)
# Create a column for each characteristic
messy %>%
  separate(
    characteristics,
    into = c("variable", "value"),
    sep = ": "
  ) %>% 
  spread(variable, value)
#>   subject a b    c
#> 1     101 1 2    3
#> 2     102 2 8 <NA>

示例数据:

library(openxlsx)
wb <- createWorkbook()
addWorksheet(wb, "Sheet 1")
df <- data.frame(
  subject = c(101, NA, NA, 102, NA),
  characteristics = c("a: 1", "b: 2", "c: 3", "a: 2", "b: 8")
)
writeData(wb, 1, df)
mergeCells(wb, 1, 1, 2:4)
mergeCells(wb, 1, 1, 5:6)
saveWorkbook(wb, "messy.xlsx")

reprex package(v0.2.0.9000) 于 2018-07-17 创建。

本站系公益性非盈利分享网址,本文来自用户投稿,不代表码文网立场,如若转载,请注明出处

(464)
在溢出公式中引用溢出和“常规”单元格(excel #spill)
上一篇
无法编辑主机文件(writable notepad)
下一篇

相关推荐

发表评论

登录 后才能评论

评论列表(79条)