Replace values based on months in a dataframe with values in another column in r, using apply functions

Question

I am working with a time series of precipitation data and attempting to use the median imputation method to replace all 0 value data points with the median of all data points for the corresponding month that that 0 value was recorded.

I have two data frames, one with the original precipitation data:

 > head(df.m)
       prcp       date
1 121.00485 1975-01-31
2 122.41667 1975-02-28
3  82.74026 1975-03-31
4 104.63514 1975-04-30
5  57.46667 1975-05-31
6  38.97297 1975-06-30

And one with the median monthly values:

> medians
   Group.1         x
1       01 135.90680
2       02 123.52613
3       03 113.09841
4       04  98.10044
5       05  75.21976
6       06  57.47287
7       07  54.16667
8       08  45.57653
9       09  77.87740
10      10 103.25179
11      11 124.36795
12      12 131.30695

Below is the current solution that I have come up with utilizing the 1st answer here:

df.m[,"prcp"] <- sapply(df.m[,"prcp"], function(y) ifelse(y==0, medians$x,y))

This has not worked as it only applies the first value of the df medians$Group.1, which is the month of January (01). How can I get the values so that correct median will be applied from the corresponding month?

Another way I have attempted a solution is via the below:

df.m[,"prcp"] <- sapply(medians$Group.1, function(y)
                 ifelse(df.m[format.Date(df.m$date, "%m") == y & 
                 df.m$prcp == 0, "prcp"], medians[medians$Group.1 == y,"x"], 
                 df.m[,"prcp"]))   

Description of the above function - this function tests and returns the amount of zeros for every month that there is a zero value in df.m[,"prcp"] Same issue here as the 1st solution, but it does return all of the 0 values by month (if just executing the sapply() portion).

How can I replace all 0 in df.m$prcp with their corresponding medians from the medians df based on the month of the data?

Apologies if this is a basic question, I'm somewhat of a newbie here. Any and all help would be greatly appreciated.

Answer 1

Consider merging the two dataframes by month/group and then calculating with ifelse:

# MERGE TWO FRAMES
df.m$month <- format(df.m$date, "%m")
df.merge <- merge(df.m, medians, by.x="month", by.y="Group.1")

# CONDITIONAL CALCULATION
df.merge$prcp <- ifelse(df.merge$prcp == 0, df.merge$x, df.merge$prcp)

# RETURN BACK TO ORIGINAL STRUCTURE
df.m <- df.merge[names(df.m)]

Answer 2

I created small datasets with some zero values and added one line of code:

#create sample data    
prcp <- c(1.5,0.0,0.0,2.1)
date <- c(01,02,03,04)
x <- c(1.11,2.22,3.33,4.44)

df <- data.frame(prcp,date)
grp <- data.frame(x,date)

#Make the assignment
df[df$prcp == 0,]$prcp <- grp[df$prcp == 0,]$x

Answer 3

A dplyr version, which does not rely on original order. This uses slightly modified test data to show replacement of zeroes and multiple years

require(dplyr)

## test data with zeroes - extended for addtional years
df.m <- read.delim(text="
i prcp date
1 121.00485 1975-01-31
2 122.41667 1975-02-28
3 82.74026 1975-03-31
4 104.63514 1975-04-30
5 57.46667 1975-05-31
6 38.97297 1975-06-30
7 0 1976-06-30
8 0 1976-07-31
9 70 1976-08-31
", sep="", stringsAsFactors = FALSE)

medians <- read.delim(text="
i month x
1       01 135.90680
2       02 123.52613
3       03 113.09841
4       04  98.10044
5       05  75.21976
6       06  57.47287
7       07  54.16667
8       08  45.57653
9       09  77.87740
10      10 103.25179
11      11 124.36795
12      12 131.30695
", sep = "", stringsAsFactors = FALSE, strip.white = TRUE)

# extract the month as integer
df.m$month = as.integer(substr(df.m$date,6,7))

# match to medians by joining
result <- df.m %>% 
  inner_join(medians, by='month') %>%
  mutate(prcp = ifelse(prcp == 0, x, prcp)) %>%
  select(prcp, date)

result

yields

       prcp       date
1 121.00485 1975-01-31
2 122.41667 1975-02-28
3  82.74026 1975-03-31
4 104.63514 1975-04-30
5  57.46667 1975-05-31
6  38.97297 1975-06-30
7  57.47287 1976-06-30
8  54.16667 1976-07-31
9  70.00000 1976-08-31