Arithmetic on nested dataframes

[Nilafhiosagam]

Hi all. I have a dataset where I would like to calculate cumulative sums, moving averages etc

Using iris I can nest by species

nested <- iris %>% 
  group_by(Species) %>% 
  nest()

And then

nested %>% 
  mutate(cumulative = map(data, cumsum)) %>% 
  unnest()

Which gives me a df with all the cumulative values at the end. Perfect

A tibble: 150 x 9
Groups: Species [3]
Species Sepal.Length Sepal.Width Petal.Length Petal.Width Sepal.Length1

1 setosa 5.1 3.5 1.4 0.2 5.1
2 setosa 4.9 3 1.4 0.2 10
3 setosa 4.7 3.2 1.3 0.2 14.7
4 setosa 4.6 3.1 1.5 0.2 19.3
5 setosa 5 3.6 1.4 0.2 24.3
6 setosa 5.4 3.9 1.7 0.4 29.7
7 setosa 4.6 3.4 1.4 0.3 34.3
8 setosa 5 3.4 1.5 0.2 39.3
9 setosa 4.4 2.9 1.4 0.2 43.7
10 setosa 4.9 3.1 1.5 0.1 48.6
... with 140 more rows, and 3 more variables: Sepal.Width1 ,
Petal.Length1 , Petal.Width1

I wanted to use movavg from the pracma package (with moving average of 6) on iris and have used the following and variations of the code with no luck. For example the below gives: Error in movavg(x = data, n = 6) : is.numeric(x) is not TRUE

nested %>% 
  mutate(movingaverage = map(data, movavg(n = 6))) %>% 
  unnest()

New to R and purrr in particular so any help would be much appreciated

[siddharthprabhu]

@Nilafhiosagam Your approach seems needlessly complicated. You don't need nest to achieve the desired result.

To calculate cumulative sums, how about this instead?

library(dplyr)

iris %>%
  group_by(Species) %>%
  mutate_all(.funs = cumsum)

# A tibble: 150 x 5
# Groups:   Species [3]
   Sepal.Length Sepal.Width Petal.Length Petal.Width Species
          <dbl>       <dbl>        <dbl>       <dbl> <fct>  
 1          5.1         3.5          1.4         0.2 setosa 
 2         10           6.5          2.8         0.4 setosa 
 3         14.7         9.7          4.1         0.6 setosa 
 4         19.3        12.8          5.6         0.8 setosa 
 5         24.3        16.4          7           1   setosa 
 6         29.7        20.3          8.7         1.4 setosa

The same approach can be used with other functions. I haven't used the pracma library but here's how to calculate rolling means using zoo.

library(zoo)

iris %>%
  group_by(Species) %>%
  mutate_all(.funs = rollmean, k = 6, fill = NA)

# A tibble: 150 x 5
# Groups:   Species [3]
   Sepal.Length Sepal.Width Petal.Length Petal.Width Species
          <dbl>       <dbl>        <dbl>       <dbl> <fct>  
 1        NA          NA           NA         NA     setosa 
 2        NA          NA           NA         NA     setosa 
 3         4.95        3.38         1.45       0.233 setosa 
 4         4.87        3.37         1.45       0.25  setosa 
 5         4.88        3.43         1.47       0.25  setosa 
 6         4.83        3.38         1.48       0.25  setosa

k defines the width of the rolling window as you'd expect. The fill parameter is required to ensure that the output contains the same number of rows as the input. You can change the fill value to suit your requirement.

Note: By default rollmean uses a center-aligned window. As a result, you will find rows of NA values at the top and bottom of the output data frame. If you'd rather have the NAs at the beginning instead, you can pass the align parameter like so:

iris %>%
  group_by(Species) %>%
  mutate_all(.funs = rollmean, k = 6, fill = NA, align = "right")

# A tibble: 150 x 5
# Groups:   Species [3]
   Sepal.Length Sepal.Width Petal.Length Petal.Width Species
          <dbl>       <dbl>        <dbl>       <dbl> <fct>  
 1        NA          NA           NA         NA     setosa 
 2        NA          NA           NA         NA     setosa 
 3        NA          NA           NA         NA     setosa 
 4        NA          NA           NA         NA     setosa 
 5        NA          NA           NA         NA     setosa 
 6         4.95        3.38         1.45       0.233 setosa

[Nilafhiosagam]

thank you very much, this is a much more straightforward approach

[siddharthprabhu]

@Nilafhiosagam Good to hear! If I've answered your question, would you mind marking my post as a solution?

FAQ: How do I mark a solution?

If your question has been answered, don't forget to mark the solution! How do I mark a solution? Find the reply you want to mark as the solution and look for the row of small gray icons at the bottom of that reply. Click the one that looks like a box with a checkmark in it: [image] Hovering over the mark solution button shows the label, "Select if this reply solves the problem". If you don't see the mark solution button, try clicking the three dots button ( ••• ) to expand the full set of options. When a solution is chosen, the icon turns green and the hover label changes to: "Unselect if this reply no longer solves the problem". Success! [solution_reply_author] …

[Nilafhiosagam]

Ah of course, thanks again