Return and calculate values based on selected criteria

Question

I've been searching around but still didn't find the answer. I'd like to see and calculate certain results based on my criteria. if and match functions wouldn't get what I look for. The original dataset looks like this:

Type  Name  Value
A     DK    3
A     MO    2
A     OM    5
A     LSO   3
B     GOP   6
B     ADG   5
C     BFC   7
D     TMD   6

What I want to get is if Type =A, then list the names and calculate the weight based on value, which should look like below:

DK    0.081
MO    0.054
OM    0.135
LSO   0.081

Any suggestion is appreciated. Thank you so much!

Please always add R code that creates the data.frame to help answering your question easier :-) — R Yoda, May 05 '16 at 21:59
"Weight based on value" means the weight of the value within the group or all groups (which the small values indicate)? — R Yoda, May 05 '16 at 22:00
Sorry, I was importing it from a csv file. Weight based on value means the weight of within all groups. rawr's answer (`prop.table(Value)[Type == 'A']`) solved the second part of my question. — T-T, May 05 '16 at 22:05
Possible duplicate of [Summary of proportions by group](http://stackoverflow.com/questions/37057784/summary-of-proportions-by-group) — alistaire, May 05 '16 at 22:07

Gaurav Taneja · Accepted Answer · 2016-05-06T21:32:52.693

3

require(dplyr)


d<-data.frame(Type=c("A","A","A","A","B","B","C","D"),
              Name = c("DK","MO","OM","LSO","GOP","ADG","BFC","TMD "),
              Value=c(3,2,5,3,6,5,7,6)
             )

dd<- d %>% 
      mutate(den=sum(Value)) %>% 
      filter(Type=="A") %>% 
      group_by(Name) %>% 
      summarise(Y=Value/min(den))
dd

edited May 06 '16 at 21:32

answered May 05 '16 at 22:02

Gaurav Taneja

1,084
1
8
19

Thank you Gaurav, but I got this `Error: could not find function "%>%"` How to fix that? – T-T May 05 '16 at 22:11
Do you have dplyr package installed? Try running `install.packages("dplyr")` . If it is installed you should not get the error. – Gaurav Taneja May 05 '16 at 22:12
Forgot to load the package. Thank you again! – T-T May 05 '16 at 22:16
Hi Gaurav, when I apply the same code to my own data set, I got `Error: expecting a single value`. I don't get any error message if I leave out the `summarize()` part. Any idea what caused the issue? – T-T May 06 '16 at 20:56
@T-T , my bad , use `summarise()` instead of `summarize()` – Gaurav Taneja May 06 '16 at 21:32
Seems like they are the same. I tried both and still got the same error message. I did some readings and found that summarise() collapses a data frame to a single row. I think that caused the issue. But how come it works in the example but not in the data set such as mtcars. The `str()` of my data set is also data.frame though. – T-T May 06 '16 at 21:45
@T-T I tested and in works on mtcars. Although, mtcars only has numeric variables and `row.name` as the type of car. – Gaurav Taneja May 06 '16 at 21:51
thank you so much for taking time out to look into it. Maybe there's an issue with my data set that I'm not aware of. What I came up with was adding another column `dd<- d %>% mutate(den=Value/sum(Value)) %>% filter(Type=="A") %>% group_by(Name, den) %>% summarise()` – T-T May 06 '16 at 22:08

R Yoda · Answer 2 · 2016-05-05T22:20:18.953

If performance matters and you have a lot of data use a data.table from the package data.table:

dt <- data.table( type=c("A", "A", "A", "A", "B", "B", "C", "D")
                  , name=c("DK", "MO", "OM", "LSO", "GOP", "ADG", "BFC", "TMD")
                  , value=c(3, 2, 5, 3, 6, 5, 7, 6))

value.total <- sum(dt$value)

dt[type=="A", .(name, weight=value/value.total)]

This will result in:

   name     weight
1:   DK 0.08108108
2:   MO 0.05405405
3:   OM 0.13513514
4:  LSO 0.08108108

If you want to calculate the weight ("proportion") for each row (not only "A") use:

dt[, .(name, weight=value/value.total)]

Which results in:

   name     weight
1:   DK 0.08108108
2:   MO 0.05405405
3:   OM 0.13513514
4:  LSO 0.08108108
5:  GOP 0.16216216
6:  ADG 0.13513514
7:  BFC 0.18918919
8:  TMD 0.16216216

score 1 · Answer 3 · edited May 06 '16 at 01:09

Type <- c('A','A','A','A','B','B','C','D')
Name <- c('DK', 'MO', 'OM', 'LSO', 'GOP', 'ADG','BFC','TMD')
Value <- c(3,2,5,3,6,5,7,6)
Dat <- data.frame(Type, Name,Value)
Dat
     Type Name Value
1    A   DK     3
2    A   MO     2
3    A   OM     5
4    A  LSO     3
5    B  GOP     6
6    B  ADG     5
7    C  BFC     7
8    D  TMD     6

new.dat <- data.frame(Name, Weight=(Value/sum(Value)))
new.dat
    Name     Weight
1   DK       0.08108108
2   MO       0.05405405
3   OM       0.13513514
4   LSO      0.08108108
5   GOP      0.16216216
6   ADG      0.13513514
7   BFC      0.18918919
8   TMD      0.16216216

Return and calculate values based on selected criteria

3 Answers3