CODEWITHSUNDEEP
rdataframe
EconRA
EconRA

Reputation: 29

Calculating yearly growth-rates from quarterly, long form data in r

My data takes the following form:

df <- data.frame(Sector=c(rep("A",8),rep("B",8)), Country = c(rep("USA", 16)),
                 Quarter=rep(1:8,2),Income=20:35)
df2 <- data.frame(Sector=c(rep("A",8),rep("B",8)), Country = c(rep("UK", 16)),
                 Quarter=rep(1:8,2),Income=32:47)
df <- rbind(df, df2)

What I want to do is to calculate the growth rate from the first quarter each year to the first quarter the second year, within country and sector. In the example above it would be the growth rate from quarter 1 to quarter 5. So for Sector A, in the USA, it would be (24/20)-1=0.2

I then want to append this data to the dataframe as a new column.

I looked at the solutions in: How calculate growth rate in long format data frame?

But didn't have the r-skills to get it to work if the lag is more then one time-unit. Any suggestions?

ADDITION

So what i want is the growth-rate, that is (24/20)-1=0.2 in the example below. Not 1-(24/20), which I first wrote. The desired output should look something like this:

  Sector Country Quarter Income     growth
   (fctr)  (fctr)   (int)  (int)      (dbl)
1       A     USA       1     20    NA
2       A     USA       2     21    NA
3       A     USA       3     22    NA
4       A     USA       4     23    NA
5       A     USA       5     24    0.2
6       A     USA       6     25    0.1904
7       A     USA       7     26    0.1818

Upvotes: 1

Views: 2217

Answers (2)

LyzandeR
LyzandeR

Reputation: 37879

I think you need something like this:

library(dplyr)
df %>%
  #group by sector and country
  group_by(Sector, Country) %>%
  #calculate growth as (quarter / 5-period-lagged quarter) - 1
  mutate(growth = Income / lag(Income, 4) - 1)

Output

Source: local data frame [32 x 5]
Groups: Sector, Country [4]

   Sector Country Quarter Income    growth
   (fctr)  (fctr)   (int)  (int)     (dbl)
1       A     USA       1     20        NA
2       A     USA       2     21        NA
3       A     USA       3     22        NA
4       A     USA       4     23        NA
5       A     USA       5     24 0.2000000
6       A     USA       6     25 0.1904762
7       A     USA       7     26 0.1818182
8       A     USA       8     27 0.1739130
9       B     USA       1     28        NA
10      B     USA       2     29        NA
..    ...     ...     ...    ...       ...

Upvotes: 1

TheComeOnMan
TheComeOnMan

Reputation: 12875

df3 = copy(df)
df3$Quarter = df3$Quarter - 4
df = merge(df,df3,c('Sector','Country','Quarter'), suffixes = c('','_prev'), all.x = T)
df$growth = 1 - (df$Income_prev/df$Income


> df
   Sector Country Quarter Income Income_prev growth
1       A     USA       1     20          24     -4
2       A     USA       2     21          25     -4
3       A     USA       3     22          26     -4
4       A     USA       4     23          27     -4
5       A     USA       5     24          NA     NA
6       A     USA       6     25          NA     NA
7       A     USA       7     26          NA     NA
8       A     USA       8     27          NA     NA
9       A      UK       1     32          36     -4
10      A      UK       2     33          37     -4
11      A      UK       3     34          38     -4
12      A      UK       4     35          39     -4
13      A      UK       5     36          NA     NA
14      A      UK       6     37          NA     NA
15      A      UK       7     38          NA     NA
16      A      UK       8     39          NA     NA
17      B     USA       1     28          32     -4
18      B     USA       2     29          33     -4
19      B     USA       3     30          34     -4
20      B     USA       4     31          35     -4
21      B     USA       5     32          NA     NA
22      B     USA       6     33          NA     NA
23      B     USA       7     34          NA     NA
24      B     USA       8     35          NA     NA
25      B      UK       1     40          44     -4
26      B      UK       2     41          45     -4
27      B      UK       3     42          46     -4
28      B      UK       4     43          47     -4
29      B      UK       5     44          NA     NA
30      B      UK       6     45          NA     NA
31      B      UK       7     46          NA     NA
32      B      UK       8     47          NA     NA
>

Upvotes: 1

Related Questions