当您读入数据时,使用该选项指定从找到“国家/地区”的行开始读取。例如,您将使用startRow
for read.xlsx
-- 不确定您使用什么来读取 Excel。...
代表任何其他参数read.xlsx
.
x <- read.xlsx("your_file.xlsx", startRow = row_that_country_is_found, ...)
完成此操作后,您可能需要清理列名。年份可能会读作“X2015”或类似的内容。我们假设“x”现在看起来像这样(随机数据):
x
# Country 2015 2014 2013 2012 2011 2010
# 1 A 0.6883601 0.9199372 0.8996433 0.9644212 0.97940387 0.7564401
# 2 B 0.1572208 0.6507811 0.9444197 0.9420349 0.06572698 0.1445383
# 3 C 0.7599602 0.8948640 0.6020316 0.7315661 0.90211468 0.5831917
# 2015 2014 2013 2012 2011 2010
# 1 0.26770837 0.45293675 0.2363191 0.9718356 0.3290432 0.57801166
# 2 0.39756729 0.06007054 0.7108505 0.6843454 0.1690740 0.93432731
# 3 0.05011677 0.30123347 0.2633371 0.5079645 0.9527117 0.04442355
从这里,您应该重命名您的列。
names(x) <- c("Country", paste(rep(c("ATM", "POS"), each = 6),
names(x)[-1], sep = "_"))
您的名字现在应该如下所示:
names(x)
# [1] "Country" "ATM_2015" "ATM_2014" "ATM_2013" "ATM_2012" "ATM_2011"
# [7] "ATM_2010" "POS_2015" "POS_2014" "POS_2013" "POS_2012" "POS_2011"
# [13] "POS_2010"
然后你可以pivot_longer
, separate
将名称放入多个变量中,然后pivot_wider
以获得您想要的输出。
x %>%
pivot_longer(-Country) %>%
separate(name, into = c("Type", "Year")) %>%
pivot_wider(names_from = Type, values_from = value)
# # A tibble: 18 x 4
# Country Year ATM POS
# <chr> <chr> <dbl> <dbl>
# 1 A 2015 0.688 0.268
# 2 A 2014 0.920 0.453
# 3 A 2013 0.900 0.236
# 4 A 2012 0.964 0.972
# 5 A 2011 0.979 0.329
# 6 A 2010 0.756 0.578
# 7 B 2015 0.157 0.398
# 8 B 2014 0.651 0.0601
# ...
# 17 C 2011 0.902 0.953
# 18 C 2010 0.583 0.0444