重新排列数据：从水年转换为日历年

我有一个表，其中包含来自流量计的数据，排列如下：

  Water.Year   May   Jun   Jul   Aug    Sep    Oct    Nov   Dec   Jan   Feb   Mar   Apr 
1  1953-1954 55.55 43.62 30.46 26.17  26.76  41.74  19.92 41.25 28.77 20.96 12.47 10.51
2  1954-1955 23.49 81.35 46.71 29.33  67.83 133.30  37.62 30.16 21.07 19.38 13.87 10.63
3  1955-1956  9.87 51.59 55.36 63.03 154.08  98.15 104.06 32.85 22.89 17.30 15.68 10.88

> data <- structure(list(Water.Year = structure(1:6, .Label = c("1953-1954", "1954-1955", "1955-1956", "1956-1957", "1957-1958", "1958-1959", "1959-1960", "1960-1961", "1961-1962", "1962-1963", "1963-1964", "1964-1965", "1965-1966", "1966-1967", "1967-1968", "1968-1969", "1969-1970", "1970-1971", "1971-1972", "1972-1973", "1973-1974", "1974-1975", "1975-1976", "1976-1977", "1977-1978", "1978-1979", "1979-1980", "1980-1981", "1981-1982", "1982-1983", "1983-1984", "1984-1985", "1985-1986", "1986-1987", "1987-1988", "1988-1989", "1989-1990", "1990-1991", "1991-1992", "1992-1993", "1993-1994", "1994-1995", "1995-1996", "1996-1997", "1997-1998", "1998-1999", "1999-2000", "2000-2001"), class = "factor"), May = c(55.55, 23.49, 9.87, 18.03, 17.46, 11.37), Jun = c(43.62, 81.35, 51.59, 28.61, 15.14, 29.48), Jul = c(30.46, 46.71, 55.36, 24.36, 20.09, 19.48), Ago = c(26.17, 29.33, 63.03, 22.01, 16.97, 16.86), Set = c(26.76, 67.83, 154.08, 28.51, 27.24, 21.01), Oct = c(41.74, 133.3, 98.15, 53.72, 35.78, 19.78), Nov = c(19.92, 37.62, 104.06, 115.78, 20.35, 18.69), Dic = c(41.25, 30.16, 32.85, 32.04, 22, 18.86), Ene = c(28.77, 21.07, 22.89, 25.44, 13.27, 14.89), Feb = c(20.96, 19.38, 17.3, 14.53, 10.37, 10.4), Mar = c(12.47, 13.87, 15.68, 10.78, 8.77, 8.79), Abr = c(10.51, 10.63, 10.88, 9.33, 7.69, 8.99)), .Names = c("Water.Year", "May", "Jun", "Jul", "Ago", "Set", "Oct", "Nov", "Dic", "Ene", "Feb", "Mar", "Abr"), row.names = c(NA, 6L), class = "data.frame")

它是按“水年”排列的，每年从五月开始，到次年四月结束（这可以在第一栏看到）。 我想将其转换为具有三列的数据框：公历年 -- Month -- 流量测量

我已经分解了水年使用 tidyr 中的“separate”将列分成两列：

> df = separate(data, Water.Year, c("year1","year2"))

   year1 year2   May   Jun   Jul   Aug    Sep    Oct    Nov   Dec   Jan   Feb   Mar   Apr 
 1  1953  1954 55.55 43.62 30.46 26.17  26.76  41.74  19.92 41.25 28.77 20.96 12.47 10.51
 2  1954  1955 23.49 81.35 46.71 29.33  67.83 133.30  37.62 30.16 21.07 19.38 13.87 10.63

现在我计划使用 tidyr 中的“gather”来完成其余的转换，但我坚持如何创建一个公历年列使用year1用于列May to Dec and year2 for Jan to Apr.

任何帮助将不胜感激。

另一个想法（使用带有英语月份的@useR数据）

library(dplyr)
library(tidyr)


df %>%
  separate(Water.Year, c("Year1", "Year2")) %>%
  gather(Month, Value, -(Year1:Year2)) %>%
  group_by(Year1, Year2) %>%
  mutate(Year = if_else(match(Month, month.abb) >= 5, Year1, Year2),
         Month = factor(Month, levels = month.abb)) %>%
  ungroup() %>%
  select(Year, Month, Value) %>%
  arrange(Year, Month)

我们分开Water.Year列入Year1 and Year2我们使用以下方法将数据重塑为长格式gather()。然后，对于每个组，我们使用match() and month.abb检查月份是否大于或等于 5（五月）并指定相应的年份if_else()。最后，我们删除不必要的列并arrange() by Year and Month

## A tibble: 36 × 3
#    Year  Month Value
#   <chr> <fctr> <dbl>
#1   1953    May 55.55
#2   1953    Jun 43.62
#3   1953    Jul 30.46
#4   1953    Aug 26.17
#5   1953    Sep 26.76
#6   1953    Oct 41.74
#7   1953    Nov 19.92
#8   1953    Dec 41.25
#9   1954    Jan 28.77
#10  1954    Feb 20.96
## ... with 26 more rows