0

I am trying to calculate the cumulative confirmed cases rate (i.e. cases per 10000 population) and the cumulative death rate by month from two DF and plot it into a map.

Here's the DF that I have

  • DF1 - clean_dif - [Province, City, District, Sub_District, Cases, Death, Month, ID]
  • DF2 - indo_filter_3 - [Object_ID, Village_Code, Village, ID, Province, City, District, Sub_District, Total_Populatiom, Gemoetry]

Here's what I have tried so far and I guess the reason why it doesn't work is cause Total_Population is not inside clean_df but inside indo_filter_3. How do I create a new calculation field from two different DF (Indo_filter_3 & clean_df) and then create a chloropleth map?

new_df <- clean_df %>%
  group_by(Sub_District, Month) %>%
  summarise(`Cases` = sum(as.numeric(Cases))/Total_Population)


tmap_mode("plot")
tm_shape(new_df)+
  tm_fill("Cases_Pop",minimize = TRUE) +
  tm_borders(alpha = 0.5)
Parfait
  • 104,375
  • 17
  • 94
  • 125
Lostguy97
  • 9
  • 3
  • 1
    Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking. – Community Aug 29 '21 at 00:48
  • Please include exact error. Which of the two code blocks is the issue you are facing? BTW - you may need to reconcile `Cases_Pop` vs `Cases`. – Parfait Aug 29 '21 at 15:31

1 Answers1

0

Consider joining the two data frames before aggregating:

new_df <- clean_df %>%
  inner_join(indo_filter_3, by=c("Province", "City", "District", "Sub_District")) %>%
  group_by(Sub_District, Month) %>%
  summarise(`Cases` = sum(as.numeric(Cases))/Total_Population)
Parfait
  • 104,375
  • 17
  • 94
  • 125
  • Hi there, thanks for answering! I still get this error message 'Error: Object new_df is neither from class sf, stars, Spatial, Raster, nor SpatRaster.' I do have a geometry column in my indo_filter_3 – Lostguy97 Aug 29 '21 at 02:51
  • But that error does not involve this answer. Correct? I thought your issue involves the `new_df` assignment in calculating `Cases` that require `Total_Population`. Per the comment above, what exactly *doesn't work*? If issue is now mapping that is a different question, not *Aggregating Values From 2 DF in R*. – Parfait Aug 29 '21 at 15:29
  • Hello that error is pertaining to the code block provided by u which is the part on summarise(`Cases` = sum(as.numeric(Cases))/Total_Population). I've tried a lot of methods but I still can't seem to be able to create a chloropleth map for cases for population. Can u help me out – Lostguy97 Aug 29 '21 at 23:58
  • Very odd, that error should be your attempted graph not the `group_by` calculation unless `clean_df` or `indo_filter_3` is not a `data.frame` but some other class object. Please post a sample like `head` of both data frames with `dput` for [reproducible example](https://stackoverflow.com/q/5963269/1422451). – Parfait Aug 30 '21 at 00:25
  • Please delete these comments and post the `dput` in your original question. See edit link below post. – Parfait Sep 03 '21 at 00:43