Split all lines by reference string into groups
Here's an example table I'm working with:
n = c(rep("A",3),rep("B",3),rep("C",3))
m = c("X", "Y", "Z", "X", "Y", "Z", "X", "Y", "Z")
s = 1:9
b = 5:13
c = 20:28
d = c(rep("abc", 9))
df = data.frame(d, n, m, s, b, c)
df
Below is the table:
d n m s b c
abc A X 1 5 20
abc A Y 2 6 21
abc A Z 3 7 22
abc B X 4 8 23
abc B Y 5 9 24
abc B Z 6 10 25
abc C X 7 11 26
abc C Y 8 12 27
abc C Z 9 13 28
I will refer to each row as a concatenation of its column n and m values (e.g. AX rows, CZ rows, etc.). I would like to split each of the A lines by the AY line, each of the B lines by the BY line, and each of the C lines by the CY line (may not always be Y, sometimes X or Z). I essentially want to re-arrange the data (columns s, b and c) by group (where column n is the group) using X, Y, or Z (column m) as the base.
I need columns d, n and m to stay intact. If possible, I would like to do this by specifying X, Y, or Z in the code directly to indicate which string will be the base, not [1], [2] or [3] (since they may not always be in the same order, and it's more intuitive for the user). I'm new to R and using dplyr, but I couldn't find a good way to do this.
Thank you for your help.
source to share
Use data.table
.
library(data.table)
setDT(df)
divselect <- "Y"
set(df, j = "s", value = as.numeric(df[["s"]]))
set(df, j = "b", value = as.numeric(df[["b"]]))
set(df, j = "c", value = as.numeric(df[["c"]]))
Teams set
must avoid mistakes. Currently there are columns integer
, but you are going to make them double
. If in your example in the real world they are already there double
, it won't be necessary.
The value divselect
changes the rows of the columns you are using as a base. You can change this to X
or Z
as needed.
df[, `:=`(s = s/s[m == divselect],
b = b/b[m == divselect],
c = c/c[m == divselect]),
by = n]
Result:
# d n m s b c
# 1: abc A X 0.500 0.8333333 0.9523810
# 2: abc A Y 1.000 1.0000000 1.0000000
# 3: abc A Z 1.500 1.1666667 1.0476190
# 4: abc B X 0.800 0.8888889 0.9583333
# 5: abc B Y 1.000 1.0000000 1.0000000
# 6: abc B Z 1.200 1.1111111 1.0416667
# 7: abc C X 0.875 0.9166667 0.9629630
# 8: abc C Y 1.000 1.0000000 1.0000000
# 9: abc C Z 1.125 1.0833333 1.0370370
Followup
I have one question: is there a way to generalize the columns that get rebased? I would like this code to be able to handle additional numeric columns (more than 3 without calling each one). those. can I determine that division will happen on all columns except d, n and m?
Yes, you can do it using lapply
internally or externally data.table
.
setDT(df)
divselect <- "Y"
funcnumeric <- function(x) {
set(df, j = x, value = as.numeric(df[[x]]))
NULL
}
modcols <- names(df)[!(names(df) %in% c("d", "n", "m"))]
a <- lapply(modcols, funcnumeric)
This replaces the three commands set
in the first answer. Instead of specifying each one, we use lapply
to execute a function for each column that is not d
, n
or m
. Note that I am returning NULL to avoid messing around with the returned text; since this data.table
is all done on the spot.
funcdiv <- function(x, pos) {
x/x[pos]
}
df[ , (modcols) := lapply(.SD,
funcdiv,
pos = which(m == divselect)),
by = n,
.SDcols = modcols]
This is done a little differently than before. Here we create a simple function that will divide a vector by this vector value a by the position specified by the parameter pos
. We apply this to each column in .SD
and also pass the value pos
as the position where the column m
is equal to the value divselect
, in which case it is Y
. Since we specify by = n
, for each value in, n
both vector and pos
arguments will be defined funcdiv
. The parameter .SDcols
indicates that we want lapply
this function, which is the same set of columns that we assigned to the variable modcols
. We'll put it all back on modcols
.
Result:
# d n m s b c
# 1: abc A X 0.500 0.8333333 0.9523810
# 2: abc A Y 1.000 1.0000000 1.0000000
# 3: abc A Z 1.500 1.1666667 1.0476190
# 4: abc B X 0.800 0.8888889 0.9583333
# 5: abc B Y 1.000 1.0000000 1.0000000
# 6: abc B Z 1.200 1.1111111 1.0416667
# 7: abc C X 0.875 0.9166667 0.9629630
# 8: abc C Y 1.000 1.0000000 1.0000000
# 9: abc C Z 1.125 1.0833333 1.0370370
source to share
Using dplyr
df2 <- filter(df, m=="Y") %>% setNames(.,c("e","n","f","g","h","i"))
df1 <- full_join(df,df2,by="n") %>%
mutate(s=s/g, b=b/h, c=c/i) %>%
select(-c(e,f,g,h,i))
Output
d n m s b c
1 abc A X 0.500 0.8333333 0.9523810
2 abc A Y 1.000 1.0000000 1.0000000
3 abc A Z 1.500 1.1666667 1.0476190
4 abc B X 0.800 0.8888889 0.9583333
5 abc B Y 1.000 1.0000000 1.0000000
6 abc B Z 1.200 1.1111111 1.0416667
7 abc C X 0.875 0.9166667 0.9629630
8 abc C Y 1.000 1.0000000 1.0000000
9 abc C Z 1.125 1.0833333 1.0370370
source to share
Using your data, we can build a table of rows to split and then select the row of the table using match
table = df[which(df$m == "Y"), c(2,4:6)]
New_df = df
New_df[, 4:6] = New_df[,4:6]/table[match(df$n, table$n), 2:4]
New_df
d n m s b c
1 abc A X 0.500 0.8333333 0.9523810
2 abc A Y 1.000 1.0000000 1.0000000
3 abc A Z 1.500 1.1666667 1.0476190
4 abc B X 0.800 0.8888889 0.9583333
5 abc B Y 1.000 1.0000000 1.0000000
6 abc B Z 1.200 1.1111111 1.0416667
7 abc C X 0.875 0.9166667 0.9629630
8 abc C Y 1.000 1.0000000 1.0000000
9 abc C Z 1.125 1.0833333 1.0370370
source to share