How to append column 2 of a dataframe below column 1 of the same dataframe in R?-CodePudding

I have an dataframe containing daily rainfall data of a particular year (say, 1990) which looks like this:

rain_df1 <- data.frame(Date = c("1", "2", "3", "4", "5"), JAN = c("0", "2", "5", "7", "3"), FEB = c("1", "0", "1", "2", "0"))

The date column goes upto 31 and there are individual columns for each month after FEB as well (MAR, APR, ...). What I want is to arrange this data in such a way that the daily rainfall data values for FEB are below the values of JAN, ones in MAR below JAN and FEB and so on, i.e, I want a two column dataframe with one column containing dates (MM/DD/YYYY) while the second column containing the rainfall on those dates.

Example:

rain_df2 <- data.frame(Date = c("01/01/1990", "01/02/1990", "01/03/1990", "01/04/1990", "01/05/1990"), Rainfall = c("0", "2", "5", "7", "3"))

with the dates extending upto 12/31/1990 along with the corresponding rainfall values.

CodePudding user response：

Pivot (melt), then convert to date. Assuming that you have uneven rows (since each month has different days), it seems likely that you'll have NA dates and/or values. I'll filter on a Rainfall of NA for now.

base R

(Assuming R-4.1 for now.)

reshape2::melt(rain_df1, id.vars = "Date", variable.name = "Month", value.name = "Rainfall") |> 
  subset(!is.na(Rainfall)) |>
  transform(Date = as.Date(paste("1990", Month, Date), format = "%Y %b %d")) |>
  subset(select = -Month)
             Date Rainfall
# 1  1990-01-01        0
# 2  1990-01-02        2
# 3  1990-01-03        5
# 4  1990-01-04        7
# 5  1990-01-05        3
# 6  1990-02-01        1
# 7  1990-02-02        0
# 8  1990-02-03        1
# 9  1990-02-04        2
# 10 1990-02-05        0

dplyr

library(dplyr)
rain_df1 %>%
  pivot_longer(-Date, names_to = "Month", values_to = "Rainfall") %>%
  filter(!is.na(Rainfall)) %>%
  mutate(Date = as.Date(paste("1990", Month, Date), format = "%Y %b %d")) %>%
  select(-Month)
# # A tibble: 10 x 2
#    Date       Rainfall
#    <date>     <chr>   
#  1 1990-01-01 0       
#  2 1990-02-01 1       
#  3 1990-01-02 2       
#  4 1990-02-02 0       
#  5 1990-01-03 5       
#  6 1990-02-03 1       
#  7 1990-01-04 7       
#  8 1990-02-04 2       
#  9 1990-01-05 3       
# 10 1990-02-05 0

CodePudding user response：

Here is a possible data.table option. First, I use melt to pivot, then I format the date and then drop the month.

library(data.table)

setDT(rain_df1)
rain_df1 <- data.table::melt(rain_df1, id.vars = "Date", variable.name = "Month", value.name = "Rainfall")
rain_df1$Date <- format(as.Date(with(rain_df1, paste(Month, Date, "1990", sep="-")), "%b-%d-%Y"), "%m/%d/%Y")
rain_df1[,Month:=NULL]

Output

         Date Rainfall
 1: 01/01/1990        0
 2: 01/02/1990        2
 3: 01/03/1990        5
 4: 01/04/1990        7
 5: 01/05/1990        3
 6: 02/01/1990        1
 7: 02/02/1990        0
 8: 02/03/1990        1
 9: 02/04/1990        2
10: 02/05/1990        0