Hello,
I have two dataframes that I need to convert to long format to use for a pivot. The actual scores that I need to show on the pivot are contained within df_score_long while the formatting that should apply to them should come from df_test_long .
As you will see we can combine them and I have done that in df_combined_long . At the bottom you will see I have my expected pivot I want to run (greyed it out for the reprex as it creates a problem given it is an html object).
What I want to accomplish is the following: if a row has a 0 in the qualify_value column then for the pivot that aggregate number should be in grey even if the rest of the numbers associated with say S1 is not 0. I would like to make use of the existing pivot functions I have so ideally this formatting could be added on top of it.
library(tidyverse)
library(data.table)
#>
#> Attaching package: 'data.table'
#> The following objects are masked from 'package:dplyr':
#>
#> between, first, last
#> The following object is masked from 'package:purrr':
#>
#> transpose
library(radiant.data)
#> Loading required package: magrittr
#>
#> Attaching package: 'magrittr'
#> The following object is masked from 'package:purrr':
#>
#> set_names
#> The following object is masked from 'package:tidyr':
#>
#> extract
#> Loading required package: lubridate
#>
#> Attaching package: 'lubridate'
#> The following objects are masked from 'package:data.table':
#>
#> hour, isoweek, mday, minute, month, quarter, second, wday, week,
#> yday, year
#> The following objects are masked from 'package:base':
#>
#> date, intersect, setdiff, union
#>
#> Attaching package: 'radiant.data'
#> The following objects are masked from 'package:lubridate':
#>
#> month, wday
#> The following objects are masked from 'package:data.table':
#>
#> month, wday
#> The following object is masked from 'package:forcats':
#>
#> as_factor
#> The following objects are masked from 'package:purrr':
#>
#> is_double, is_empty, is_numeric
#> The following object is masked from 'package:ggplot2':
#>
#> diamonds
#> The following object is masked from 'package:base':
#>
#> date
library(dt)
#> Error in library(dt): there is no package called 'dt'
df_score <- data.table::data.table(
ID = c(
1, 2, 3, 4, 5, 6, 7, 8, 9, 10,
11, 12, 13, 14, 15, 16, 17, 18, 19, 20
),
S_1 = c(
1, 8, 10, 2, 3, 5, 4, 9, 5, 3, 3,
6, 10, 2, 10, 2, 3, 3, 6, 3
),
S_2 = c(
9, 9, 3, 6, 2, 7, 9, 1, 10, 6, 8,
7, 7, 6, 3, 7, 5, 3, 1, 10
),
S_3 = c(
3, 6, 8, 3, 1, 6, 3, 7, 1, 5, 7,
9, 4, 3, 6, 3, 5, 3, 4, 8
),
S_4 = c(
1, 1, 2, 9, 9, 1, 7, 3, 7, 2, 5,
2, 9, 8, 4, 8, 1, 3, 1, 4
),
S_5 = c(
8, 4, 6, 10, 7, 5, 8, 1, 9, 6, 3,
7, 3, 1, 3, 3, 9, 8, 4, 2
)
)
df_test <- data.frame(
ID = c(
1, 2, 3, 4, 5, 6, 7, 8, 9, 10,
11, 12, 13, 14, 15, 16, 17, 18, 19, 20
),
T_1 = c(
0, 1, 1, 0, 1, 1, 0, 0, 1, 0, 1, 1, 0, 1, 1, 0, 0, 1,
1, 0
),
T_2 = c(
1, 0, 0, 1, 1,
1, 1, 1, 1, 1, 1, 0, 1, 0, 0, 1, 1, 1,
0, 1
),
T_3 = c(
1, 1, 0, 0, 0,
1, 1, 1, 0, 0, 1, 0, 1, 0, 1, 0, 1, 1,
1, 0
),
T_4 = c(
0, 0, 1, 0, 1,
1, 0, 0, 1, 0, 1, 1, 1, 1, 1, 1, 0, 1,
1, 0
),
T_5 = c(
1, 1, 1, 0, 1,
1, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0,
1, 1
)
)
df_score_long <- df_score %>% pivot_longer(!ID, names_to = "score", values_to = "score_value")
df_test_long <- df_test %>% pivot_longer(!ID, names_to = "qualify", values_to = "qualify_value")
df_combined_long <- cbind(df_score_long,df_test_long)
df_combined_long
#> ID score score_value ID qualify qualify_value
#> 1 1 S_1 1 1 T_1 0
#> 2 1 S_2 9 1 T_2 1
#> 3 1 S_3 3 1 T_3 1
#> 4 1 S_4 1 1 T_4 0
#> 5 1 S_5 8 1 T_5 1
#> 6 2 S_1 8 2 T_1 1
#> 7 2 S_2 9 2 T_2 0
#> 8 2 S_3 6 2 T_3 1
#> 9 2 S_4 1 2 T_4 0
#> 10 2 S_5 4 2 T_5 1
#> 11 3 S_1 10 3 T_1 1
#> 12 3 S_2 3 3 T_2 0
#> 13 3 S_3 8 3 T_3 0
#> 14 3 S_4 2 3 T_4 1
#> 15 3 S_5 6 3 T_5 1
#> 16 4 S_1 2 4 T_1 0
#> 17 4 S_2 6 4 T_2 1
#> 18 4 S_3 3 4 T_3 0
#> 19 4 S_4 9 4 T_4 0
#> 20 4 S_5 10 4 T_5 0
#> 21 5 S_1 3 5 T_1 1
#> 22 5 S_2 2 5 T_2 1
#> 23 5 S_3 1 5 T_3 0
#> 24 5 S_4 9 5 T_4 1
#> 25 5 S_5 7 5 T_5 1
#> 26 6 S_1 5 6 T_1 1
#> 27 6 S_2 7 6 T_2 1
#> 28 6 S_3 6 6 T_3 1
#> 29 6 S_4 1 6 T_4 1
#> 30 6 S_5 5 6 T_5 1
#> 31 7 S_1 4 7 T_1 0
#> 32 7 S_2 9 7 T_2 1
#> 33 7 S_3 3 7 T_3 1
#> 34 7 S_4 7 7 T_4 0
#> 35 7 S_5 8 7 T_5 0
#> 36 8 S_1 9 8 T_1 0
#> 37 8 S_2 1 8 T_2 1
#> 38 8 S_3 7 8 T_3 1
#> 39 8 S_4 3 8 T_4 0
#> 40 8 S_5 1 8 T_5 0
#> 41 9 S_1 5 9 T_1 1
#> 42 9 S_2 10 9 T_2 1
#> 43 9 S_3 1 9 T_3 0
#> 44 9 S_4 7 9 T_4 1
#> 45 9 S_5 9 9 T_5 0
#> 46 10 S_1 3 10 T_1 0
#> 47 10 S_2 6 10 T_2 1
#> 48 10 S_3 5 10 T_3 0
#> 49 10 S_4 2 10 T_4 0
#> 50 10 S_5 6 10 T_5 0
#> 51 11 S_1 3 11 T_1 1
#> 52 11 S_2 8 11 T_2 1
#> 53 11 S_3 7 11 T_3 1
#> 54 11 S_4 5 11 T_4 1
#> 55 11 S_5 3 11 T_5 0
#> 56 12 S_1 6 12 T_1 1
#> 57 12 S_2 7 12 T_2 0
#> 58 12 S_3 9 12 T_3 0
#> 59 12 S_4 2 12 T_4 1
#> 60 12 S_5 7 12 T_5 0
#> 61 13 S_1 10 13 T_1 0
#> 62 13 S_2 7 13 T_2 1
#> 63 13 S_3 4 13 T_3 1
#> 64 13 S_4 9 13 T_4 1
#> 65 13 S_5 3 13 T_5 1
#> 66 14 S_1 2 14 T_1 1
#> 67 14 S_2 6 14 T_2 0
#> 68 14 S_3 3 14 T_3 0
#> 69 14 S_4 8 14 T_4 1
#> 70 14 S_5 1 14 T_5 0
#> 71 15 S_1 10 15 T_1 1
#> 72 15 S_2 3 15 T_2 0
#> 73 15 S_3 6 15 T_3 1
#> 74 15 S_4 4 15 T_4 1
#> 75 15 S_5 3 15 T_5 0
#> 76 16 S_1 2 16 T_1 0
#> 77 16 S_2 7 16 T_2 1
#> 78 16 S_3 3 16 T_3 0
#> 79 16 S_4 8 16 T_4 1
#> 80 16 S_5 3 16 T_5 0
#> 81 17 S_1 3 17 T_1 0
#> 82 17 S_2 5 17 T_2 1
#> 83 17 S_3 5 17 T_3 1
#> 84 17 S_4 1 17 T_4 0
#> 85 17 S_5 9 17 T_5 0
#> 86 18 S_1 3 18 T_1 1
#> 87 18 S_2 3 18 T_2 1
#> 88 18 S_3 3 18 T_3 1
#> 89 18 S_4 3 18 T_4 1
#> 90 18 S_5 8 18 T_5 0
#> 91 19 S_1 6 19 T_1 1
#> 92 19 S_2 1 19 T_2 0
#> 93 19 S_3 4 19 T_3 1
#> 94 19 S_4 1 19 T_4 1
#> 95 19 S_5 4 19 T_5 1
#> 96 20 S_1 3 20 T_1 0
#> 97 20 S_2 10 20 T_2 1
#> 98 20 S_3 8 20 T_3 0
#> 99 20 S_4 4 20 T_4 0
#> 100 20 S_5 2 20 T_5 1
tab <- pivotr(df_score_long, cvars = c("score"), nvar = "score_value", fun = "sum") #%>%
#dtab(format="color_bar")
#tab
Created on 2020-09-25 by the reprex package (v0.3.0)