CODEWITHSUNDEEP
rdataframe
HarshUp
HarshUp

Reputation: 17

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

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.

Upvotes: 0

Views: 64

Answers (2)

r2evans
r2evans

Reputation: 160407

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

Upvotes: 2

AndrewGB
AndrewGB

Reputation: 16836

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

Upvotes: 1

Related Questions