Summation of row based on different id and same date

nirbhay · November 24, 2020, 2:10am

I have big data set and i am trying to do some calculations. I want to produce some calculations based on the row sum based on the same date with different IDs and store their value.
ex for data set:-

gvkey(Id) date a b
0001 1/1/2000 15624000 10011531920
0001 2/1/2000 18480000 10592724720
0001 3/1/2000 16968000 10160611743
0002 1/1/2000 9375333997 10011531920
0002 2/1/2000 9943536057 10592724720
0002 3/1/2000 9470034340 10160611743
0003 1/1/2000 441665123.3 10011531920
0003 2/1/2000 412041243.1 10592724720
0003 3/1/2000 417427403.1 10160611743

I am trying to do this code but values are coming constantly same for me.

library(readxl)
library(tidyverse)
library(plyr)
df<-NewData
head(NewData)

a<-NewData %>%
  mutate(firmMarket=NewData$cshoc*NewData$prccd)
  
head(a)
c<-ddply(b,.(gvkey,datadate),summarize,TotalMarket=sum(firmMarket))

Kindly help will be appreciated. Thanks in advance.

williaml · November 24, 2020, 2:48am

Can you make your data reproducible next time?

FAQ: What's a reproducible example (`reprex`) and how do I create one? meta

Why reprex? Getting unstuck is hard. Your first step here is usually to create a reprex, or reproducible example. The goal of a reprex is to package your code, and information about your problem so that others can run it and feel your pain. Then, hopefully, folks can more easily provide a solution. What's in a Reproducible Example? Parts of a reproducible example: background information - Describe what you are trying to do. What have you already done? complete set up - include any library() calls and data to reproduce your issue. data for a reprex: Here's a discussion on setting up data for a reprex make it run - include the minimal code required to reproduce your error on the data…

Your data:

library(tidyverse)
NewData <- tibble::tribble(
  ~gvkey,        ~date,          ~a,          ~b,
            1L, "01/01/2000",    15624000, 10011531920,
            1L, "02/01/2000",    18480000, 10592724720,
            1L, "03/01/2000",    16968000, 10160611743,
            2L, "01/01/2000",  9375333997, 10011531920,
            2L, "02/01/2000",  9943536057, 10592724720,
            2L, "03/01/2000",  9470034340, 10160611743,
            3L, "01/01/2000", 441665123.3, 10011531920,
            3L, "02/01/2000", 412041243.1, 10592724720,
            3L, "03/01/2000", 417427403.1, 10160611743
  )

a<-NewData %>%
mutate(firmMarket=NewData$cshoc*NewData$prccd)

Which column is meant to be cschoc and which one is meant to be prccd? Are they from somewhere else?

nirbhay · November 24, 2020, 2:50am

yes, that is the calculation for frimMarket which is "a" in my mentioned data set. You can just ignore that code. Problem is in next code.

Thanks

williaml · November 24, 2020, 5:05am

So what exactly are you after? Just the sum of each day for a and b?

nirbhay · November 24, 2020, 5:31am

yes, sum of a for all dates and id's

williaml · November 24, 2020, 5:36am

What, like this? You should specify the example better in your question so that people can answer it.

library(tidyverse)
library(lubridate)
NewData <- tibble::tribble(
  ~gvkey,        ~date,          ~a,          ~b,
  1L, "01/01/2000",    15624000, 10011531920,
  1L, "02/01/2000",    18480000, 10592724720,
  1L, "03/01/2000",    16968000, 10160611743,
  2L, "01/01/2000",  9375333997, 10011531920,
  2L, "02/01/2000",  9943536057, 10592724720,
  2L, "03/01/2000",  9470034340, 10160611743,
  3L, "01/01/2000", 441665123.3, 10011531920,
  3L, "02/01/2000", 412041243.1, 10592724720,
  3L, "03/01/2000", 417427403.1, 10160611743
) %>% 
  mutate(date = dmy(date))

NewData %>% 
  group_by(date) %>% 
  summarise(a = sum(a), b = sum(b))

# A tibble: 3 x 3
  date                  a           b
  <chr>             <dbl>       <dbl>
1 01/01/2000  9832623120. 30034595760
2 02/01/2000 10374057300. 31778174160
3 03/01/2000  9904429743. 30481835229

nirbhay · November 24, 2020, 5:44am

NO, ok let me try to make it more clear, sorry for messy writing.
Now i want to have sum of "a" based on date and id and "b" should be output as shown in my data set. So in "a" i am trying to do sum of the first id
i.e,

~gvkey,        ~date,          ~a,          
            1L, "01/01/2000",    15624000,

and 2nd id of same date

~gvkey,        ~date,          ~a,        
                       2L, "01/01/2000",  9375333997,

and out will be stored in b as answer

~gvkey,        ~date,          ~a,          ~b,
            1L, "01/01/2000",    15624000, 10011531920,
             2L, "01/01/2000",  9375333997, 10011531920,

and so on for all dates and id's.

