I have a data frame that I am trying to group by a column and pivot_wider. Here is my data frame
df = structure(list(Date = structure(c(1608238800, 1608238800, 1608238800,
1608238802, 1608238800, 1608238800, 1608238802, 1608238800, 1608238800,
1608238802, 1608238802, 1608238800, 1608238800, 1608238800, 1608238802,
1608238800, 1608238800, 1608238800), tzone = "America/New_York", class = c("POSIXct",
"POSIXt")), Last = c(56.15, 7.9, 196.29, 311, 458.07, 18.38,
377.08, 70.61, 4.02, 372.19, 15.7, 391.2, 9.1, 66.62, 176.25,
6.9, 28.74, 128.7), ticker = c("FAS", "FAZ", "IWM", "QQQ", "SOXL",
"SOXS", "SOXX", "SPXL", "SPXS", "SPY", "SQQQ", "TECL", "TECS",
"TNA", "TQQQ", "TZA", "XLF", "XLK"), Core = c("XLF", "XLF", "IWM",
"QQQ", "SOXX", "SOXX", "SOXX", "SPY", "SPY", "SPY", "QQQ", "XLK",
"XLK", "IWM", "QQQ", "IWM", "XLF", "XLK"), Type = c("ETNBull",
"ETNBear", "ETF1x", "ETF1x", "ETNBull", "ETNBear", "ETF1x", "ETNBull",
"ETNBear", "ETF1x", "ETNBear", "ETNBull", "ETNBear", "ETNBull",
"ETNBull", "ETNBear", "ETF1x", "ETF1x")), row.names = c(NA, -18L
), groups = structure(list(ticker = c("FAS", "FAZ", "IWM", "QQQ",
"SOXL", "SOXS", "SOXX", "SPXL", "SPXS", "SPY", "SQQQ", "TECL",
"TECS", "TNA", "TQQQ", "TZA", "XLF", "XLK"), .rows = structure(list(
1L, 2L, 3L, 4L, 5L, 6L, 7L, 8L, 9L, 10L, 11L, 12L, 13L, 14L,
15L, 16L, 17L, 18L), ptype = integer(0), class = c("vctrs_list_of",
"vctrs_vctr", "list"))), row.names = c(NA, 18L), class = c("tbl_df",
"tbl", "data.frame"), .drop = TRUE), class = c("grouped_df",
"tbl_df", "tbl", "data.frame"))
head(df)
Date Last ticker Core Type
1 2020-12-17 56.2 FAS XLF ETNBull
2 2020-12-17 7.9 FAZ XLF ETNBear
3 2020-12-17 196. IWM IWM ETF1x
4 2020-12-17 311 QQQ QQQ ETF1x
5 2020-12-17 458. SOXL SOXX ETNBull
6 2020-12-17 18.4 SOXS SOXX ETNBear
I would like to group by "Core" and pivot wider using Type as the new columns and ticker as the values. For example, i would like my new data frame to look like this
Date Last Core ETNBull ETNBear ETF1x
1 2020-12-17 56.2 XLF FAS FAZ XLF
2 2020-12-17 7.9 IWM TNA TZA IWM
.
.
You can see we grouped by "Core" and then spread by "Type" using 'ticker' as the values.
Here is an example of one attempt that did not have the desired results
df %>% group_by(Core) %>% pivot_wider(names_from = Type, values_from = ticker)
