New table using dplyr /tidyr

General
,
1

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

There is a useful lag() function in dplyr package. For your task it can be applied something like this (supposed df is a name of this data frame):

library(dplyr)
df %>%
  mutate(
    Month_diff = paste0(lag(Month_considered), "-", Month_considered),
    pct_diff = pct - lag(pct)
  ) %>%
  tidyr::drop_na()

It creates new columns Month_diff and pct_diff instead of replacing previous ones. During the process first row is filled with NA values, so tidyr::drop_na() is one way to remove it. Also, Month_diff is a character column, and extra conversion to factor is needed if you really want that.

4 Likes