williaml · November 24, 2020, 6:17am

It would something like this, but you don't really have enough data for it to look useful because it isn't summarising anything. I have changed the format of the date field in the code above as well.

NewData %>% 
  group_by(gvkey, date) %>% 
  summarise(a = sum(a), b = sum(b)) %>% 
  arrange(date, gvkey)

# A tibble: 9 x 4
# Groups:   gvkey [3]
  gvkey date                 a           b
  <int> <date>           <dbl>       <dbl>
1     1 2000-01-01   15624000  10011531920
2     2 2000-01-01 9375333997  10011531920
3     3 2000-01-01  441665123. 10011531920
4     1 2000-01-02   18480000  10592724720
5     2 2000-01-02 9943536057  10592724720
6     3 2000-01-02  412041243. 10592724720
7     1 2000-01-03   16968000  10160611743
8     2 2000-01-03 9470034340  10160611743
9     3 2000-01-03  417427403. 10160611743

nirbhay · November 24, 2020, 7:35am

ok let me give me your real data:

gvkey datadate    cshoc prccd
  <dbl>    <dbl>    <dbl> <dbl>
1  7041 20000103 42000000  3.86
2  7041 20000104 42000000  4.28
3  7041 20000105 42000000  4   
4  7041 20000106 42000000  4.04
5  7041 20000111 42000000  4   
6  7041 20000112 42000000  3.96

now I want to add a new column with calculation as

b<-NewData%>% 
  mutate(firmMarket=NewData$cshoc*NewData$prccd)%>%
  group_by(gvkey,datadate)%>%
  mutate(TotalMarket=sum(firmMarket),na.rm=TRUE)%>%
  mutate(weightage=firmMarket/TotalMarket)%>%
  mutate(PriceWeightage=weightage*prccd)%>%
  group_by(gvkey,datadate)%>%
  mutate(MarketIndex=sum(PriceWeightage,na.rm = TRUE))

This is my output

 gvkey datadate    cshoc prccd firmMarket TotalMarket na.rm weightage PriceWeightage MarketIndex
  <dbl>    <dbl>    <dbl> <dbl>      <dbl>       <dbl> <lgl>     <dbl>          <dbl>       <dbl>
1  7041 20000103 42000000  3.86  162120000          NA TRUE         NA             NA           0
2  7041 20000104 42000000  4.28  179760000          NA TRUE         NA             NA           0
3  7041 20000105 42000000  4     168000000          NA TRUE         NA             NA           0
4  7041 20000106 42000000  4.04  169680000          NA TRUE         NA             NA           0
5  7041 20000111 42000000  4     168000000          NA TRUE         NA             NA           0
6  7041 20000112 42000000  3.96  166320000          NA TRUE         NA             NA           0

Please help me in getting values. calculation are as follows:

TotalMarket at t = Sum of firmMarket at t,I
Weightage at t,i = firmMarket /TotalMarket
PriceWightage = pprccd*Weightage

williaml · November 24, 2020, 8:22am

Again, please provide a reproducible example:

FAQ: What's a reproducible example (`reprex`) and how do I create one? meta

Why reprex? Getting unstuck is hard. Your first step here is usually to create a reprex, or reproducible example. The goal of a reprex is to package your code, and information about your problem so that others can run it and feel your pain. Then, hopefully, folks can more easily provide a solution. What's in a Reproducible Example? Parts of a reproducible example: background information - Describe what you are trying to do. What have you already done? complete set up - include any library() calls and data to reproduce your issue. data for a reprex: Here's a discussion on setting up data for a reprex make it run - include the minimal code required to reproduce your error on the data…

Use dput() or reprex()

nirgrahamuk · November 24, 2020, 10:04am

na.rm is a param for sum not for mutate.

nirbhay · November 24, 2020, 2:10pm

Thanks for your input.
Here is a new output

gvkey datadate    cshoc prccd firmMarket TotalMarket    weightage PriceWeightage MarketIndex
  <dbl>    <dbl>    <dbl> <dbl>      <dbl>       <dbl>        <dbl>          <dbl>       <dbl>
1  7041 20000103 42000000  3.86  162120000     4.33e15 0.0000000375    0.000000145        17.7
2  7041 20000104 42000000  4.28  179760000     4.33e15 0.0000000415    0.000000178        17.7
3  7041 20000105 42000000  4     168000000     4.33e15 0.0000000388    0.000000155        17.7
4  7041 20000106 42000000  4.04  169680000     4.33e15 0.0000000392    0.000000158        17.7
5  7041 20000111 42000000  4     168000000     4.33e15 0.0000000388    0.000000155        17.7
6  7041 20000112 42000000  3.96  166320000     4.33e15 0.0000000384    0.000000152        17.7

but now issue is, value for TotalMarket is same for all . That should not be same. Kindly help.

nirgrahamuk · November 24, 2020, 2:24pm

Did you omit grouping by ?

nirbhay · November 24, 2020, 2:27pm

No is used grouped by

