I have a dataframe with multiple columns of months and total (13 columns in total) and two periods (half a year, labeled as 1 and 7).
I am trying to spread it wide and have it presented as January1 and January7 (for all 12 months + Total and Total1). And then I will calculate the difference between periods.
Please advise how to do it.
I tried spread(), old Hadley's function, but these multiple months complicate everything with keys and values. Also failed with pivot_wide().
Obviously tried multiple questions like this.
My data sample is here.
structure(list(Year = c(2019, 2019, 2019, 2019, 2019), Period = c(1,
1, 7, 1, 7), KPKV = c(99999, 110000, 111000, 111010, 111010),
KEKV = c(4, 1, 1, 2, 2), Name = c("A", "B", "B", "B", "B"
), January = c(70198346.4, 125181.4, 125181.4, 64008.4, 34374.1
), February = c(71052496.2, 127697.1, 127697.1, 66007.3,
34719.1), March = c(96884031.5, 142375.3, 142375.3, 75510.2,
38082.1), April = c(74389605.4, 139627.8, 139627.8, 75891.9,
37262.5), May = c(101876908, 144649.4, 144649.4, 79889.6,
41150), June = c(86362730.8, 178706.8, 178706.8, 96616, 49727.9
), July = c(74326532.8, 178708.4, 178708.4, 96616, 55955.7
), August = c(80052666.3, 186225.8, 186225.8, 102606.5, 30816.8
), September = c(90236044.8, 182131, 182131, 102885.7, 49123.1
), October = c(79077964, 175287.8, 175287.8, 101166.1, 49942.8
), November = c(92509081.2, 185182.1, 185182.1, 109051.8,
37609.2), December = c(88801141.2, 198270.2, 198270.2, 119648,
37609.2), Total = c(1005767549, 1964043.1, 1964043.1, 1089897.5,
496372.5)), class = c("spec_tbl_df", "tbl_df", "tbl", "data.frame"
), row.names = c(NA, -5L), spec = structure(list(cols = list(
Year = structure(list(), class = c("collector_double", "collector"
)), Period = structure(list(), class = c("collector_double",
"collector")), KPKV = structure(list(), class = c("collector_double",
"collector")), KEKV = structure(list(), class = c("collector_double",
"collector")), Name = structure(list(), class = c("collector_character",
"collector")), January = structure(list(), class = c("collector_double",
"collector")), February = structure(list(), class = c("collector_double",
"collector")), March = structure(list(), class = c("collector_double",
"collector")), April = structure(list(), class = c("collector_double",
"collector")), May = structure(list(), class = c("collector_double",
"collector")), June = structure(list(), class = c("collector_double",
"collector")), July = structure(list(), class = c("collector_double",
"collector")), August = structure(list(), class = c("collector_double",
"collector")), September = structure(list(), class = c("collector_double",
"collector")), October = structure(list(), class = c("collector_double",
"collector")), November = structure(list(), class = c("collector_double",
"collector")), December = structure(list(), class = c("collector_double",
"collector")), Total = structure(list(), class = c("collector_double",
"collector"))), default = structure(list(), class = c("collector_guess",
"collector")), skip = 1), class = "col_spec"))
UPDATED:
After using the first solution, the data was transformed, but not everything was done properly. Some columns were missing.
I believe it happened because KPKV column is unique, but KEKV column can have multiple values under the same KPKV.
MY EXPECTED OUTPUT
structure(list(Year = 2019, KPKV = 99999, KEKV = 4, Name = "Random name",
April1 = 74389605.4, April7 = NA_real_, August1 = 80052666.3,
August7 = NA_real_, December1 = 88801141.2, December7 = NA_real_,
February1 = 71052496.2, February7 = NA_real_, January1 = 70198346.4,
January7 = NA_real_, July1 = 74326532.8, July7 = NA_real_,
June1 = 86362730.8, June7 = NA_real_, March1 = 96884031.5,
March7 = NA_real_, May1 = 101876908, May7 = NA_real_, November1 =
92509081.2,
November7 = NA_real_, October1 = 79077964, October7 = NA_real_,
September1 = 90236044.8, September7 = NA_real_, Total1 = 1005767548.6,
Total7 = NA_real_), row.names = 1L, class = "data.frame")
An option is pivot_wider from the devel version of tidyr
library(tidyr) #‘0.8.3.9000’
library(dplyr)
df1 %>%
pivot_wider(id_cols = Name, names_from = Period,
values_from = c(January:December), names_sep = "")
# A tibble: 2 x 25
# Name January1 January7 February1 February7 March1 March7 April1 April7 May1 May7 June1 June7 July1 July7 August1 August7 September1 September7
# <chr> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
#1 A 7.02e7 125181. 71052496. 127697. 9.69e7 1.42e5 7.44e7 1.40e5 1.02e8 #1.45e5 8.64e7 1.79e5 7.43e7 1.79e5 8.01e7 186226. 90236045. 182131
#2 B 1.25e5 64008. 127697. 66007. 1.42e5 7.55e4 1.40e5 7.59e4 1.45e5 7.99e4 1.79e5 9.66e4 1.79e5 9.66e4 1.86e5 102606. 182131 102886.
# … with 6 more variables: October1 <dbl>, October7 <dbl>, November1 <dbl>, November7 <dbl>, December1 <dbl>, December7 <dbl>
Or with previous tidyr version
library(dplyr)
library(tidyr)
df1 %>%
gather(key, val, January:December) %>%
unite(key, key, Period, sep="") %>%
spread(key, val)
Or data.table option
library(data.table)
dcast(setDT(df1), Name + KPKV + Year ~ Period, value.var = month.name, sep="")