Moving Averages on multiple columns - Grouped Data
Apologies if this has been answered. I've gone through numerous examples today but I can't find any that match what I am trying to do.
I have a data set which I need to calculate a 3 point moving average on. I've generated some dummy data below:
set.seed(1234) data.frame(Week = rep(seq(1:5), 3), Section = c(rep("a", 5), rep("b", 5), rep("c", 5)), Qty = runif(15, min = 100, max = 500), To = runif(15, min = 40, max = 80))
I want to calculate the MA for each group based on the 'Section' column for both the 'Qty' and the 'To' columns. Ideally the output would be a data table. The moving average would start at Week 3 so would be the average of wks 1:3
I am trying to master the data.table package so a solution using that would be great but otherwise any will be much appreciated.
Just for reference my actual data set will have approx. 70 sections with c.1M rows in total. I've found the data.table to be extremely fast at crunching these kind of volumes so far.
We could use
rollmean from the
zoo package, in combination with
library(data.table) library(zoo) setDT(df)[, c("Qty.mean","To.mean") := lapply(.SD, rollmean, k = 3, fill = NA, align = "right"), .SDcols = c("Qty","To"), by = Section] > df # Week Section Qty To Qty.mean To.mean #1: 1 a 145.4814 73.49183 NA NA #2: 2 a 348.9198 51.44893 NA NA #3: 3 a 343.7099 50.67283 279.3703 58.53786 #4: 4 a 349.3518 47.46891 347.3271 49.86356 #5: 5 a 444.3662 49.28904 379.1426 49.14359 #6: 1 b 356.1242 52.66450 NA NA #7: 2 b 103.7983 52.10773 NA NA #8: 3 b 193.0202 46.36184 217.6476 50.37802 #9: 4 b 366.4335 41.59984 221.0840 46.68980 #10: 5 b 305.7005 48.75198 288.3847 45.57122 #11: 1 c 377.4365 72.42394 NA NA #12: 2 c 317.9899 61.02790 NA NA #13: 3 c 213.0934 76.58633 302.8400 70.01272 #14: 4 c 469.3734 73.25380 333.4856 70.28934 #15: 5 c 216.9263 41.83081 299.7977 63.89031
Solved: How can I group multiple columns by year-month in , How can I group multiple columns by year-month in the dataflow? The very end goal is to create a Simple Moving Average Calculation with a 3 Set Column Type: Set your newly created column to a data type of date with 1.Select the data range and click Kutools > Insert > Insert Blank Row & Columns, see screenshot:. 2.In the Insert Blank Rows & Columns dialog box, select the Blank rows or Blank columns under the Insert type that you want to insert, then from the Options section, enter the interval number that you want to insert blank rows or columns between, and then type the number of blank rows or columns
A solution using dplyr:
library(dplyr); library(zoo) myfun = function(x) rollmean(x, k = 3, fill = NA, align = "right") df %>% group_by(Section) %>% mutate_each(funs(myfun), Qty, To) #### Week Section Qty To #### (int) (fctr) (dbl) (dbl) #### 1 1 a NA NA #### 2 2 a NA NA #### 3 3 a 279.3703 58.53786 #### 4 4 a 347.3271 49.86356
Calculate Moving Average over X days across multip, Hi All, I've been working with some data for COVID-19 and had begun Calculate Moving Average over X days across multiple columns and Now the groupby method first splits the data by Type, and data within each Type group is further split into subgroups by the values in the Test_1_Grade column. The averages may look a little funny in our Test_2 column because of how we composed our DataFrame, but the groupby methods successfully performs its job.
There is currently faster approach using new
frollmean function in data.table 1.12.0.
setDT(df)[, c("Qty.mean","To.mean") := frollmean(.SD, 3), .SDcols = c("Qty","To"), by = Section]
rolling functions, rolling aggregates, sliding window, moving , rolling functions, rolling aggregates, sliding window, moving average #2778 use openmp to parallelize calculation of multiple columns/windows K-fold split: view data as multiple groups where each group excludes only a In the final output, I don’t want all the monthly sales data to be displayed, I want only quarterly & yearly sales data to be displayed, it can be done through multiple grouping of column options. Initially, I need to select the column which I want to group it, now let’s select the months (i.e. Jan, Feb, Mar columns).
SQL Puzzle: How to Calculate Moving Averages, Today, we're going to learn how to calculate moving averages by working a We also discuss puzzle and more in Essential SQL Learning Group on FaceBook. Download this script to create the table and populate it with sample data. in the result; the average is a one day, two days, three-day moving average, and so I want to calculate the MA for each group based on the 'Section' column for both the 'Qty' and the 'To' columns. Ideally the output would be a data table. The moving average would start at Week 3 so would be the average of wks 1:3. I am trying to master the data.table package so a solution using that would be great but otherwise any will be
Moving Averages in Excel (Examples), Moving Average in Excel is used to find the average of rolling iteration data by using the AVERAGE function in multiple iterations. Step 1: Select the data and insert the column chart. VLOOKUP Tutorial in Excel · Moving Columns in Excel · Grouping Columns in Excel · Excel Formula of Percentage · Excel Leading Zeros But the actual Mode may not even be in that group! Or there may be more than one mode. Without the raw data we don't really know. But, we can estimate the Mode using the following formula: Estimated Mode = L + f m − f m-1 (f m − f m-1) + (f m − f m+1) × w. where: L is the lower class boundary of the modal group ; f m-1 is the frequency
Create a Moving Average on an Event Stream Using an Aggregation , Add an aggregate window to create a moving average on the event data, How to add an aggregate window to hold multiple data entry and observe patterns GROUP: Get Started with SAP HANA Streaming Analytics for SAP HANA, express edition Choose Copy Columns from Input from the menu to open the column Booklet with assessments at the start and end of the lesson on: - Averages from a list - Mean from a frequency table - Mode, Range, Median from a frequency table - Averages from grouped frequency Read more
- See also here for some more options
- did you use the same seed as the OP?
- @mtoto thanks for the quick reply, it's exactly what I needed!!
- thank you! It's good to see how you can you solve the same problem multiple ways.