How to calculate the sum of each day with pandas

Question

I want to summarise each day of my table. The values in the original table are minutes, but these are way to exactly I only need the value of 24 hours. The original table looks like this three columns and x rows:

Datum   Zeit    Rad
01.01.2017  00:01:00    0
01.01.2017  00:02:00    0
01.01.2017  00:03:00    0
01.01.2017  00:04:00    0
01.01.2017  00:05:00    0
01.01.2017  00:06:00    0
01.01.2017  00:07:00    0
01.01.2017  00:08:00    0
01.01.2017  00:09:00    0
01.01.2017  00:10:00    0

I changed the datetime with pandas to 2017-01-01 so that the table looks like: 2017-01-01 00:01:00 0

Is it even possible to count every Bike(Rad) on every day.

My idea is, sum all Bike(Rad) till the datetime changes to 02.01.2017 and then to 03.01.2017.

import pandas as pd
import numpy as np

Tab = pd.read_csv('Komplett.csv', delimiter=';')

print(Tab.head())

#Tab.Rad = Tab.Rad.astype(int)
#Tab.Rad.astype(int)

#print(Tab.Rad.dtypes)

#print(Tab.Rad)

#print(Tab.Rad[0:1440])
#print(Tab[0:1439])

#print(len(Tab))

#sum = np.sum(Tab.Rad[0:1439])
#Date = Tab.Datum[1438]
#print(Date, sum)


Tab['Datum']  = pd.to_datetime(Tab.Datum)
print(Tab.head())


#Day=Tab[Tab.Datum=='2017-01-01']
#print(np.sum(Day.Rad))

New output:

        Datum      Zeit  Rad
0  01.01.2017  00:01:00    0
1  01.01.2017  00:02:00    0
2  01.01.2017  00:03:00    0
3  01.01.2017  00:04:00    0
4  01.01.2017  00:05:00    0
       Datum      Zeit  Rad
0 2017-01-01  00:01:00    0
1 2017-01-01  00:02:00    0
2 2017-01-01  00:03:00    0
3 2017-01-01  00:04:00    0
4 2017-01-01  00:05:00    0

I guess I am little confused as to what you are asking but does `df.groupby('Datum').size()` get close to what you are looking for — It_is_Chris, Dec 22 '18 at 16:23
Hey Chris,thank you for your fast response. I change the size() to sum(), and it worked, but pandas got confused by the date. Its switch the 02.Januar (01.02) with the 01.February (02.01), and so on. Independently if i use the orginial date or the pd.to_datetime function. Do you have any ideas to avoid it ? — Kai_hismelf, Dec 22 '18 at 16:53
look at the [pd.to_datetime docs](https://pandas.pydata.org/pandas-docs/stable/generated/pandas.to_datetime.html) you can specify the format of you date column so it will parse correctly. — It_is_Chris, Dec 22 '18 at 17:06

How to calculate the sum of each day with pandas

0 Answers0