嵌套行标签到列

Question

我有一个CSV，它似乎是Excel Pivot表的输出，其名称嵌套为用于重复组的行标签。我想清洁数据，以便在单独的列中重复行标签，理想情况下使用dplyr。

数据看起来像这样：

dd <- data.frame(variables = c("Abington", "Number of Sales","YTD Number of Sales","Median Sale Price","YTD Median Sale Price", "Acton", "Number of Sales","YTD Number of Sales","Median Sale Price","YTD Median Sale Price"), Year1 = c(" ", 16, 50,415000,413500," ",23,60,799900,704000), Year2 = c(" ",8,13,583000,575000," ",9,39,995000,800000))

dd

variables              Year1   Year2
Abington              
Number of Sales        16      8
YTD Number of Sales    50      13
Median Sale Price      415000  583000
YTD Median Sale Price  413500  575000
Acton              
Number of Sales        23      9
YTD Number of Sales    60      39
Median Sale Price      799900  995000
YTD Median Sale Price  704000  800000

我希望它看起来像这样：

Town          variables               Year1  Year2           
Abington      Number of Sales         16     8
Abington      YTD Number of Sales     50     13
Abington      Median Sale Price       415000 583000
Abington      YTD Median Sale Price   413500 575000          
Acton         Number of Sales         23      9
Acton         YTD Number of Sales     60     39
Acton         Median Sale Price       799900 995000
Acton         YTD Median Sale Price   704000 800000

Answer 1

我们可以使用tidyverse（或dplyr＆amp; tidyr）为此：

library(tidyverse)

dd %>%
  mutate(Town = ifelse(Year1 == " " & Year2 == " ", variables, NA)) %>%
  fill(Town, .direction = "down") %>%
  filter(Town != variables) %>%
  relocate(Town)

结果：

      Town             variables  Year1  Year2
1 Abington       Number of Sales     16      8
2 Abington   YTD Number of Sales     50     13
3 Abington     Median Sale Price 415000 583000
4 Abington YTD Median Sale Price 413500 575000
5    Acton       Number of Sales     23      9
6    Acton   YTD Number of Sales     60     39
7    Acton     Median Sale Price 799900 995000
8    Acton YTD Median Sale Price 704000  8e+05

很重要，请注意，year y year 1的空值1和year2实际上是whitespaces（“”），而不是空字符串或na。

Answer 2

这是另一种方法：

bind_cols(
  tibble(Town=rep(filter(dd,is.na(as.numeric(Year1)))$variables, each=4)),
  filter(dd,!is.na(as.numeric(Year1)))
)

输出：

  Town     variables             Year1  Year2 
  <chr>    <chr>                 <chr>  <chr> 
1 Abington Number of Sales       16     8     
2 Abington YTD Number of Sales   50     13    
3 Abington Median Sale Price     415000 583000
4 Abington YTD Median Sale Price 413500 575000
5 Acton    Number of Sales       23     9     
6 Acton    YTD Number of Sales   60     39    
7 Acton    Median Sale Price     799900 995000
8 Acton    YTD Median Sale Price 704000 8e+05