问题描述
我尝试读取一个Excel文件,该文件也需要读取子列,但没有解决此问题的方法。
I tried reading an Excel file where I need to read sub columns too, but not getting a way to resolve this.
Excel文件包含的数据为
The Excel file contains data as,
| Sl No. | Sales 1 | Sales 2 | % Change |
| | 1 Qtr | % Qtr | 2 Qtr| % Qtr | |
| 1 | 134 | 67 | 175 | 74 | 12.5 |
导入后,我可以看到数据为
After importing I can see the data as
| Sl No. |Sales 1| ...3 |Sales 2 | ...5 | % Change |
| NA | 1 Qtr | % Qtr | 2 Qtr | % Qtr | NA |
| 1 | 134 | 67 | 175 | 74 | 12.5 |
我尝试了几种合并 Sales 1& ... 3 and Sales 2&的方法。 ..5,并保留1个Qtr,%Qtr,2个Qtr,%Qtr作为子列,但无法做到这一点
I tried several ways to merge "Sales 1 & ...3 and Sales 2 & ...5" and keep 1 Qtr,% Qtr,2 Qtr,% Qtr as sub columns, but unable to do so
我需要这样,
| Sl No. | Sales 1 | Sales 2 | % Change |
| | 1 Qtr | % Qtr | 2 Qtr| % Qtr | |
| 1 | 134 | 67 | 175 | 74 | 12.5 |
推荐答案
不幸的是,R不允许使用多个别名。因此,使用基数R可能最简单的方法是合并名称,然后删除第一行。
Unfortunately, R doesn't allow for multiple colnames. So probably the easiest thing you can do using base R is combining the colnames and then getting rid of the first line.
library(openxlsx)
x <- read.xlsx("your_file.xlsx")
# Sl.No Sales.1 X3 Sales.2 X5 %Change
# 1 NA 1 Qtr %Qtr 2 Qtr %Qtr NA
# 2 1 134 67 175 74 12.5
colnames(x) <- paste0(colnames(x),ifelse(is.na(x[1,]),"",paste0(" - ", x[1,])))
x <- x[-1,]
# Sl.No Sales.1 - 1 Qtr X3 - %Qtr Sales.2 - 2 Qtr X5 - %Qtr %Change
# 2 1 134 67 175 74 12.5
colnames(x)
# [1] "Sl.No" "Sales.1 - 1 Qtr" "X3 - %Qtr" "Sales.2 - 2 Qtr" "X5 - %Qtr" "%Change"
这篇关于如何从excel / .csv /任何文件中读取和处理带有子列的列?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!