Python int too large to convert to C long - plotting pandas dates

Question

I have merged two datasets by finding the nearest timestamp following a method shown in the accepted answer of this post:

pandas.merge: match the nearest time stamp >= the series of timestamps

However when I try plot the results I run into the error:

`<matplotlib.collections.LineCollection at 0x2975a3547b8>Traceback (most recent call last):

File "C:\ProgramData\Anaconda3\lib\site-packages\IPython\core\formatters.py", line 332, in __call__
return printer(obj)

File "C:\ProgramData\Anaconda3\lib\site-packages\IPython\core\pylabtools.py", line 237, in <lambda>
png_formatter.for_type(Figure, lambda fig: print_figure(fig, 'png', **kwargs))

File "C:\ProgramData\Anaconda3\lib\site-packages\IPython\core\pylabtools.py", line 121, in print_figure
fig.canvas.print_figure(bytes_io, **kw)

File "C:\ProgramData\Anaconda3\lib\site-packages\matplotlib\backend_bases.py", line 2208, in print_figure
**kwargs)

File "C:\ProgramData\Anaconda3\lib\site-packages\matplotlib\backends\backend_agg.py", line 507, in print_png
FigureCanvasAgg.draw(self)

File "C:\ProgramData\Anaconda3\lib\site-packages\matplotlib\backends\backend_agg.py", line 430, in draw
self.figure.draw(self.renderer)

File "C:\ProgramData\Anaconda3\lib\site-packages\matplotlib\artist.py", line 55, in draw_wrapper
return draw(artist, renderer, *args, **kwargs)

File "C:\ProgramData\Anaconda3\lib\site-packages\matplotlib\figure.py", line 1295, in draw
renderer, self, artists, self.suppressComposite)

File "C:\ProgramData\Anaconda3\lib\site-packages\matplotlib\image.py", line 138, in _draw_list_compositing_images
a.draw(renderer)

File "C:\ProgramData\Anaconda3\lib\site-packages\matplotlib\artist.py", line 55, in draw_wrapper
return draw(artist, renderer, *args, **kwargs)

File "C:\ProgramData\Anaconda3\lib\site-packages\matplotlib\axes\_base.py", line 2399, in draw
mimage._draw_list_compositing_images(renderer, self, artists)

File "C:\ProgramData\Anaconda3\lib\site-packages\matplotlib\image.py", line 138, in _draw_list_compositing_images
a.draw(renderer)

File "C:\ProgramData\Anaconda3\lib\site-packages\matplotlib\artist.py", line 55, in draw_wrapper
return draw(artist, renderer, *args, **kwargs)

File "C:\ProgramData\Anaconda3\lib\site-packages\matplotlib\axis.py", line 1133, in draw
ticks_to_draw = self._update_ticks(renderer)

File "C:\ProgramData\Anaconda3\lib\site-packages\matplotlib\axis.py", line 974, in _update_ticks
tick_tups = list(self.iter_ticks())

File "C:\ProgramData\Anaconda3\lib\site-packages\matplotlib\axis.py", line 917, in iter_ticks
majorLocs = self.major.locator()

File "C:\ProgramData\Anaconda3\lib\site-packages\matplotlib\dates.py", line 1054, in __call__
self.refresh()

File "C:\ProgramData\Anaconda3\lib\site-packages\matplotlib\dates.py", line 1074, in refresh
dmin, dmax = self.viewlim_to_dt()

File "C:\ProgramData\Anaconda3\lib\site-packages\matplotlib\dates.py", line 832, in viewlim_to_dt
return num2date(vmin, self.tz), num2date(vmax, self.tz)

File "C:\ProgramData\Anaconda3\lib\site-packages\matplotlib\dates.py", line 441, in num2date
return _from_ordinalf(x, tz)

File "C:\ProgramData\Anaconda3\lib\site-packages\matplotlib\dates.py", line 256, in _from_ordinalf
dt = datetime.datetime.fromordinal(ix).replace(tzinfo=UTC)

OverflowError: Python int too large to convert to C long

`

Iv copied and pasted the accepted answer from the post and I run into the same error.

My data looks like this (its already been merged):

cm_time_4           log_time_1
2017-06-25 10:30:35 2017-06-25 10:30:31
2017-06-25 10:50:35 2017-06-25 10:50:31
2017-06-25 11:10:35 2017-06-25 11:10:31
2017-06-25 11:30:35 2017-06-25 11:30:31
2017-06-25 11:50:35 2017-06-25 11:50:31
2017-06-25 12:10:35 2017-06-25 12:10:31
2017-06-25 12:30:35 2017-06-25 12:30:31
2017-06-25 12:50:35 2017-06-25 12:50:31
2017-06-25 13:10:35 2017-06-25 13:10:31
2017-06-25 13:30:35 2017-06-25 13:30:31
2017-06-25 13:50:35 2017-06-25 13:50:31

and my code looks like this:

import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
import scipy.io.netcdf as netcdf

readcsv = pd.read_csv(filename,parse_dates={'timestamp':['date','time']},index_col=['timestamp'])

# round off times to the nearest second
log_time = readcsv.index.round('1s')

fh = netcdf.netcdf_file(nc, mmap=False)
cm_time = fh.variables['time'][:]
ref_time = pd.datetime(year=2017, month=6, day=25, hour=10, minute=30, second=35) # Reference time
cm_time_2 = [ref_time + pd.Timedelta(minutes=np.float(i)) for i in cm_time] # Add seconds to reference time
cm_time_3 = pd.to_datetime(cm_time_2)

idx = np.searchsorted(log_time, cm_time_3) - 1
mask = idx >= 0
df = pd.DataFrame({"log_time_1":log_time[idx][mask], "cm_time_4":cm_time_3[mask]}) 

# Plot
plt.figure(figsize=(18, 4))
plt.vlines(pd.Series(log_time),0,1,colors="g")
plt.vlines(df.log_time_1, 0.3, 0.7, colors="r", lw=2)
plt.vlines(df.cm_time_4, 0.3, 0.7, colors="b", lw=2)

I am using python 3.6 on windows 10

How do I get solve the error?

Many thanks

Answer 1

The pandas datetime size is 64 bits. This allows datetimes with a resolution of up to the nanosecond (the default). However, matplotlib uses the python datetime module which only works with datetimes that have millisecond resolution and are stored as 32-bits. When matplotlib tries to work with your dates, it converts them to the in-built python datetime. This conversion doesn't work properly and raises an exception.

As such, you need to convert your datetimes into 32-bit representations. Modify your plotting code as such:

from datetime import datetime

log_time_py = [datetime.fromtimestamp(dt.timestamp()) for dt in log_time]
log_time_1_py = [datetime.fromtimestamp(dt.timestamp()) for dt in df.log_time_1]
cm_time_4_py = [datetime.fromtimestamp(dt.timestamp()) for dt in df.cm_time_4]

# Plot
plt.figure(figsize=(18, 4))
plt.vlines(log_time_py,0,1,colors="g")
plt.vlines(log_time_1_py, 0.3, 0.7, colors="r", lw=2)
plt.vlines(cm_time_4_py, 0.3, 0.7, colors="b", lw=2)