b<-NewData%>% 
  mutate(firmMarket=NewData$cshoc*NewData$prccd)%>%
  group_by(gvkey,datadate)%>%
  mutate(TotalMarket=sum(firmMarket,na.rm=TRUE))%>%
  mutate(weightage=firmMarket/TotalMarket)%>%
  mutate(PriceWeightage=weightage*prccd)%>%
  group_by(gvkey,datadate)%>%
  mutate(MarketIndex=sum(PriceWeightage,na.rm = TRUE))

nirgrahamuk · November 24, 2020, 3:00pm

And does this work?

mpg %>%
select(model,hwy) %>%
 group_by(model) %>%
 mutate(sum_hwy_by_model = sum(hwy,na.rm=TRUE))

# A tibble: 234 x 3
# Groups:   model [38]
   model        hwy sum_hwy_by_model
   <chr>      <int>           <int>
 1 a4            29             198
 2 a4            29             198
 3 a4            31             198
 4 a4            30             198
 5 a4            26             198
 6 a4            26             198
 7 a4            27             198
 8 a4 quattro    26             206
 9 a4 quattro    25             206
10 a4 quattro    28             206
...

nirbhay · November 25, 2020, 2:59am

its giving same output.

nirgrahamuk · November 25, 2020, 8:18am

How about you give me some of NewData, and we see if grouped sums can be calculated.?

nirbhay · November 25, 2020, 1:12pm

Ok, NewData look like this with many more gvkeys, these are in the monthly data set, but have a daily data set. Along with that i have a very big data set I tried to do dout() it took a long process to compile and too much information to be displayed here.

gvkey	datadate	cshoc	prccd
7041	31/1/2000	42000000	3.72
7041	29/2/2000	42000000	4.4
7041	31/3/2000	42000000	4.04
7041	28/4/2000	42000000	3.44
7041	31/5/2000	42000000	3.24
15632	31/1/2000	2367508585	3.96
15632	29/2/2000	2367508585	4.2
15632	31/3/2000	2367508585	4
15632	28/4/2000	2367508585	3.96
15632	31/5/2000	2367508585	3.82
203244	31/1/2000	269308002	1.64
203244	29/2/2000	269308002	1.53
203244	31/3/2000	269308002	1.55
203244	28/4/2000	269308002	1.45
203244	29/5/2000	269308002	1.42
212452	31/1/2000	17095000	2.24
212452	29/2/2000	17107000	3.06
212452	31/3/2000	17245000	6
212452	28/4/2000	17356000	7.5
212452	31/5/2000	17515000	10.9
now based on this i need to create new column named "firmMarket", "TotalMarket", "weightage", "PriceWeigtage". So the major problem is in calculating "TotalMarket". Kindly refer to the image i am uploading in this sum is required based on time and id.

Kindly help in solving this problem. Thanks in advance.

nirgrahamuk · November 25, 2020, 1:48pm

Hello,
I'm sure you shared this image with the best intentions, but perhaps you didnt realise what it implies.
If someone wished to use example data to test code against, they would type it out from your screenshot...

This is very unlikely to happen, and so it reduces the likelihood you will receive the help you desire.
Therefore please see this guide on how to reprex data. Key to this is use of either datapasta, or dput() to share your data as code

FAQ: How to do a minimal reproducible example ( reprex ) for beginners Guides & FAQs

A minimal reproducible example consists of the following items: A minimal dataset, necessary to reproduce the issue The minimal runnable code necessary to reproduce the issue, which can be run on the given dataset, and including the necessary information on the used packages. Let's quickly go over each one of these with examples: Minimal Dataset (Sample Data) You need to provide a data frame that is small enough to be (reasonably) pasted on a post, but big enough to reproduce your issue. Let's say, as an example, that you are working with the iris data frame head(iris) #> Sepal.Length Sepal.Width Petal.Length Petal.Width Species #> 1 5.1 3.5 1.4 0.…

nirbhay · November 26, 2020, 3:23am

gvkey datadate cshoc prccd

7041 31/1/2000 42000000 3.72

7041 29/2/2000 42000000 4.4

7041 31/3/2000 42000000 4.04

7041 28/4/2000 42000000 3.44

7041 31/5/2000 42000000 3.24

15632 31/1/2000 2367508585 3.96

15632 29/2/2000 2367508585 4.2

15632 31/3/2000 2367508585 4

15632 28/4/2000 2367508585 3.96

15632 31/5/2000 2367508585 3.82

203244 31/1/2000 269308002 1.64

203244 29/2/2000 269308002 1.53

203244 31/3/2000 269308002 1.55

203244 28/4/2000 269308002 1.45

203244 29/5/2000 269308002 1.42

212452 31/1/2000 17095000 2.24

212452 29/2/2000 17107000 3.06

212452 31/3/2000 17245000 6

212452 28/4/2000 17356000 7.5

212452 31/5/2000 17515000 10.9

Sir, this is the data set. Image i just used to show how the sum is done in TotalMarket. You can use this data it is not in image formate.
Thanks