Using pandas to get cumulative sum of data by days

Question

Python

I've got weather data for 122 days from Wunderground that doesn't have equal time sampling intervals. Here's a sample of my data:

Bangor Weather Data from Wunderground
Datetime,Temp(F),Precip(in.),Snow (in.),PET(in./day),Baro(mBar)
    2015-12-02 01:30:00,1.1,0.3,0.0,0.45524647117649564,1017.5 
    2015-12-02 01:53:00,1.1,0.3,0.0,0.45524647117649564,1017.6 
    2015-12-02 02:20:00,1.1,0.3,0.0,0.45524647117649564,1017.2 
    2015-12-02 02:53:00,1.7,0.5,0.0,0.500024812603692,1016.7 
    2015-12-02 02:55:00,1.7,0.3,0.0,0.500024812603692,1016.5 
    2015-12-02 03:09:00,1.1,0.3,0.0,0.45524647117649564,1016.1 
    2015-12-02 03:33:00,1.1,0.5,0.0,0.45524647117649564,1016.1 
    2015-12-02 03:53:00,1.7,0.8,0.0,0.500024812603692,1016.1 
    2015-12-02 04:34:00,1.7,0.5,0.0,0.500024812603692,1015.1 
    2015-12-02 04:46:00,1.7,0.5,0.0,0.500024812603692,1015.1 
    2015-12-02 04:53:00,1.7,0.8,0.0,0.500024812603692,1015.1 
    2015-12-02 05:13:00,1.7,0.0,0.0,0.500024812603692,1014.4

I want to get the daily sum of snow (reset to zero for a new day) for my entire data set. I want my output would look like:

    2015-12-01,0.0
    2015-12-02,0.0
    2015-12-03,1.0
    2015-12-04,3.0
    2015-12-05,0.0
    2015-12-06,1.0

How can I use pandas to do this?

[Related question](https://stackoverflow.com/questions/14673394/python-pandas-extract-unique-dates-from-time-series) about extracting unique dates from time series. I suspect that you can just `groupby` the `Date`, then apply `cumsum`. — Paul, Apr 03 '16 at 18:04
Actually, this is a bit unclear - do you want the cumulative sum to "reset" to 0 with every new day, or do you want 1 data point for each day, and the value for that day corresponds to the total number of snow accumulation up to that point? I think showing an example output would help here. — Paul, Apr 03 '16 at 18:07
Yeah, sorry for the lack of clarity. I want the sum of snow for specific days. So yes, it would reset to zero when there was a new day. — Strak, Apr 03 '16 at 18:13
@Strak Where does `cumulative` enter the picture? Looks like you're interested in daily sums. — Sergey Bushmanov, Apr 03 '16 at 18:17
@Sergey Bushmanov, Cumulative sum per day... It's the same as a daily sum. Essentially you add the amount of snow per time interval on a given day, which is a cumulative sum on a specific day. — Strak, Apr 03 '16 at 18:22
@Strak I think you just want the sum per day (or daily totals). A cumulative sum is a specific thing, like the cumulative sum of [1, 2, 3, 4] is [1, 3, 6, 10] (it has the same number of elements as the original list, but each element is the sum of all the previous elements of the original list). — Paul, Apr 03 '16 at 18:31
@Strak Yeah, I was just telling you that "cumulative sum" has a specific meaning. I believe that MaxU's answer is correct. — Paul, Apr 03 '16 at 18:50

MaxU - stand with Ukraine · Answer 1 · 2016-04-03T18:07:49.543

2

Is that what you want?

df.groupby(df.Datetime.dt.date)['Snow (in.)'].sum()

This will give you amount of snow (sum) per day

edited Apr 03 '16 at 18:07

answered Apr 03 '16 at 18:03

MaxU - stand with Ukraine

205,989
36
386
419

2

I think presumably they want [`cumsum`](http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.cumsum.html), not `sum`. – Paul Apr 03 '16 at 18:04
@Paul, IMO `cumsum()` doesn't make much sense in terms of snow in this case – MaxU - stand with Ukraine Apr 03 '16 at 18:05
@SergeyBushmanov, let's wait for OP - i hope she/he will clarify that – MaxU - stand with Ukraine Apr 03 '16 at 18:11
@MaxU Well, I mean, often people talk about snow in terms of "how much fell in X storm" or whatever (and want to know how much had fallen at any given point). The fact that we interpret the question differently makes me think OP really needs to clarify. – Paul Apr 03 '16 at 18:12
@MaxU, sort of. But yes, I do want cumulative daily sum. I've edited the question to be more clear. – Strak Apr 03 '16 at 18:17
@Strak, please try to apply my answer to your DF - it should produce desired result set – MaxU - stand with Ukraine Apr 03 '16 at 18:19

score 0 · Answer 2 · answered Oct 08 '17 at 21:53

0

You can also use:

df['Snow (in.)'].resample('D').sum()

answered Oct 08 '17 at 21:53

pjw

2,133
3
27
44

Using pandas to get cumulative sum of data by days

2 Answers2