CODEWITHSUNDEEP
r
Program
Program

Reputation: 1

transform data frame to another form

I'm new in [r]. And recently i'm stuck in how to perform operation in data.frame. Now I have a data.frame called frame. And I want to transform it to another form. 

> frame
         A      B  Freq total
1        0      0   75   110
2        1      0   21   110
3        0      1    8   110
4        1      1    6   110

the expected form is:

> frame(B=1)
         A    Freq total
1        0      8   83
2        1      6   27

Can anyone give some suggestions? Thanks

Upvotes: 0

Views: 87

Answers (2)

akrun
akrun

Reputation: 886938

One option would be using dplyr. We group by 'A', and create a new column 'total' as the sum of "Freq", filter the rows where 'B' = 1, and select all other columns except 'B'

library(dplyr)
frame %>% 
     group_by(A) %>% 
     mutate(total= sum(Freq)) %>%
     filter(B==1)%>%
     select(-B)
#  A Freq total
#1 0    8    83
#2 1    6    27

Or using data.table, we convert the data.frame to data.table (setDT(frame) or we can do as.data.table(frame)), create a new column total as the sum of 'Freq', grouped by 'A', subset the rows with B=1, and remove the 'B' column by assigning it to NULL. 

library(data.table)
setDT(frame)[, total:= sum(Freq), A][B==1][,B:=NULL]
#   A Freq total
#1: 0    8    83
#2: 1    6    27

Or using base R, we create the 'total' using transform/ave and then subset the rows that are 1 for 'B'.

subset(transform(frame, total=ave(Freq, A, FUN=sum)), B==1, select=-B)
#   A Freq total
#3 0    8    83
#4 1    6    27

Upvotes: 2

Jaehyeon Kim
Jaehyeon Kim

Reputation: 1417

Below is an example using functions in the base package - aggregate() and merge().

frame <- read.table(header = T, text = "
A      B  Freq total
1        0      0   75   110
2        1      0   21   110
3        0      1    8   110
4        1      1    6   110")

# obtain sum by column A
frame1 <- aggregate(frame$Freq, by = list(frame$A), sum)
names(frame1) <- c("A", "total")

# merge Freq
frame2 <- merge(frame1, frame[frame$B == 1, c(1,3)], by="A")
#  A total Freq
#1 0    83    8
#2 1    27    6

Upvotes: 0

Related Questions