Les
August 26, 2019, 2:38pm
1
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)
Les
August 26, 2019, 5:22pm
3
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 REPR oducible EX ample (reprex) , simply replace df
by somedata
in the code.
Les
August 26, 2019, 7:01pm
5
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
Les
August 26, 2019, 8:52pm
7
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
system
Closed
September 2, 2019, 8:57pm
8
This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.