All geek questions in one place

Rotate rows into columns with sample values ​​for each dimension R

I have a sample of data that I work with

ID <- c("A","A","A","A","A","A","A","A","A","A","A","A","B","B","B","B","B","B","B","B","B","B")
TARG_AVG <- c(2.1,2.1,2.1,2.1,2.1,2.1,2.3,2.3,2.5,2.5,2.5,2.5,3.1,3.1,3.1,3.1,3.3,3.3,3.3,3.3,3.5,3.5)
Measurement <- c("Len","Len","Len","Wid","Ht","Ht","Dep","Brt","Ht","Ht","Dep","Dep"
                 ,"Dep","Dep","Len","Len","Ht","Ht","Brt","Brt","Wid","Wid")
df1 <- data.frame(ID,TARG_AVG,Measurement)

I am trying to solve 3 different problems here.

1) I want to get a summary of the number of unique dimensions for grouping (ID and TARG_AVG). I'm doing it right now

unique <- summaryBy(Measurement~ID+TARG_AVG, data=df1, FUN=function(x) { c(Count=length(x)) } )

This gives me a general (measure.count), but I want the counts for each measurement. My desired result is

  ID TARG_AVG Len Wid Ht Dep Brt Measurement.Count
1  A      2.1   3   1  2   0   0                 6
2  A      2.3   0   0  0   1   1                 2
3  A      2.5   0   0  2   2   0                 4
4  B      3.1   2   0  0   2   0                 4
5  B      3.3   0   0  2   0   2                 4
6  B      3.5   0   2  0   0   0                 2

2) As soon as I get the above output, I would like to multiply the lines so that I get a filtered output that returns rows that have at least 2 dimensions > 2 . Here my desired result will be

  ID TARG_AVG Len Wid Ht Dep Brt Measurement.Count
1  A      2.1   3   1  2   0   0                 6
3  A      2.5   0   0  2   2   0                 4
4  B      3.1   2   0  0   2   0                 4
5  B      3.3   0   0  2   0   2                 4

3) , > 2. 

      ID TARG_AVG Measurement
    1  A      2.1   Len   
    2  A      2.1   Len   
    3  A      2.1   Len   
    4  A      2.1   Ht   
    5  A      2.1   Ht   
    6  A      2.5   Ht   
    7  A      2.5   Ht   
    8  A      2.5   Dep  
    9  A      2.5   Dep  
   10  B      3.1   Len  
   11  B      3.1   Len  
   12  B      3.1   Dep 
   13  B      3.1   Dep
   14  B      3.3   Ht 
   15  B      3.3   Ht 
   16  B      3.3   Brt 
   17  B      3.3   Brt

reshape2, dplyr data.table , - , .

+4
3

library(data.table) #v 1.9.6+
setDT(df1)[, indx := .N, by = names(df1)
           ][indx > 1, if(uniqueN(Measurement) > 1) .SD, by = .(ID, TARG_AVG)]
#     ID TARG_AVG Measurement indx
#  1:  A      2.1         Len    3
#  2:  A      2.1         Len    3
#  3:  A      2.1         Len    3
#  4:  A      2.1          Ht    2
#  5:  A      2.1          Ht    2
#  6:  A      2.5          Ht    2
#  7:  A      2.5          Ht    2
#  8:  A      2.5         Dep    2
#  9:  A      2.5         Dep    2
# 10:  B      3.1         Dep    2
# 11:  B      3.1         Dep    2
# 12:  B      3.1         Len    2
# 13:  B      3.1         Len    2
# 14:  B      3.3          Ht    2
# 15:  B      3.3          Ht    2
# 16:  B      3.3         Brt    2
# 17:  B      3.3         Brt    2

dplyr

df1 %>%
  group_by(ID, TARG_AVG, Measurement) %>%
  filter(n() > 1) %>%
  group_by(ID, TARG_AVG) %>%
  filter(n_distinct(Measurement) > 1)


library(data.table)
## dcast the data (no need in total)
res <- dcast(df1, ID + TARG_AVG  ~ Measurement)
## filter by at least 2 incidents of at least length 2
res <- res[rowSums(res[-(1:2)] > 1) > 1,]
## melt the data back and filter again by at least 2 incidents
res <- melt(setDT(res), id = 1:2)[value > 1]
## Expand the data back
res[, .SD[rep(.I, value)]]

reshape2

1-

library(reshape2)
res <- dcast(df1, ID + TARG_AVG  ~ Measurement, margins = "Measurement")


res <- res[res$"(all)" > 2,]

3D-

library(data.table)
setDT(df1)[, if(.N > 2) .SD, by = .(ID, TARG_AVG)]
+5

tidyr. dplyr:

df2 <- df1 %>%
  group_by(ID, TARG_AVG) %>% # Group by ID and TARG_AVG
  mutate(count=n()) %>%      # Count how many are there for each combination of ID and TARG_AVG
  filter(count > 2) %>%      # Only keep the ones with more than 2 (I think you meant > 2)
  select(-count)             # Remove the auxiliary variable count
df2

( ) :

df2 <- df1 %>%
  group_by(ID, TARG_AVG) %>%
  filter(n() > 2)
df2

n() count.

EDIT: dplyr tidyr, :

ID <- c("A","A","A","A","A","A","A","A","A","A","A","A","B","B","B","B","B","B","B","B","B","B")
TARG_AVG <- c(2.1,2.1,2.1,2.1,2.1,2.1,2.3,2.3,2.5,2.5,2.5,2.5,3.1,3.1,3.1,3.1,3.3,3.3,3.3,3.3,3.5,3.5)
Measurement <- c("Len","Len","Len","Wid","Ht","Ht","Dep","Brt","Ht","Ht","Dep","Dep"
                 ,"Dep","Dep","Len","Len","Ht","Ht","Brt","Brt","Wid","Wid")
df0 <- data.frame(ID,TARG_AVG,Measurement)

1 2. , ,

df1 <- df0 %>%
  group_by(ID, TARG_AVG, Measurement) %>%
  summarise(count=n()) %>%
  group_by(ID, TARG_AVG) %>% # Step "2"
  filter(n() >= 2) %>%       # Step "2"
  spread(Measurement, count, fill = 0) %>% # Resume step "1"
  mutate(Measurement.count = Len + Wid + Ht + Dep + Brt)
df1

3.

df3 <- df2 %>%
  select(-Measurement.count) %>%
  gather(Measurement, dummy, Brt:Wid) %>%
  select(-dummy)
df3
+1

data.table, . , j : [1] , ( ), [2] ():

> cTbl[, N := .N, .(ID, TARG_AVG, Measurement)
      ][N > 1, NMgt1 := uniqueN(Measurement) > 1, .(ID, TARG_AVG)
      ][N > 1 & NMgt1
      ][, c('N', 'NMgt1') := NULL
      ][]



    ID TARG_AVG Measurement
 1:  A      2.1         Len
 2:  A      2.1         Len
 3:  A      2.1         Len
 4:  A      2.1          Ht
 5:  A      2.1          Ht
 6:  A      2.5          Ht
 7:  A      2.5          Ht
 8:  A      2.5         Dep
 9:  A      2.5         Dep
10:  B      3.1         Dep
11:  B      3.1         Dep
12:  B      3.1         Len
13:  B      3.1         Len
14:  B      3.3          Ht
15:  B      3.3          Ht
16:  B      3.3         Brt
17:  B      3.3         Brt
>
+1

Source: https://habr.com/ru/post/1610659/

More articles:


All Articles