I have a blank data frame 'DF1' that looks like this:
| Site | 2021-01-01 00:00:00 | 2021-01-01 03:00:00 | 2021-01-01 06:00:00 | 2021-01-01 09:00:00 |
|---|---|---|---|---|
| BMA | NA | NA | NA | NA |
| BMC | NA | NA | NA | NA |
| MCA | NA | NA | NA | NA |
| MCC | NA | NA | NA | NA |
I have a section data frame 'DF2' that looks like this:
| ImageDate | SiteName |
|---|---|
| 2021-01-01 02:53:00 | BMA |
| 2021-01-01 08:44:00 | MCC |
| 2021-01-01 05:22:00 | BMC |
How can I query DF2 so that when a data point in 'ImageDate' falls into one of the column headers (time slots) of DF1 a number '1' is placed in the corresponding/matching 'Site' of DF1? And if no data is found in a particular column (time slot) for a site, that cell gets a '0', so that it would yield the following data frame:
| Site | 2021-01-01 00:00:00 | 2021-01-01 03:00:00 | 2021-01-01 06:00:00 | 2021-01-01 09:00:00 |
|---|---|---|---|---|
| BMA | 1 | 0 | 0 | 0 |
| BMC | 0 | 1 | 0 | 0 |
| MCA | 0 | 0 | 0 | 0 |
| MCC | 0 | 0 | 1 | 0 |
Thank you!
CodePudding user response：
Instead of creating a blank df1 and filling it you can transform df2 in a way that it gives the structure that we want.
You can try -
library(dplyr)
library(tidyr)
library(lubridate)
df2 %>%
mutate(ImageDate = floor_date(ymd_hms(ImageDate), '3 hours')) %>%
pivot_wider(names_from = ImageDate, values_from = ImageDate,
values_fn = length,values_fill = 0)