How can i sum values of 1 column based on the categories of another column, multiple times, in R?-CodePudding

I guess my question its a little strange, let me try to explain it. I need to solve a simple equation for a longitudinal database (29 consecutive years) about food availability and international commerce: (importations-exportations)/(production importations-exportations)*100[equation for food dependence coeficient, by FAO]. The big problem is that my database has the food products and its values of interest (production, importation and exportation) dissagregated, so i need to find a way to apply that equation to a sum of the values of interest for every year, so i can get the coeficient i need for every year.

My data frame looks like this:

element      product   year   value (metric tons)
Production   Wheat     1990   16
Importation  Wheat     1990   2
Exportation  Wheat     1990   1
Production   Apples    1990   80
Importation  Apples    1990   0
Exportation  Apples    1990   72
Production   Wheat     1991   12
Importation  Wheat     1991   20
Exportation  Wheat     1991   0

I guess the solution its pretty simple, but im not good enough in R to solve this problem by myself. Every help is very welcome.

Thanks!

This is a picture of my R session

CodePudding user response：

require(data.table)

# dummy table. Use setDT(df) if yours isn't a data table already
df <- data.table(element = (rep(c('p', 'i', 'e'), 3))
                 , product = (rep(c('w', 'a', 'w'), each=3))
                 , year = rep(c(1990, 1991), c(6,3))
                 , value = c(16,2,1,80,0,72,12,20,0)
                 ); df

   element product year value
1:       p       w 1990    16
2:       i       w 1990     2
3:       e       w 1990     1
4:       p       a 1990    80
5:       i       a 1990     0
6:       e       a 1990    72
7:       p       w 1991    12
8:       i       w 1991    20
9:       e       w 1991     0



# long to wide
df_1 <- dcast(df
              , product   year ~ element
              , value.var = 'value'
              ); df_1

# apply calculation
df_1[, food_depend_coef := (i-e) / (p i-e)*100][]

   product year  e  i  p food_depend_coef
1:       a 1990 72  0 80      -900.000000
2:       w 1990  1  2 16         5.882353
3:       w 1991  0 20 12        62.500000