How to use average value in geom_line?

Question

I am struggling to make a continuous line graph with bar plot. I used the below code to make this graph.

Is there any way I can use geom_line with average value?

ggplot(df, aes(Date, Rain)) +
  geom_col() +
  geom_line(aes(y = a + Temperature*b), size = 1.5, color = "red") + #linetype="dashed", 
  theme(axis.line.y.right = element_line(color = "red"), 
        axis.ticks.y.right = element_line(color = "red"),
        axis.text.y.right = element_text(color = "red"), 
        axis.title.y.right = element_text(color = "red"))+
  scale_x_discrete(guide = guide_axis(angle = 90))+
  scale_y_continuous("Precipitation", sec.axis = sec_axis(trans=~.*3, name = "Temperature"))

Here is the link for my data file https://docs.google.com/spreadsheets/d/13kosrVt0kQsjM1dPFn4hOCp3UQ9fmBBU_KlXVRfL_J0/edit?usp=sharing

Answer 1

You've got multiple observations per day (due to the time column). You can either summarise the two measurements (Rain and Temperature) by Date before plotting, or use the stat_summary function from ggplot2 on the raw data.

Also, your code suggests that the x-axis contains dates stored as character. You may want to convert these to valid dates first.

df$Date <- as.Date(df$Date, format="%m/%d/%Y")

summarise(df, Rain=sum(Rain), Temperature=mean(Temperature),
          .by=Date) |>
  ggplot(aes(Date, Rain, group=1)) +  # Need to specify group
     geom_line(aes(y = Temperature), linewidth = 1.5, color = "red") + 
     scale_x_date(guide = guide_axis(angle = 90),
           breaks="day") +
     scale_y_continuous("Precipitation", 
                     sec.axis = sec_axis(~.*3, name = "Temperature")) +
theme(...) +
...

---

Or,

ggplot(df, aes(Date, Rain)) +
  geom_col() +
  stat_summary(aes(y = Temperature), geom = "line", fun = mean,
               linewidth = 1.5, color = "red") +
  theme(...) + 
...

---