Calculating the percentage change and put it in a table

Question

I have a data frame with three columns, one with years and the rest are just anonymized data. I would like to calculate the percentage change through years and put it in a table. Take a look.

#here's my data
data<-structure(list(Year = c(2012, 2012, 2012, 2012, 2012, 2012, 2012, 
                          2012, 2012, 2012, 2012, 2012, 2012, 2012, 2012, 2013, 2013, 2013, 
                          2013, 2013, 2013, 2013, 2013, 2013, 2013, 2013, 2013, 2013, 2013, 
                          2013, 2014, 2014, 2014, 2014, 2014, 2014, 2014, 2014, 2014, 2014, 
                          2014, 2014, 2014, 2014, 2014, 2015, 2015, 2015, 2015, 2015, 2015, 
                          2015, 2015, 2015, 2015, 2015, 2015, 2015, 2015, 2015, 2015, 2016, 
                          2016, 2016, 2016, 2016, 2016, 2016, 2016, 2016, 2016, 2016, 2016, 
                          2016, 2016, 2016, 2016, 2017, 2017, 2017, 2017, 2017, 2017, 2017, 
                          2017, 2017, 2017, 2017, 2017, 2017, 2017, 2017, 2017), V2 = c("O1", 
                                                                                        "O2", "O3", "O4", "O5", "O6", "O7", "O8", "O9", "O10", "O12", 
                                                                                        "O13", "O14", "O15", "O16", "O1", "O2", "O3", "O4", "O5", "O6", 
                                                                                        "O7", "O8", "O9", "O10", "O12", "O13", "O14", "O15", "O16", "O1", 
                                                                                        "O2", "O3", "O4", "O5", "O6", "O7", "O8", "O9", "O10", "O12", 
                                                                                        "O13", "O14", "O15", "O16", "O1", "O2", "O3", "O4", "O5", "O6", 
                                                                                        "O7", "O8", "O9", "O10", "O11", "O12", "O13", "O14", "O15", "O16", 
                                                                                        "O1", "O2", "O3", "O4", "O5", "O6", "O7", "O8", "O9", "O10", 
                                                                                        "O11", "O12", "O13", "O14", "O15", "O16", "O1", "O2", "O3", "O4", 
                                                                                        "O5", "O6", "O7", "O8", "O9", "O10", "O11", "O12", "O13", "O14", 
                                                                                        "O15", "O16"), KOW = c(778.4, 37586.5, 6160.7, 260.9, 50812.1, 
                                                                                                               24419.6, 30179.4, 2733.2, 59518.7, 14696, 20926.6, 35195.2, 21446.7, 
                                                                                                               5068.9, 7276.2, 1014.1, 38691, 6765.8, 154.8, 43218.9, 31110.8, 
                                                                                                               20114.9, 56.7, 69116.4, 13787.4, 13483.3, 33735.9, 22899.1, 2977.8, 
                                                                                                               5127.1, 926.4, 61612.4, 7532.3, 194.9, 41324.9, 38248.7, 28609.8, 
                                                                                                               959.3, 58435.6, 16506.3, 11754.4, 40769, 30958.6, 3408.2, 5354.5, 
                                                                                                               1355.4, 58311.9, 9413.2, 206.2, 42858.4, 44511.2, 24667.4, 78.2, 
                                                                                                               66650.9, 16220.4, 17, 11940.6, 38096.1, 35791.9, 2949.3, 8220.7, 
                                                                                                               826.6, 66431.7, 15258.7, 286.4, 47960.6, 38507.8, 31062.2, 773.6, 
                                                                                                               73581.2, 19931.6, 0.5, 14735.4, 38361.2, 39286.8, 2904.3, 7993.1, 
                                                                                                               576.4, 66232, 16049.1, 335.2, 46367.2, 37431.9, 25392.7, 2232, 
                                                                                                               81797.7, 22793.7, 0.5, 16569.4, 37323, 55369.2, 3666, 6604.7)), row.names = c(NA, 
                                                                                                                                                                                             -93L), vars = "Year", drop = TRUE, class = c("grouped_df", "tbl_df", 
                                                                                                                                                                                                                                          "tbl", "data.frame"))


#Year   V2  KOW

#2012   O1  778.4
#2012   O2  37586.5
#2012   O3  6160.7
#2012   O4  260.9
#2012   O5  50812.1
#2012   O6  24419.6
#2012   O7  30179.4
             .
             .
             .

I was able to calculate the percentage change

pct_chg<-data%>%
  group_by(V2)%>%
  arrange(Year, .by_group=TRUE)%>%
  mutate("+/-" = (KOW/lag(KOW)-1)*100)

#Year   V2  KOW       +/-

#2012   O1  778.4     NA
#2013   O1  1014.1  30.28
#2014   O1  926.4   -8.65
#2015   O1  1355.4  46.31
                   .
                   .
                   .

what I couldn't do is put it into a table in the following format, omitting the 2012 year

#V2    2013     +/-    2014      +/-    2015     ....
#O1    1014.1  -8.65   926.4    46.31   1355.4   ....
#02
#03     
.
.
.
#Please help

If you really wanted to, you could do a `gather`, then split the years up, then the `+/-`, then do something like a `bind_cols`. It would be a bit of a pain in the arse though. — william3031, Jun 12 '19 at 10:03
Did you try `filter(year != 2012)` when making your table? I may be oversimplifying — H5470, Jun 12 '19 at 14:05
Using `tidyverse` you can do `pct_chg %>% filter(Year != 2012) %>% gather(key, value, -Year, -V2) %>% unite(Year, Year, key) %>% spread(Year, value)` — Ronak Shah, Jun 13 '19 at 03:08

Calculating the percentage change and put it in a table

0 Answers0