Calculate the proportion

[jasperjaks]

Hey there,

I´m pretty new to R studio and struggling with the following.

I have a dataset which looks as follows:

ID | Date | Value
1 | 2015-01-01 | 1000
2 | 2015-01-01 | 5000
3 | 2015-01-01 | 2500
1 | 2015-01-02 | 3000
2 | 2015-01-02 | 6000
3 | 2015-01-02 | 3000

I would like to add the following collumn which tells me the proportion of the companys (ID) value in comparison to the market (all three companys accumulated for the same date)

ID | date | Value | Market Value
1 | 2015-01-01 | 1000 | 1000/8500
2 | 2015-01-01 | 5000 | 5000/8500
3 | 2015-01-01 | 2500 | 2500/8500
1 | 2015-01-02 | 3000 | 3000/12000
2 | 2015-01-02 | 6000 | 6000/12000
3 | 2015-01-02 | 3000 | 3000/12000

I was able to calculate the market value for each day via the following:

market=aggregate(Value~date,sum,data=x)

But from here I don´t know how to proceed since the dataset market has only one line per date and the actual dataset has multiple lines per date for every company.

I appreciate any help!

[jasperjaks]

I think I figured it out. Thanks! Just x$marketvalue=x$value/market$value

[andresrcs]

If you are interested, tidyverse syntax is more human-readable.

library(dplyr)

# Sample data on a copy/paste friendly format
sample_df <- data.frame(
          ID = c(1, 2, 3, 1, 2, 3),
        Date = c("2015-01-01","2015-01-01","2015-01-01",
                 "2015-01-02","2015-01-02","2015-01-02"),
       Value = c(1000, 5000, 2500, 3000, 6000, 3000)
)

sample_df %>% 
    group_by(Date) %>% 
    mutate(market_value = Value / sum(Value))
#> # A tibble: 6 x 4
#> # Groups:   Date [2]
#>      ID Date       Value market_value
#>   <dbl> <chr>      <dbl>        <dbl>
#> 1     1 2015-01-01  1000        0.118
#> 2     2 2015-01-01  5000        0.588
#> 3     3 2015-01-01  2500        0.294
#> 4     1 2015-01-02  3000        0.25 
#> 5     2 2015-01-02  6000        0.5  
#> 6     3 2015-01-02  3000        0.25

^{Created on 2020-05-14 by the reprex package (v0.3.0)}

If you want to learn how to use this set of tools you can read this free book.

Welcome | R for Data Science