r - 如何根据 Excel 电子表格中重复标题的值在 R 中创建新列?
问题描述
我正在尝试读取多个 Excel 电子表格,其中数据被组织成表格。(电子表格当前在图像中的外观示例):
我需要将实验单元(牛)作为自己的分析列,但它只位于每个表的标题中。关于在 R 中重新排列数据的最佳方法的任何想法?电子表格的第一行也作为列名读入,我显然也不想这样做。这是读入 R 时工作表外观的可重现示例:
r1<-c("id","Cow 5590","...2","...3","Control","...4","...5","...6","...7","...8")
r2<-c("","","date","day","intake1","intake2","eattime","visits","dmi","vtime")
r3<-c("1","","43173","-21","24384","14.6","214","10","1.46","21.4")
r4<-c("1"," ","43174","-20","24374","17.6","217","17","1.76","27.4")
r5<-c("id","Cow 5591","...2","...3","Control","...4","...5","...6","...7","...8")
r6<-c("","","date","day","intake1","intake2","eattime","visits","dmi","vtime")
r7<-c("1"," ","43173","-21","24364","15.6","234","12","1.36","22.4")
r8<-c("1"," ","43174","-20","24884","18.6","284","18","1.86","28.4")
r9<-c("id","Cow 5592","...2","...3","Control","...4","...5","...6","...7","...8")
r10<-c("","","date","day","intake1","intake2","eattime","visits","dmi","vtime")
r11<-c("1"," ","43173","-21","24564","15.5","234","15","1.56","24.4")
r12<-c("1"," ","43174","-20","24584","15.6","254","18","1.85","34.4")
df<-data.frame(matrix(ncol=10,nrow=11))
colnames(df)<-r1
df[1,]<-r2
df[2,]<-r3
df[3,]<-r4
df[4,]<-r5
df[5,]<-r6
df[6,]<-r7
df[7,]<-r8
df[8,]<-r9
df[9,]<-r10
df[10,]<-r11
df[11,]<-r12
这就是我想要的样子:
c1<-c("id","Cow", "date","day","intake1","intake2","eattime","visits","dmi","vtime")
c2<-c("1","Cow 5590","43173","-21","24384","14.6","214","10","1.46","21.4")
c3<-c("1","Cow 5590","43174","-20","24374","17.6","217","17","1.76","27.4")
c4<-c("1","Cow 5591","43173","-21","24364","15.6","234","12","1.36","22.4")
c5<-c("1","Cow 5591","43174","-20","24884","18.6","284","18","1.86","28.4")
c6<-c("1","Cow 5592","43173","-21","24564","15.5","234","15","1.56","24.4")
c7<-c("1","Cow 5592","43174","-20","24584","15.6","254","18","1.85","34.4")
df2<-data.frame(matrix(ncol=10,nrow=6))
colnames(df2)<-c1
df2[1,]<-c2
df2[2,]<-c3
df2[3,]<-c4
df2[4,]<-c5
df2[5,]<-c6
df2[6,]<-c7
第一次海报。我很感激任何帮助。
解决方案
我们可以做
library(dplyr)
library(tidyr)
library(janitor)
df %>%
mutate(`Cow 5590` = na_if(trimws(`Cow 5590`), "")) %>%
fill(`Cow 5590`) %>%
mutate(`Cow 5590` = replace_na(`Cow 5590`, "Cow 5590")) %>%
mutate(id = replace(id, 1, 'id')) %>%
row_to_names(1) %>%
filter(!id %in% c("id", "")) %>%
rename(Cow = `Cow 5590`) %>%
type.convert(as.is = TRUE)
-输出
id Cow date day intake1 intake2 eattime visits dmi vtime
1 1 Cow 5590 43173 -21 24384 14.6 214 10 1.46 21.4
2 1 Cow 5590 43174 -20 24374 17.6 217 17 1.76 27.4
3 1 Cow 5591 43173 -21 24364 15.6 234 12 1.36 22.4
4 1 Cow 5591 43174 -20 24884 18.6 284 18 1.86 28.4
5 1 Cow 5592 43173 -21 24564 15.5 234 15 1.56 24.4
6 1 Cow 5592 43174 -20 24584 15.6 254 18 1.85 34.4
推荐阅读
- c# - 如果找不到文件,如何重定向程序集绑定
- angular - 在ionic4中的每个地方切换值都是相同的
- reactjs - 如何在 React Material-UI 中动态更改 GridList 的列
- javascript - 带有 Angular 的浮动面板
- asp.net - .NET Core Web 应用程序使用 .NET Framework 业务层与 .NET Standard 之间
- watson-studio - 将编目对象推送到项目
- javascript - 将日期从 UTC 转换为 CEST
- java - VLOOKUP 错误:formulaparse 异常:当前工作簿中不存在指定的命名范围“lookup_name”
- database - 通过 SSH 密钥从本地计算机连接使用 docker 运行且未发布端口的远程数据库
- java - 如果引用变量设置为 null 是否仍会消耗内存?