I have the following data frame in R.
ID | Year_Month | Amount
10001|2021-06 | 85
10001|2021-07 | 32.0
20032|2021-08 | 63
20032|2021-09 | 44.23
20033|2021-11 | 10.90
I would like to transform this data to look something like this:
ID | 2021-06 | 2021-07 |2021-08 | 2021-09 | 2021-11
10001| 85 | 32 | 0 | 0 | 0
20032| 0 | 0 | 63 | 44.23 | 0
20033| 0 | 0 | 0 | 0 | 10.90
The totals will be on the columns based on the Year_Month column. Can someone help? I have tried using transpose but it did not work.
You should check out tidyverse package, it has some really good functions for data wrangling.
## Loading the required libraries
library(dplyr)
library(tidyverse)
## Creating the dataframe
df = data.frame(ID=c(10001,10001,20032,20032,20033),
Date=c('2021-06','2021-07','2021-08','2021-09','2021-11'),
Amount = c(85,32,63,44.2,10.9))
## Pivot longer to wider
df_pivot = df %>%
pivot_wider(names_from = Date, values_from = c(Amount))
## Replacing NA with 0
df_pivot[is.na(df_pivot)] = 0
df_pivot
# A tibble: 3 x 6
ID `2021-06` `2021-07` `2021-08` `2021-09` `2021-11`
<dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
1 10001 85 32 0 0 0
2 20032 0 0 63 44.2 0
3 20033 0 0 0 0 10.9