How to group by category when creating XMR columns?

I'm new to R, but so far have imported some data from SQL server. The source data has 3 columns as so:

Year Category Value
2001 CatA 17
2001 CatB 32
2002 CatA 13
2002 CatA 12
2003 CatB 11
2003 CatB 15

I have used this script to generate new columns: Order, Central Line, Moving Range, Av Moving Range, Lover process limit, upper process limit:

library("xmrr", lib.loc="E:/Program Files/R/R-3.5.3/library")
xmr_data <- xmr(df = somedata, measure = "Value")

What I am trying to do is calculate the XMR column values separately for each category, so I can filter the data to create a separate chart for each category. How can I group the data by category while creating the XMR columns?

Thanks
Leslie

Is this what you mean?

library(tidyverse)
library(xmrr)

df <- data.frame(stringsAsFactors=FALSE,
        Year = c(2001, 2001, 2002, 2002, 2003, 2003, 2001, 2001, 2002, 2002, 2003, 2003),
    Category = c("CatA", "CatB", "CatA", "CatA", "CatB", "CatB", "CatA", "CatB", "CatA", "CatA", "CatB", "CatB"),
       Value = c(17, 32, 13, 12, 11, 15, 17, 32, 13, 12, 11, 15)
    )

df %>% 
  group_nest(Category) %>% 
  pull(data) %>%
  setNames(nm = unique(df$Category)) %>% 
  map_dfr(~xmr(., measure = "Value"), .id = "Category")
#> # A tibble: 12 x 9
#>    Category  Year Value Order `Central Line` `Moving Range`
#>    <chr>    <dbl> <dbl> <dbl>          <dbl>          <dbl>
#>  1 CatA      2001    17     1           14.4             NA
#>  2 CatA      2002    13     2           14.4              4
#>  3 CatA      2002    12     3           14.4              1
#>  4 CatA      2001    17     4           14.4              5
#>  5 CatA      2002    13     5           14.4              4
#>  6 CatA      2002    12     6           14.4              1
#>  7 CatB      2001    32     1           20.2             NA
#>  8 CatB      2003    11     2           20.2             21
#>  9 CatB      2003    15     3           20.2              4
#> 10 CatB      2001    32     4           20.2             17
#> 11 CatB      2003    11     5           20.2             21
#> 12 CatB      2003    15     6           20.2              4
#> # … with 3 more variables: `Average Moving Range` <dbl>, `Lower Natural
#> #   Process Limit` <dbl>, `Upper Natural Process Limit` <dbl>

Created on 2019-08-26 by the reprex package (v0.3.0)

Thanks for responding andresrcs. This looks like what I'm trying to achieve, but I need to reference my existing dataset (eg df = somedata) rather than hard coding the data as you've done in your script. How could I do that with your script?

In my example df is just sample data for the purpose of making a REPRoducible EXample (reprex), simply replace df by somedata in the code.

I think I'm almost there... I've run the script as shown in the screenshot, but I only see the output in the console, showing the first 10 rows. How can I see the whole output?
Sorry I'm new to RStudio and R today!

If you just want to view the result in the data viewer add view() at the end of the code

df %>% 
    group_nest(Category) %>% 
    pull(data) %>%
    setNames(nm = unique(df$Category)) %>% 
    map_dfr(~xmr(., measure = "Value"), .id = "Category") %>% 
    view()

if you want to store the result, then assign it to a new variable

new_df <- df %>% 
    group_nest(Category) %>% 
    pull(data) %>%
    setNames(nm = unique(df$Category)) %>% 
    map_dfr(~xmr(., measure = "Value"), .id = "Category")
new_df

That's just what I needed. Thanks again.
My final working script is this:

library("xmrr", lib.loc="E:/Program Files/R/R-3.5.3/library")
library("tidyverse", lib.loc="E:/Program Files/R/R-3.5.3/library")
df <- data.frame(dataset)
Output <- df %>% 
     group_nest(Category) %>% 
    pull(data) %>%
     setNames(nm = unique(df$Category)) %>% 
    map_dfr(~xmr(., measure = "Value"), .id = "Category")

Using this in Power BI

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.