如何从excel / .csv /任何文件中读取和处理带有子列的列？ [英] How to read and process columns with sub columns from an excel/.csv/any file?

问题描述

我尝试读取一个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 /任何文件中读取和处理带有子列的列？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！