Integrating over x values of type pandas.Datetime

Question

I am trying to do some integration with np.trapz of some data using DatetimeIndex as x values, but the results are not what I was expecting. Could somebody maybe help me out?

The problem:

I got a pandas dataframe which has a timestamps as indexes and a single column with Irradiance data called irradiance. It looks like the following:

>>> data
Timestamp                Irradiance
2017-04-29 00:01:49     -1.16143
2017-04-29 00:06:49     -1.54857
2017-04-29 00:11:49     -2.03248
2017-04-29 00:16:49     -1.69373
2017-04-29 00:21:49     -1.35498
...     ...
2017-04-29 23:38:49     -1.93543
2017-04-29 23:43:49     -2.03222
2017-04-29 23:48:49     -2.85481
2017-04-29 23:53:49     -2.41937
2017-04-29 23:58:49     -2.17745
1921 rows × 1 columns

>>>data.index         
<class 'pandas.core.indexes.datetimes.DatetimeIndex'>

>>>data["Irradiance"] 
<class 'pandas.core.series.Series'>

Now I like to integrate the data to obtain the total Irradiance during this day. np.trapz(data["Irradiance"]) should do the trick if the timestamps were evenly spaced: they are not (during daytime timestamps are spaced only 30 seconds, in contrast to the 5 min during nighttime).

So I add the DatetimeIndex as x values to np.trapz to account for the unevenly spaced timestamps: np.trapz(data["Irradiance"], x = data.index)

The output is now:

>>>np.trapz(data["Irradiance"], x = data.index) 
numpy.timedelta64(17899433815886749,'ns')

Why is this integration giving my this nanosecond output? It doesn't make sense to me. I expected it to give some value, but definitely not this.

for clarity: This is what the actual data looks like when plotted

Answer 1

The np.trapz function can't take a DatetimeIndex as input. However, you can calculate the samples points yourself. The first timestamp would correspond to 0, the next one 300 (5 minutes during the night) and so on. This can be done as follows:

sample_points = df.reset_index()['Timestamp'].diff().dt.total_seconds().fillna(0).cumsum().values

Then, compute the integral:

np.trapz(data["Irradiance"], x=sample_points)