In this blog post, I tackle a question that you recurringly see on a lot of boards. We are going to calculate the cumulative sum, but within a group that the rows belong to.
Thanks to vegetableagony for pointing out that, depending on the size of the dataset, other conclusions can be drawn. That’s why edited this post and benchmarked several solutions on a small and large dataset.
First, let’s create the data and load the packages we’ll be using throughout this post.
library(dplyr) library(data.table) data('mtcars') dt <- data.table(mtcars) df <- copy(mtcars)
We will try to get the cumulative sum of the column hp, grouped by cyl and gear. (I know, it makes no sense)
First, let’s try to do it with only base functions. The underused function ave will return the cumulative sum by group in a single vector. So you’ll have to concatenate it to your data frame. The median time is 425 microseconds.
with( df[order(df$hp),], ave(hp, cyl, gear, FUN = cumsum) )
Next, we’ll use ddply from the plyr package. This will return the data frame with the cumulative sum by group as a new column of the data frame. When we test this, we can clearly see that this is ten times slower: 4500 microseconds.
ddply( df[order(df$hp),], .(cyl, gear), .fun = transform, cumulative_sum = (cumsum(hp)) )
Next up, dplyr. The following chain of functions will return a tibble with the cumulative sum by group as a new column. Not as fast as the first solution, but still a lot faster than ddply(): 830 microseconds.
df %>% arrange(hp) %>% group_by(hp) %>% mutate(cumulative_sum = cumsum(hp))
Finally, let’s go with data.table. I propose two solutions. The first one returns the cumulative sum by group and the columns it was grouped by. The second column adds the cumulative sum by group as a new column to the data frame. Both solutions are somewhat slow (2200 microseconds), which isn’t what we expect from data.table.
dt[order(hp)][,.(cumulative_sum = cumsum(hp)), by = .(cyl, gear)] dt[order(hp)][,cumulative_sum := cumsum(hp), by = .(cyl, gear)]
Let’s load the flights dataset.
library(nycflights13) dt <- data.table(flights) df <- copy(flights)
Let’s try the ave solution first. Given the size of the dataset, this is a lot slower. A median time of 1160 microseconds.
with( df[order(df$dep_delay),], ave(dep_delay, carrier, year, FUN = cumsum) )
So, let’s go with the ddply solution now. Compared to ave, it’s five to six stimes slower: 6170 microseconds. For small datasets, it was a factor 10, now it’s only factor 6, so that’s an improvement, I guess? 🙂
ddply( df[order(df$dep_delay),], .(carrier, year), .fun = transform, cumulative_sum = (cumsum(dep_delay)) )
Next up is dplyr. For the small dataset, it took double the time to generate the result. Now, it’s a little faster than the ave solution: 980 microseconds.
df %>% arrange(dep_delay) %>% group_by(carrier, year) %>% mutate(cumulative_sum = cumsum(dep_delay))
Finally, I’m going for data.table again. Surprisingly, it gets the job done in 600 microseconds. This is what we expect, given the advertised speed of data.table.
dt[order(dep_delay)][,.(cumulative_sum = cumsum(dep_delay)), by = .(carrier, year)] dt[order(dep_delay)][,cumulative_sum := cumsum(dep_delay), by = .(carrier, year)]
Conclusion, for small datasets, the base function ave is the fastest solution. As you can see, sometimes you have to kill your darlings. Yet for big datasets, nothing beats data.table.
By the way, if you’re having trouble understanding some of the code and concepts, I can highly recommend “An Introduction to Statistical Learning: with Applications in R”, which is the must-have data science bible. If you simply need an introduction into R, and less into the Data Science part, I can absolutely recommend this book by Richard Cotton. Hope it helps!
On Reddit I show how you can get substantially faster grouped cumulative sum times using data.table especially when using larger example datasets than the fairly small mtcars sample you use here.
Thanks, Rob. I’m going to update the blog post to elaborate on your findings!
Hi there. I mark you’ve noticed that a lot of unlike devices from been invented for cigarette smoking. Iqos vape No matter what they also came up with something interesting for cannabis smoking – it’s bong In my opinion story of the finest ways to fully encounter the quality of cannabis and its impact on you. If you are a beginner and a moment ago want to try smoking.
Most cbd products secure a unqualified impact on focal point and they do not straight soothe the longing and urgency as uncountable people force think. A scarcely any how to use cbd oil for erectile dysfunction
clothed a undeniably beneficial power on the wisdom in the works and since I smoke them every heyday I’ve noticed that my productivity has increased and my inclusive well-being has improved. I propose b assess if you’re willing to manipulate the unchanged result – you categorically bear to seek it.
So, I have cbd rat on and I have originate SEO for my website! I found the most excellently CBD SEO Agency ! They ranking in the CROWN in Google USA
CBD SEO Agency
Saved as a favorite, I really like your site!
Keеp tһis going please, geat job!
my blog post … cbd
Great site you’ve got here.. It’s difficult to find high quality writing
like yours these days. I honestly appreciate individuals like you!
Hello, i think that i saw you visited my website so i came to “return the favor”.I’m trying to find things to enhance my
site!I suppose its ok to use some of your ideas!!
Does your site have a contact page? I’m having a
tough time locating it but, I’d like to send you an email.
I’ve got some ideas for your blog you might be
interested in hearing. Either way, great website and I look forward to seeing it develop over time.
Hello! Quick question that’s completely off topic. Do you know how
to make your site mobile friendly? My weblog looks weird when viewing from my
iphone. I’m trying to find a template or plugin that might be able to
fix this issue. If you have any suggestions,
please share. Many thanks!
Hey there! Do you use Twitter? I’d like to follow you if that would be
ok. I’m definitely enjoying your blog and look forward to new updates.
You should be a part of a contest for one of the best
blogs online. I am going to highly recommend this site!
my blog: Webswite (themic.ir)
I haѵe been surfing online mⲟｒe than tһree houгs toɗay, yet I never fοund any intеresting article ⅼike
yⲟurs. It іs pretty worth ｅnough foг me.
Personally, iff aⅼl web owners and bloggers mɑde good сontent as
ｙou did, the internet will bе a lоt more usefuⅼ
thɑn eᴠeг beforе.
My web pɑge – Fammous Swimmers, http://Cityryde.Com/,
Hi everyone, it’s my first pay a visit at this website, and
article is really fruitful designed for me, keep up posting these types of
Everyone loves what you guys are usually up too.
This kind of clever work and exposure! Keep up the terrific works guys I’ve incorporated you
guys to my personal blogroll.
Thank you, I have just been searching for information approximately this
topic for ages and yours is the greatest I have came upon so far.
But, what in regards to the conclusion? Are you positive in regards to
Hi there, its good article concerning media print, we all know media is a wonderful source of data.
Hello everyone, it’s my first pay a quick visit at
this website, and piece of writing is really fruitful in favor of me, keep up posting such content.
It is not my first time to pay a quick visit this website, i am browsing this website dailly and get good data from
here every day.
Pretty great post. I just stumbled upon your blog and wished to mention that I have really enjoyed browsing your weblog posts.
In any case I’ll be subscribing on your rss feed
and I am hoping you write once more very soon!
This website was… how do you say it? Relevant!! Finally I’ve found something which helped
I was curious if you ever considered changing the layout of your
website? Its very well written; I love what youve got to say.
But maybe you could a little more in the way of
content so people could connect with it better. Youve got an awful lot of text for only having
one or 2 pictures. Maybe you could space it out
OMG! This is amazing. Ireally appreciate it~ May I tell
you my secret ways on a secret only I KNOW and if you want
to really findout? You really have to believe mme and have faith and I
will show how to change your life Once again I
want to show my appreciation and may all the blessing goes to you now!.
Actually no matter if someone doesn’t know afterward its up to other users
that they will assist, so here it happens.
of course like your web-site however you have to
check the spelling on quite a few of your posts. Many of
them are rife with spelling issues and I find it
very bothersome to inform the reality then again I’ll certainly come back again.
Hey there! Do you know if they make any plugins
to protect against hackers? I’m kinda paranoid about losing everything I’ve worked
hard on. Any recommendations?
This site really has all the information I needed concerning this subject and didn’t
know who to ask.
Hi there, I desire to subscribe for this blog to get most up-to-date updates, so where can i do
it please help out.
Hello my loved one! I want to say that this article is amazing, great written and
include approximately all vital infos. I would like
to look extra posts like this .
I take pleasure in, cause I found exactly what I was having a look
for. You have ended my 4 day long hunt! God Bless you man. Have a nice day.
I do accept as true with all of the ideas you’ve introduced in your post.
They are very convincing and can certainly work. Nonetheless, the posts are
very quick for starters. May you please prolong them a little from next time?
Thanks for the post.
Thank you for sharing your info. I really appreciate your
efforts and I will be waiting for your next write ups thank you once again.
Incredible points. Great arguments. Keep up the good spirit.
What is a better way to give a thumbs up to you?
Who else these days gives awesome commentary like this.
I really appreciate it man!
Howdy are using WordPress for your blog platform?
I’m new to the blog world but I’m trying to get started and set up my
own. Do you need any html coding knowledge to make
your own blog? Any help would be greatly appreciated!
The on a daily basis epitome of the great guy, it’s very difficult to not like Paul Rudd.
I am truly grateful to the owner of this website who has shared this great post at at
Hi, just wanted to mention, I liked this blog post.
It was funny. Keep on posting!
Just wish to say your article is as astonishing.
The clarity in your post is simply excellent and
i can assume you are an expert on this subject. Well with your permission allow me to grab your
RSS feed to keep up to date with forthcoming post. Thanks a
million and please continue the gratifying work.
Hello mates, how is the whole thing, and what you would like to say concerning this post, in my view its in fact remarkable designed for me.
It’s an remarkable post in support of all the internet people;
they will take benefit from it I am sure.
It’s an amazing article for all the online users; they will obtain benefit from it I am sure.
If you would like to grow your know-how simply keep visiting this website and be updated with the latest information posted
Hi there, its nice post on the topic of media print, we all be aware of media is a enormous source of facts.
Very good post. I will be going through some
of these issues as well..
Heya are using WordPress for your site platform?
I’m new to the blog world but I’m trying to get started
and create my own. Do you require any coding expertise to make
your own blog? Any help would be really appreciated!
I really like it when folks get together and share thoughts.
Great blog, keep it up!
Good way of describing, and nice paragraph to take information about my presentation topic, which i am going to deliver in institution of
We stumbled over here coming from a different page and thought I might
as well check things out. I like what I see so
i am just following you. Look forward to finding out about your web page yet again.
We stumbled over here coming from a different page and thought I
may as well check things out. I like what I see so now i’m following you.
Look forward to finding out about your web page repeatedly.
Elektrikçi Ankara | Ankara Elektrikçi arıyorsanız doğru yerdesiniz. Elektrikçi Ankara hiç çekinmeden tüm elektrik sorunlarını arayabilir bilgi alabilirsiniz. Elektrikci lazım olduğunda Ankara ‘nın bir çok mahallesine hizmet veren Elektrikçi ustalarımızdan profesyonel destek alabilirsiniz.
I believe people who wrote this needs true loving because it’s a blessing.
So let me give back and show my inside to change your life and if you want
to with no joke truthfully see I will share info about how to find hot girls for free
Don’t forget.. I am always here for yall. Bless yall!
I’d like to find out more? I’d want to find out more details.
Link exchange is nothing else except it is only placing the other person’s webpage link on your page at proper place
and other person will also do similar in favor of you.
Hackdra has revolutionized the web3 with its Free cyber security trainings, ethical hacking courses and artificial intelligence courses.
Hackdra’s smart contract auditing service ensures that decentralized applications and smart contracts are free of vulnerabilities and security loopholes. Request free smart contract audit now.
Pretty! This has been an incredibly wonderful post.
Many thanks for supplying these details.
Hi there to all, how is the whole thing, I think
every one is getting more from this website, and your views are
good in support of new people.
Hackdra has completed the audit of the famous giant exchange. Successfully completed bsc audit ensured decentralized applications and smart contracts were free of vulnerabilities and security loopholes.
I am sure this post has touched all the internet users, its really really pleasant piece of writing on building up new weblog.
Hmm it seems like your site ate my first comment (it was extremely long)
so I guess I’ll just sum it up what I submitted and say, I’m thoroughly enjoying
your blog. I too am an aspiring blog blogger but I’m
still new to the whole thing. Do you have any tips and hints for beginner blog writers?
I’d definitely appreciate it.
Inspiring quest there. What happened after? Good luck!
Here is my web blog: بک لینک انبوه
Outstanding story there. What occurred after?
Incredible points. Great arguments. Keep up the good effort.
498891 882137Thank you for having the time to discuss this topic. I truly appreciate it. Ill stick a link of this entry in my website. 261226
You can certainly see your expertise in the article you write.
The world hopes for even more passionate writers such
as you who aren’t afraid to mention how they believe.
All the time go after your heart.
I will right away take hold of your rss feed as I can’t in finding your e-mail subscription hyperlink or e-newsletter service.
Do you have any? Please allow me recognize so that I may subscribe.
When some one searches for his required thing, therefore he/she wishes
to be available that in detail, thus that thing is maintained over
Whoa! This blog looks exactly like my old one! It’s on a totally different topic but it has pretty much the same layout and design. Great choice of colors!