Format/Generate new table in R using dplyr/tidyr

2
votes

I have a table calculated using a df which looks like the following.

 Month_considered   pct `ATC Count`
   <fct>            <dbl> <fct>      
 1 Apr-17            54.9 198,337    
 2 May-17            56.4 227,681    
 3 Jun-17            58.0 251,664    
 4 Jul-17            57.7 251,934    
 5 Aug-17            55.5 259,617    
 6 Sep-17            55.7 245,588    
 7 Oct-17            56.6 247,051    
 8 Nov-17            57.6 256,375    
 9 Dec-17            56.9 277,784    
10 Jan-18            56.7 272,818

Now I want to find the difference in pct between two months.So the desired output would be like

 Month_considered          pct 
   <fct>                   <dbl>    
 1 Apr-17-May-17            1.5    
 2 May-17-Jun-17            1.6   
 3 Jun-17-Jul-17           - 0.3

How do I concatenate the first column like above. I did try using unite in tidyr but it isnt the output what I want to generate.Thank you.

2
rdplyrtidyr

2 Answers

4
votes

We need to take the difference between the current and the next value

library(dplyr)
library(zoo)
df1 %>%
    arrange(as.yearmon(Month_considered, format = "%b-%y")) %>% # to order
    mutate_at(vars(Month_considered, pct),
              funs(new = lead(., default = last(.)))) %>% 
    unite(Month_considered, Month_considered, Month_considered_new, sep="-") %>% 
    transmute(Month_considered, pct = pct_new - pct)
#   Month_considered  pct
#1     Apr-17-May-17  1.5
#2     May-17-Jun-17  1.6
#3     Jun-17-Jul-17 -0.3
#4     Jul-17-Aug-17 -2.2
#5     Aug-17-Sep-17  0.2
#6     Sep-17-Oct-17  0.9
#7     Oct-17-Nov-17  1.0
#8     Nov-17-Dec-17 -0.7
#9     Dec-17-Jan-18 -0.2
#10    Jan-18-Jan-18  0.0

Or using base R

pct <- df1$pct[-1] - df1$pct[-nrow(df1)]
Month_considered <- paste(df1$Month_considered[-1], 
             df1$Month_considered[-nrow(df1)], sep="-")

data.frame(Month_considered, pct)
4
votes

One can try using self-join after adding 1 month from zoo::yearmon type column.

To add a month in yearmon type column simply add 1/12.

The solution is:

library(zoo)
library(dplyr)

df %>% mutate(Month_considered = as.yearmon(Month_considered, "%b-%y"), 
              Next_Month = Month_considered+(1/12)) %>%
  #self join
  left_join(.,.,by=c("Next_Month"="Month_considered")) %>%
  mutate(Month_considered = paste(Month_considered,Next_Month,sep="-"), 
         pct = pct.y - pct.x) %>%
  select(Month_considered, pct)

#     Month_considered  pct
# 1  Apr 2017-May 2017  1.5
# 2  May 2017-Jun 2017  1.6
# 3  Jun 2017-Jul 2017 -0.3
# 4  Jul 2017-Aug 2017 -2.2
# 5  Aug 2017-Sep 2017  0.2
# 6  Sep 2017-Oct 2017  0.9
# 7  Oct 2017-Nov 2017  1.0
# 8  Nov 2017-Dec 2017 -0.7
# 9  Dec 2017-Jan 2018 -0.2
# 10 Jan 2018-Feb 2018   NA

Data:

df <- read.table(text=
"Month_considered   pct 'ATC Count'
Apr-17            54.9 198337    
May-17            56.4 227681    
Jun-17            58.0 251664    
Jul-17            57.7 251934    
Aug-17            55.5 259617    
Sep-17            55.7 245588    
Oct-17            56.6 247051    
Nov-17            57.6 256375    
Dec-17            56.9 277784    
Jan-18            56.7 272818",
header=TRUE, stringsAsFactors = FALSE)