Python crashing when working with more than 300.000 records
0
I'm plotting a seaborn heatmap and for the dataset I'm retrieving records from MySQL. When I run the script it crashes with the following error:
Process finished with exit code 139 (interrupted by signal 11:
SIGSEGV)
At first I thought it had to be in the data, as 300K records is not that much right? But ordering the dataset the other way around when selecting from the database I'm seeing the same behaviour: 300K records is fine, 310K records does cause a crash.
Secondly I tried disabling all the different things in the code to see what is causing the crash. As far as I can tell it's is the groupby that is causing the problem. Hard to be sure as other parts of the code won't work without the groupby, but disabling the merge and the pivot were no solution so it really seems to be the groupby.
Is there another way I should be doing the groupby? or perhaps splitting it or something?
My code:
import pandas as pd
from sqlalchemy import create_engine
import matplotlib.pyplot as plt
import seaborn as sns
import numpy as np
from matplotlib import rcParams
#mysql create engine and retrieve data and put in dataframe
engine = create_engine('mysql+mysqlconnector://[connection data]', echo=False)
connection = engine.connect()
query = connection.execute("SELECT * FROM ActivityRawData order by Time asc limit 0, 310000")
df = pd.DataFrame(query.fetchall())
df.columns = query.keys()
df['Date'] = pd.to_datetime(df['Time'])
df.set_index('Date',inplace=True)
#round for the groupby
df = df.round(1)
#groupby HR and Speed
df = df.groupby(['HR','Speed']).count().reset_index()
#create continuous values and merge to fill up and make sure plot is continuous
dfY = pd.DataFrame({'HR':np.arange(min(df['HR']), max(df['HR']),1)})
dfX = pd.DataFrame({'Speed':np.arange(min(df['Speed']), max(df['Speed']),0.1)})
df = pd.merge(df,dfY, how='outer', on='HR')
df = pd.merge(df,dfX, how='outer', on='Speed')
df = df.round(1)
data = df.pivot("HR","Speed","Distance").sort_values(by=['HR'],ascending=False).drop(np.nan,axis=1).drop(np.nan,axis=0)
#######################
## Create the graph ##
#######################
sns.set(style="white")
#plt.style.use('dark_background')
fig, ax = plt.subplots(figsize=(8,6))
ax=sns.heatmap(data,xticklabels="auto",ax = ax, robust=True, cmap='RdBu_r',cbar_kws={"shrink":.8, "label": "Count"})
#configure and set title
rcParams['axes.titlepad']=20
ax.set_title('Running Efficiency', fontsize=16, fontdict={})
#configure the x and y ticks
plt.xticks(fontsize="9", rotation=45)
plt.yticks(rotation=0, fontsize="10")
plt.ylabel("Average heart rate")
plt.xlabel("Average speed (m/s)")
#set labelsize of the colorbar
cbar = ax.collections[0].colorbar
cbar.ax.tick_params(labelsize=10)
plt.show()
And 100 rows of the data as example:
Alt Cadence Distance HR Lat Lon Speed Time
14.600000381469727 10 10670.7802734375 162 52.684949431568384 4.698166446760297 3.0510001182556152 2018-10-28 10:32:44
14.399999618530273 10 10674.26953125 161 52.68491967581213 4.6981824561953545 3.181999921798706 2018-10-28 10:32:45
14.199999809265135 10 10677.83984375 161 52.6848918478936 4.6982124634087095 3.23799991607666 2018-10-28 10:32:46
14.199999809265135 10 10680.6103515625 161 52.68487055785953 4.6982402075082055 3.23799991607666 2018-10-28 10:32:47
14.399999618530273 10 10684.169921875 161 52.68484013155103 4.698260156437755 3.125999927520752 2018-10-28 10:32:48
14.399999618530273 10 10686.75 162 52.684817416593425 4.698268957436085 3.003999948501587 2018-10-28 10:32:49
14.600000381469727 10 10689.4501953125 162 52.684792522341006 4.698286643251778 2.809000015258789 2018-10-28 10:32:50
14.800000190734865 10 10691.58984375 162 52.68477332778274 4.698283877223731 2.7709999084472656 2018-10-28 10:32:51
15 10 10693.3896484375 162 52.68475765362382 4.698276836425067 2.575000047683716 2018-10-28 10:32:52
15.199999809265135 10 10695.9599609375 161 52.684735022485256 4.69826971180737 2.565999984741211 2018-10-28 10:32:53
15.399999618530273 10 10699.419921875 161 52.684704847633846 4.698256468400358 2.743000030517578 2018-10-28 10:32:54
15.600000381469727 10 10702.3095703125 160 52.68467886373401 4.698254121467472 3.032000064849853 2018-10-28 10:32:55
15.800000190734865 10 10705.25 159 52.68465237691999 4.698256887495519 3.059999942779541 2018-10-28 10:32:56
15.600000381469727 10 10708.400390625 158 52.684624129906304 4.698261078447104 3.0699999332427983 2018-10-28 10:32:57
15.600000381469727 10 10711.990234375 158 52.68459185957909 4.698263006284833 3.1440000534057617 2018-10-28 10:32:58
15.600000381469727 10 10715.240234375 157 52.68456269055605 4.698264850303532 3.3220000267028813 2018-10-28 10:32:59
15.399999618530273 10 10717.919921875 157 52.68453855067492 4.698263173922896 3.3220000267028813 2018-10-28 10:33:00
15.199999809265135 10 10720.75 157 52.68451306968928 4.698261916637421 3.088000059127808 2018-10-28 10:33:01
15 10 10723.580078125 157 52.684487337246544 4.698260743170977 2.994999885559082 2018-10-28 10:33:02
14.800000190734865 10 10726.4599609375 157 52.684461520984776 4.698255965486169 3.0789999961853027 2018-10-28 10:33:03
14.600000381469727 10 10729.1396484375 157 52.684436459094286 4.698256803676487 3.032000064849853 2018-10-28 10:33:04
14.600000381469727 10 10731.75 158 52.68441391177476 4.698244817554952 3.032000064849853 2018-10-28 10:33:05
14.600000381469727 10 10733.66015625 158 52.68439555540681 4.698238614946604 2.7809998989105225 2018-10-28 10:33:06
14.800000190734865 10 10736.2001953125 160 52.68437401391565 4.698225958272816 2.743000030517578 2018-10-28 10:33:07
14.800000190734865 10 10739.48046875 161 52.684344844892614 4.698215480893849 2.743000030517578 2018-10-28 10:33:08
14.800000190734865 10 10742.3095703125 161 52.68431944772601 4.698211960494518 2.874000072479248 2018-10-28 10:33:09
15 10 10744.7001953125 162 52.684298409149044 4.698218666017056 2.874000072479248 2018-10-28 10:33:10
15 10 10747.0595703125 161 52.6842768676579 4.698222856968642 2.7249999046325684 2018-10-28 10:33:11
15.199999809265135 10 10749.6103515625 161 52.68425406888127 4.698230735957623 2.63100004196167 2018-10-28 10:33:12
15.199999809265135 10 10752.58984375 160 52.68422774970532 4.698239956051111 2.63100004196167 2018-10-28 10:33:13
15.199999809265135 10 10755.8896484375 160 52.68420411273837 4.6982696279883385 2.8929998874664307 2018-10-28 10:33:14
15.199999809265135 10 10758.7099609375 158 52.684180224314325 4.698285050690174 2.8929998874664307 2018-10-28 10:33:15
15.199999809265135 10 10761.25 157 52.68415843136609 4.698297204449774 2.882999897003174 2018-10-28 10:33:16
15.199999809265135 10 10763.73046875 157 52.684141919016845 4.698338359594344 2.882999897003174 2018-10-28 10:33:17
15.199999809265135 10 10766.2001953125 157 52.684122556820505 4.6983565483242264 2.82699990272522 2018-10-28 10:33:18
15.199999809265135 10 10769.3896484375 157 52.68409498035908 4.698371049016714 2.8929998874664307 2018-10-28 10:33:19
15 10 10772 157 52.6840767916292 4.698397284373642 2.7809998989105225 2018-10-28 10:33:20
15 10 10774.4697265625 157 52.68405927345157 4.698419999331237 2.7809998989105225 2018-10-28 10:33:21
14.800000190734865 10 10776.650390625 157 52.68404150381684 4.698434248566628 2.650000095367432 2018-10-28 10:33:22
14.800000190734865 10 10778.759765625 156 52.684029182419174 4.698461489751934 2.565999984741211 2018-10-28 10:33:23
14.800000190734865 10 10781.3095703125 156 52.6840139273554 4.69849057495594 2.556999921798706 2018-10-28 10:33:24
14.800000190734865 10 10783.349609375 156 52.68400403670967 4.698517145588994 2.500999927520752 2018-10-28 10:33:25
14.800000190734865 10 10786.0703125 156 52.68399615772069 4.6985556185245505 2.556999921798706 2018-10-28 10:33:26
14.800000190734865 10 10788.33984375 157 52.683991966769106 4.698589397594334 2.565999984741211 2018-10-28 10:33:27
14.800000190734865 10 10791.7099609375 157 52.68398568034172 4.698638264089824 2.63100004196167 2018-10-28 10:33:28
14.600000381469727 10 10794.7900390625 158 52.68397461622954 4.6986801736056805 3.1070001125335693 2018-10-28 10:33:29
14.800000190734865 10 10798.6103515625 158 52.683963384479284 4.698733650147915 3.125999927520752 2018-10-28 10:33:30
14.800000190734865 10 10801.5498046875 159 52.6839523203671 4.698774134740233 3.125999927520752 2018-10-28 10:33:31
15 10 10804.6298828125 159 52.683931533247225 4.6988044772297135 3.032000064849853 2018-10-28 10:33:32
15.199999809265135 10 10807.830078125 159 52.68390789628029 4.698831299319863 2.994999885559082 2018-10-28 10:33:33
15.199999809265135 10 10811.2197265625 159 52.68388685770332 4.698868095874786 2.986000061035156 2018-10-28 10:33:34
15.399999618530273 10 10813.7998046875 159 52.683868166059256 4.698890643194319 2.9210000038146973 2018-10-28 10:33:35
15.399999618530273 10 10816.8798828125 159 52.683849222958095 4.6989243384450665 2.874000072479248 2018-10-28 10:33:36
15.600000381469727 10 10819.5498046875 159 52.683832375332706 4.698952585458756 2.7709999084472656 2018-10-28 10:33:37
15.800000190734865 10 10821.490234375 159 52.68381795845925 4.698968846350908 2.5850000381469727 2018-10-28 10:33:38
16 10 10824.08984375 159 52.683800524100654 4.698995668441056 2.509999990463257 2018-10-28 10:33:39
16.200000762939453 10 10826.419921875 160 52.68378459848464 4.699018215760589 2.4820001125335693 2018-10-28 10:33:40
16.200000762939453 10 10828.66015625 161 52.68376825377345 4.6990391705185175 2.4260001182556152 2018-10-28 10:33:41
16.399999618530273 10 10832.169921875 161 52.683745371177785 4.699077475816011 2.4260001182556152 2018-10-28 10:33:42
16.399999618530273 10 10834.919921875 162 52.683722991496325 4.699094910174608 2.7709999084472656 2018-10-28 10:33:43
16.399999618530273 10 10837.58984375 162 52.683701617643244 4.6991133503615865 2.818000078201294 2018-10-28 10:33:44
16.399999618530273 10 10840.7900390625 162 52.68367672339082 4.699138244614005 2.818000078201294 2018-10-28 10:33:45
16.399999618530273 10 10843.23046875 162 52.683656355366104 4.6991525776684275 2.818000078201294 2018-10-28 10:33:46
16.200000762939453 10 10845.6103515625 161 52.683634394779794 4.699170682579278 2.6689999103546143 2018-10-28 10:33:47
16.200000762939453 10 10848.51953125 161 52.68360824324191 4.699174789711833 2.6689999103546143 2018-10-28 10:33:48
16 10 10851.9404296875 161 52.68357764929533 4.699184009805323 2.845999956130981 2018-10-28 10:33:49
15.600000381469727 10 10854.2802734375 160 52.683556862175465 4.6991913858801135 2.845999956130981 2018-10-28 10:33:50
15.399999618530273 10 10857.4599609375 160 52.68352878279985 4.699200941249729 2.9670000076293945 2018-10-28 10:33:51
15 10 10859.7998046875 160 52.683508163318045 4.699207479134202 2.697000026702881 2018-10-28 10:33:52
14.800000190734865 10 10862 159 52.68348846584559 4.699210580438376 2.697000026702881 2018-10-28 10:33:53
14.600000381469727 10 10864.5302734375 158 52.68346566706896 4.699210664257407 2.697000026702881 2018-10-28 10:33:54
14.199999809265135 10 10867.9296875 158 52.68343515694141 4.699207982048391 2.7809998989105225 2018-10-28 10:33:55
14 10 10870.849609375 157 52.683409340679646 4.6991996839642525 2.9019999504089355 2018-10-28 10:33:56
13.800000190734865 10 10873.7802734375 157 52.683384278789156 4.699186272919178 2.9479999542236333 2018-10-28 10:33:57
13.600000381469727 10 10876.4501953125 156 52.68336148001254 4.699174119159579 2.9019999504089355 2018-10-28 10:33:58
13.600000381469727 10 10878.7197265625 156 52.683341531082995 4.699167162179948 2.809000015258789 2018-10-28 10:33:59
13.600000381469727 10 10880.8603515625 156 52.68332208506764 4.699161043390632 2.6410000324249268 2018-10-28 10:34:00
13.399999618530273 10 10884.25 156 52.68329232931137 4.6991495601832876 2.6410000324249268 2018-10-28 10:34:01
13.399999618530273 10 10887.48046875 157 52.683263579383485 4.69914679415524 2.9210000038146973 2018-10-28 10:34:02
13.600000381469727 10 10890.6201171875 157 52.683235080912716 4.699149392545222 2.9210000038146973 2018-10-28 10:34:03
13.600000381469727 10 10893.5595703125 157 52.683206498622894 4.699148051440716 2.836999893188477 2018-10-28 10:34:04
13.600000381469727 10 10895.9404296875 157 52.68318470567465 4.699138579890132 2.76200008392334 2018-10-28 10:34:05
13.600000381469727 10 10897.509765625 157 52.683173390105374 4.699124582111836 2.61299991607666 2018-10-28 10:34:06
13.600000381469727 10 10899.580078125 157 52.6831560395658 4.6991130989044905 2.2950000762939453 2018-10-28 10:34:07
13.600000381469727 10 10901.830078125 157 52.683137599378824 4.699097340926529 2.2950000762939453 2018-10-28 10:34:08
13.600000381469727 10 10904.5 157 52.683116644620895 4.699075799435377 2.434999942779541 2018-10-28 10:34:09
13.600000381469727 10 10906.849609375 158 52.68309887498617 4.6990533359348765 2.5190000534057617 2018-10-28 10:34:10
13.600000381469727 10 10909.169921875 159 52.68308160826564 4.6990314591676 2.5190000534057617 2018-10-28 10:34:11
13.600000381469727 10 10911.740234375 160 52.68306417390704 4.699004888534546 2.5290000438690186 2018-10-28 10:34:12
13.600000381469727 10 10915.080078125 160 52.683039866387844 4.698975635692476 2.556999921798706 2018-10-28 10:34:13
13.800000190734865 10 10918.4404296875 161 52.683017402887344 4.6989419404417285 2.9110000133514404 2018-10-28 10:34:14
13.800000190734865 10 10922.2099609375 160 52.68299250863493 4.698903635144233 2.938999891281128 2018-10-28 10:34:15
14 10 10925.490234375 160 52.68297281116247 4.6988669224083415 3.0510001182556152 2018-10-28 10:34:16
14.199999809265135 10 10928.6103515625 159 52.682955795899026 4.698829958215357 3.059999942779541 2018-10-28 10:34:17
14.199999809265135 10 10931.6796875 158 52.682941798120744 4.698789473623037 2.9670000076293945 2018-10-28 10:34:18
14.399999618530273 10 10933.8095703125 158 52.682930901646614 4.6987626515328875 2.865000009536743 2018-10-28 10:34:19
14.399999618530273 10 10936.4404296875 158 52.68291547894478 4.698730967938901 2.6589999198913574 2018-10-28 10:34:20
14.399999618530273 10 10938.7099609375 158 52.68290064297617 4.698704229667784 2.565999984741211 2018-10-28 10:34:21
14.600000381469727 10 10940.5400390625 158 52.68288924358786 4.698678581044078 2.5380001068115234 2018-10-28 10:34:22
14.800000190734865 10 10942.7197265625 159 52.6828832924366 4.698647148907185 2.4260001182556152 2018-10-28 10:34:23
python pandas
asked Nov 15 '18 at 19:34
ChrisvdbergeChrisvdberge
5652822
add a comment |
0
I'm plotting a seaborn heatmap and for the dataset I'm retrieving records from MySQL. When I run the script it crashes with the following error:
Process finished with exit code 139 (interrupted by signal 11:
SIGSEGV)
At first I thought it had to be in the data, as 300K records is not that much right? But ordering the dataset the other way around when selecting from the database I'm seeing the same behaviour: 300K records is fine, 310K records does cause a crash.
Secondly I tried disabling all the different things in the code to see what is causing the crash. As far as I can tell it's is the groupby that is causing the problem. Hard to be sure as other parts of the code won't work without the groupby, but disabling the merge and the pivot were no solution so it really seems to be the groupby.
Is there another way I should be doing the groupby? or perhaps splitting it or something?
My code:
import pandas as pd
from sqlalchemy import create_engine
import matplotlib.pyplot as plt
import seaborn as sns
import numpy as np
from matplotlib import rcParams
#mysql create engine and retrieve data and put in dataframe
engine = create_engine('mysql+mysqlconnector://[connection data]', echo=False)
connection = engine.connect()
query = connection.execute("SELECT * FROM ActivityRawData order by Time asc limit 0, 310000")
df = pd.DataFrame(query.fetchall())
df.columns = query.keys()
df['Date'] = pd.to_datetime(df['Time'])
df.set_index('Date',inplace=True)
#round for the groupby
df = df.round(1)
#groupby HR and Speed
df = df.groupby(['HR','Speed']).count().reset_index()
#create continuous values and merge to fill up and make sure plot is continuous
dfY = pd.DataFrame({'HR':np.arange(min(df['HR']), max(df['HR']),1)})
dfX = pd.DataFrame({'Speed':np.arange(min(df['Speed']), max(df['Speed']),0.1)})
df = pd.merge(df,dfY, how='outer', on='HR')
df = pd.merge(df,dfX, how='outer', on='Speed')
df = df.round(1)
data = df.pivot("HR","Speed","Distance").sort_values(by=['HR'],ascending=False).drop(np.nan,axis=1).drop(np.nan,axis=0)
#######################
## Create the graph ##
#######################
sns.set(style="white")
#plt.style.use('dark_background')
fig, ax = plt.subplots(figsize=(8,6))
ax=sns.heatmap(data,xticklabels="auto",ax = ax, robust=True, cmap='RdBu_r',cbar_kws={"shrink":.8, "label": "Count"})
#configure and set title
rcParams['axes.titlepad']=20
ax.set_title('Running Efficiency', fontsize=16, fontdict={})
#configure the x and y ticks
plt.xticks(fontsize="9", rotation=45)
plt.yticks(rotation=0, fontsize="10")
plt.ylabel("Average heart rate")
plt.xlabel("Average speed (m/s)")
#set labelsize of the colorbar
cbar = ax.collections[0].colorbar
cbar.ax.tick_params(labelsize=10)
plt.show()
And 100 rows of the data as example:
Alt Cadence Distance HR Lat Lon Speed Time
14.600000381469727 10 10670.7802734375 162 52.684949431568384 4.698166446760297 3.0510001182556152 2018-10-28 10:32:44
14.399999618530273 10 10674.26953125 161 52.68491967581213 4.6981824561953545 3.181999921798706 2018-10-28 10:32:45
14.199999809265135 10 10677.83984375 161 52.6848918478936 4.6982124634087095 3.23799991607666 2018-10-28 10:32:46
14.199999809265135 10 10680.6103515625 161 52.68487055785953 4.6982402075082055 3.23799991607666 2018-10-28 10:32:47
14.399999618530273 10 10684.169921875 161 52.68484013155103 4.698260156437755 3.125999927520752 2018-10-28 10:32:48
14.399999618530273 10 10686.75 162 52.684817416593425 4.698268957436085 3.003999948501587 2018-10-28 10:32:49
14.600000381469727 10 10689.4501953125 162 52.684792522341006 4.698286643251778 2.809000015258789 2018-10-28 10:32:50
14.800000190734865 10 10691.58984375 162 52.68477332778274 4.698283877223731 2.7709999084472656 2018-10-28 10:32:51
15 10 10693.3896484375 162 52.68475765362382 4.698276836425067 2.575000047683716 2018-10-28 10:32:52
15.199999809265135 10 10695.9599609375 161 52.684735022485256 4.69826971180737 2.565999984741211 2018-10-28 10:32:53
15.399999618530273 10 10699.419921875 161 52.684704847633846 4.698256468400358 2.743000030517578 2018-10-28 10:32:54
15.600000381469727 10 10702.3095703125 160 52.68467886373401 4.698254121467472 3.032000064849853 2018-10-28 10:32:55
15.800000190734865 10 10705.25 159 52.68465237691999 4.698256887495519 3.059999942779541 2018-10-28 10:32:56
15.600000381469727 10 10708.400390625 158 52.684624129906304 4.698261078447104 3.0699999332427983 2018-10-28 10:32:57
15.600000381469727 10 10711.990234375 158 52.68459185957909 4.698263006284833 3.1440000534057617 2018-10-28 10:32:58
15.600000381469727 10 10715.240234375 157 52.68456269055605 4.698264850303532 3.3220000267028813 2018-10-28 10:32:59
15.399999618530273 10 10717.919921875 157 52.68453855067492 4.698263173922896 3.3220000267028813 2018-10-28 10:33:00
15.199999809265135 10 10720.75 157 52.68451306968928 4.698261916637421 3.088000059127808 2018-10-28 10:33:01
15 10 10723.580078125 157 52.684487337246544 4.698260743170977 2.994999885559082 2018-10-28 10:33:02
14.800000190734865 10 10726.4599609375 157 52.684461520984776 4.698255965486169 3.0789999961853027 2018-10-28 10:33:03
14.600000381469727 10 10729.1396484375 157 52.684436459094286 4.698256803676487 3.032000064849853 2018-10-28 10:33:04
14.600000381469727 10 10731.75 158 52.68441391177476 4.698244817554952 3.032000064849853 2018-10-28 10:33:05
14.600000381469727 10 10733.66015625 158 52.68439555540681 4.698238614946604 2.7809998989105225 2018-10-28 10:33:06
14.800000190734865 10 10736.2001953125 160 52.68437401391565 4.698225958272816 2.743000030517578 2018-10-28 10:33:07
14.800000190734865 10 10739.48046875 161 52.684344844892614 4.698215480893849 2.743000030517578 2018-10-28 10:33:08
14.800000190734865 10 10742.3095703125 161 52.68431944772601 4.698211960494518 2.874000072479248 2018-10-28 10:33:09
15 10 10744.7001953125 162 52.684298409149044 4.698218666017056 2.874000072479248 2018-10-28 10:33:10
15 10 10747.0595703125 161 52.6842768676579 4.698222856968642 2.7249999046325684 2018-10-28 10:33:11
15.199999809265135 10 10749.6103515625 161 52.68425406888127 4.698230735957623 2.63100004196167 2018-10-28 10:33:12
15.199999809265135 10 10752.58984375 160 52.68422774970532 4.698239956051111 2.63100004196167 2018-10-28 10:33:13
15.199999809265135 10 10755.8896484375 160 52.68420411273837 4.6982696279883385 2.8929998874664307 2018-10-28 10:33:14
15.199999809265135 10 10758.7099609375 158 52.684180224314325 4.698285050690174 2.8929998874664307 2018-10-28 10:33:15
15.199999809265135 10 10761.25 157 52.68415843136609 4.698297204449774 2.882999897003174 2018-10-28 10:33:16
15.199999809265135 10 10763.73046875 157 52.684141919016845 4.698338359594344 2.882999897003174 2018-10-28 10:33:17
15.199999809265135 10 10766.2001953125 157 52.684122556820505 4.6983565483242264 2.82699990272522 2018-10-28 10:33:18
15.199999809265135 10 10769.3896484375 157 52.68409498035908 4.698371049016714 2.8929998874664307 2018-10-28 10:33:19
15 10 10772 157 52.6840767916292 4.698397284373642 2.7809998989105225 2018-10-28 10:33:20
15 10 10774.4697265625 157 52.68405927345157 4.698419999331237 2.7809998989105225 2018-10-28 10:33:21
14.800000190734865 10 10776.650390625 157 52.68404150381684 4.698434248566628 2.650000095367432 2018-10-28 10:33:22
14.800000190734865 10 10778.759765625 156 52.684029182419174 4.698461489751934 2.565999984741211 2018-10-28 10:33:23
14.800000190734865 10 10781.3095703125 156 52.6840139273554 4.69849057495594 2.556999921798706 2018-10-28 10:33:24
14.800000190734865 10 10783.349609375 156 52.68400403670967 4.698517145588994 2.500999927520752 2018-10-28 10:33:25
14.800000190734865 10 10786.0703125 156 52.68399615772069 4.6985556185245505 2.556999921798706 2018-10-28 10:33:26
14.800000190734865 10 10788.33984375 157 52.683991966769106 4.698589397594334 2.565999984741211 2018-10-28 10:33:27
14.800000190734865 10 10791.7099609375 157 52.68398568034172 4.698638264089824 2.63100004196167 2018-10-28 10:33:28
14.600000381469727 10 10794.7900390625 158 52.68397461622954 4.6986801736056805 3.1070001125335693 2018-10-28 10:33:29
14.800000190734865 10 10798.6103515625 158 52.683963384479284 4.698733650147915 3.125999927520752 2018-10-28 10:33:30
14.800000190734865 10 10801.5498046875 159 52.6839523203671 4.698774134740233 3.125999927520752 2018-10-28 10:33:31
15 10 10804.6298828125 159 52.683931533247225 4.6988044772297135 3.032000064849853 2018-10-28 10:33:32
15.199999809265135 10 10807.830078125 159 52.68390789628029 4.698831299319863 2.994999885559082 2018-10-28 10:33:33
15.199999809265135 10 10811.2197265625 159 52.68388685770332 4.698868095874786 2.986000061035156 2018-10-28 10:33:34
15.399999618530273 10 10813.7998046875 159 52.683868166059256 4.698890643194319 2.9210000038146973 2018-10-28 10:33:35
15.399999618530273 10 10816.8798828125 159 52.683849222958095 4.6989243384450665 2.874000072479248 2018-10-28 10:33:36
15.600000381469727 10 10819.5498046875 159 52.683832375332706 4.698952585458756 2.7709999084472656 2018-10-28 10:33:37
15.800000190734865 10 10821.490234375 159 52.68381795845925 4.698968846350908 2.5850000381469727 2018-10-28 10:33:38
16 10 10824.08984375 159 52.683800524100654 4.698995668441056 2.509999990463257 2018-10-28 10:33:39
16.200000762939453 10 10826.419921875 160 52.68378459848464 4.699018215760589 2.4820001125335693 2018-10-28 10:33:40
16.200000762939453 10 10828.66015625 161 52.68376825377345 4.6990391705185175 2.4260001182556152 2018-10-28 10:33:41
16.399999618530273 10 10832.169921875 161 52.683745371177785 4.699077475816011 2.4260001182556152 2018-10-28 10:33:42
16.399999618530273 10 10834.919921875 162 52.683722991496325 4.699094910174608 2.7709999084472656 2018-10-28 10:33:43
16.399999618530273 10 10837.58984375 162 52.683701617643244 4.6991133503615865 2.818000078201294 2018-10-28 10:33:44
16.399999618530273 10 10840.7900390625 162 52.68367672339082 4.699138244614005 2.818000078201294 2018-10-28 10:33:45
16.399999618530273 10 10843.23046875 162 52.683656355366104 4.6991525776684275 2.818000078201294 2018-10-28 10:33:46
16.200000762939453 10 10845.6103515625 161 52.683634394779794 4.699170682579278 2.6689999103546143 2018-10-28 10:33:47
16.200000762939453 10 10848.51953125 161 52.68360824324191 4.699174789711833 2.6689999103546143 2018-10-28 10:33:48
16 10 10851.9404296875 161 52.68357764929533 4.699184009805323 2.845999956130981 2018-10-28 10:33:49
15.600000381469727 10 10854.2802734375 160 52.683556862175465 4.6991913858801135 2.845999956130981 2018-10-28 10:33:50
15.399999618530273 10 10857.4599609375 160 52.68352878279985 4.699200941249729 2.9670000076293945 2018-10-28 10:33:51
15 10 10859.7998046875 160 52.683508163318045 4.699207479134202 2.697000026702881 2018-10-28 10:33:52
14.800000190734865 10 10862 159 52.68348846584559 4.699210580438376 2.697000026702881 2018-10-28 10:33:53
14.600000381469727 10 10864.5302734375 158 52.68346566706896 4.699210664257407 2.697000026702881 2018-10-28 10:33:54
14.199999809265135 10 10867.9296875 158 52.68343515694141 4.699207982048391 2.7809998989105225 2018-10-28 10:33:55
14 10 10870.849609375 157 52.683409340679646 4.6991996839642525 2.9019999504089355 2018-10-28 10:33:56
13.800000190734865 10 10873.7802734375 157 52.683384278789156 4.699186272919178 2.9479999542236333 2018-10-28 10:33:57
13.600000381469727 10 10876.4501953125 156 52.68336148001254 4.699174119159579 2.9019999504089355 2018-10-28 10:33:58
13.600000381469727 10 10878.7197265625 156 52.683341531082995 4.699167162179948 2.809000015258789 2018-10-28 10:33:59
13.600000381469727 10 10880.8603515625 156 52.68332208506764 4.699161043390632 2.6410000324249268 2018-10-28 10:34:00
13.399999618530273 10 10884.25 156 52.68329232931137 4.6991495601832876 2.6410000324249268 2018-10-28 10:34:01
13.399999618530273 10 10887.48046875 157 52.683263579383485 4.69914679415524 2.9210000038146973 2018-10-28 10:34:02
13.600000381469727 10 10890.6201171875 157 52.683235080912716 4.699149392545222 2.9210000038146973 2018-10-28 10:34:03
13.600000381469727 10 10893.5595703125 157 52.683206498622894 4.699148051440716 2.836999893188477 2018-10-28 10:34:04
13.600000381469727 10 10895.9404296875 157 52.68318470567465 4.699138579890132 2.76200008392334 2018-10-28 10:34:05
13.600000381469727 10 10897.509765625 157 52.683173390105374 4.699124582111836 2.61299991607666 2018-10-28 10:34:06
13.600000381469727 10 10899.580078125 157 52.6831560395658 4.6991130989044905 2.2950000762939453 2018-10-28 10:34:07
13.600000381469727 10 10901.830078125 157 52.683137599378824 4.699097340926529 2.2950000762939453 2018-10-28 10:34:08
13.600000381469727 10 10904.5 157 52.683116644620895 4.699075799435377 2.434999942779541 2018-10-28 10:34:09
13.600000381469727 10 10906.849609375 158 52.68309887498617 4.6990533359348765 2.5190000534057617 2018-10-28 10:34:10
13.600000381469727 10 10909.169921875 159 52.68308160826564 4.6990314591676 2.5190000534057617 2018-10-28 10:34:11
13.600000381469727 10 10911.740234375 160 52.68306417390704 4.699004888534546 2.5290000438690186 2018-10-28 10:34:12
13.600000381469727 10 10915.080078125 160 52.683039866387844 4.698975635692476 2.556999921798706 2018-10-28 10:34:13
13.800000190734865 10 10918.4404296875 161 52.683017402887344 4.6989419404417285 2.9110000133514404 2018-10-28 10:34:14
13.800000190734865 10 10922.2099609375 160 52.68299250863493 4.698903635144233 2.938999891281128 2018-10-28 10:34:15
14 10 10925.490234375 160 52.68297281116247 4.6988669224083415 3.0510001182556152 2018-10-28 10:34:16
14.199999809265135 10 10928.6103515625 159 52.682955795899026 4.698829958215357 3.059999942779541 2018-10-28 10:34:17
14.199999809265135 10 10931.6796875 158 52.682941798120744 4.698789473623037 2.9670000076293945 2018-10-28 10:34:18
14.399999618530273 10 10933.8095703125 158 52.682930901646614 4.6987626515328875 2.865000009536743 2018-10-28 10:34:19
14.399999618530273 10 10936.4404296875 158 52.68291547894478 4.698730967938901 2.6589999198913574 2018-10-28 10:34:20
14.399999618530273 10 10938.7099609375 158 52.68290064297617 4.698704229667784 2.565999984741211 2018-10-28 10:34:21
14.600000381469727 10 10940.5400390625 158 52.68288924358786 4.698678581044078 2.5380001068115234 2018-10-28 10:34:22
14.800000190734865 10 10942.7197265625 159 52.6828832924366 4.698647148907185 2.4260001182556152 2018-10-28 10:34:23
python pandas
asked Nov 15 '18 at 19:34
ChrisvdbergeChrisvdberge
5652822
what are thedtypesof the df that contains the query results?
– Rafael Barros
Nov 15 '18 at 19:36
print(df.dtypes) gives: Alt float64 Cadence float64 Distance float64 HR float64 Lat float64 Lon float64 Speed float64 Time datetime64[ns] dtype: object
– Chrisvdberge
Nov 15 '18 at 19:48
add a comment |
0
0
0
0
I'm plotting a seaborn heatmap and for the dataset I'm retrieving records from MySQL. When I run the script it crashes with the following error:
Process finished with exit code 139 (interrupted by signal 11:
SIGSEGV)
At first I thought it had to be in the data, as 300K records is not that much right? But ordering the dataset the other way around when selecting from the database I'm seeing the same behaviour: 300K records is fine, 310K records does cause a crash.
Secondly I tried disabling all the different things in the code to see what is causing the crash. As far as I can tell it's is the groupby that is causing the problem. Hard to be sure as other parts of the code won't work without the groupby, but disabling the merge and the pivot were no solution so it really seems to be the groupby.
Is there another way I should be doing the groupby? or perhaps splitting it or something?
My code:
import pandas as pd
from sqlalchemy import create_engine
import matplotlib.pyplot as plt
import seaborn as sns
import numpy as np
from matplotlib import rcParams
#mysql create engine and retrieve data and put in dataframe
engine = create_engine('mysql+mysqlconnector://[connection data]', echo=False)
connection = engine.connect()
query = connection.execute("SELECT * FROM ActivityRawData order by Time asc limit 0, 310000")
df = pd.DataFrame(query.fetchall())
df.columns = query.keys()
df['Date'] = pd.to_datetime(df['Time'])
df.set_index('Date',inplace=True)
#round for the groupby
df = df.round(1)
#groupby HR and Speed
df = df.groupby(['HR','Speed']).count().reset_index()
#create continuous values and merge to fill up and make sure plot is continuous
dfY = pd.DataFrame({'HR':np.arange(min(df['HR']), max(df['HR']),1)})
dfX = pd.DataFrame({'Speed':np.arange(min(df['Speed']), max(df['Speed']),0.1)})
df = pd.merge(df,dfY, how='outer', on='HR')
df = pd.merge(df,dfX, how='outer', on='Speed')
df = df.round(1)
data = df.pivot("HR","Speed","Distance").sort_values(by=['HR'],ascending=False).drop(np.nan,axis=1).drop(np.nan,axis=0)
#######################
## Create the graph ##
#######################
sns.set(style="white")
#plt.style.use('dark_background')
fig, ax = plt.subplots(figsize=(8,6))
ax=sns.heatmap(data,xticklabels="auto",ax = ax, robust=True, cmap='RdBu_r',cbar_kws={"shrink":.8, "label": "Count"})
#configure and set title
rcParams['axes.titlepad']=20
ax.set_title('Running Efficiency', fontsize=16, fontdict={})
#configure the x and y ticks
plt.xticks(fontsize="9", rotation=45)
plt.yticks(rotation=0, fontsize="10")
plt.ylabel("Average heart rate")
plt.xlabel("Average speed (m/s)")
#set labelsize of the colorbar
cbar = ax.collections[0].colorbar
cbar.ax.tick_params(labelsize=10)
plt.show()
And 100 rows of the data as example:
Alt Cadence Distance HR Lat Lon Speed Time
14.600000381469727 10 10670.7802734375 162 52.684949431568384 4.698166446760297 3.0510001182556152 2018-10-28 10:32:44
14.399999618530273 10 10674.26953125 161 52.68491967581213 4.6981824561953545 3.181999921798706 2018-10-28 10:32:45
14.199999809265135 10 10677.83984375 161 52.6848918478936 4.6982124634087095 3.23799991607666 2018-10-28 10:32:46
14.199999809265135 10 10680.6103515625 161 52.68487055785953 4.6982402075082055 3.23799991607666 2018-10-28 10:32:47
14.399999618530273 10 10684.169921875 161 52.68484013155103 4.698260156437755 3.125999927520752 2018-10-28 10:32:48
14.399999618530273 10 10686.75 162 52.684817416593425 4.698268957436085 3.003999948501587 2018-10-28 10:32:49
14.600000381469727 10 10689.4501953125 162 52.684792522341006 4.698286643251778 2.809000015258789 2018-10-28 10:32:50
14.800000190734865 10 10691.58984375 162 52.68477332778274 4.698283877223731 2.7709999084472656 2018-10-28 10:32:51
15 10 10693.3896484375 162 52.68475765362382 4.698276836425067 2.575000047683716 2018-10-28 10:32:52
15.199999809265135 10 10695.9599609375 161 52.684735022485256 4.69826971180737 2.565999984741211 2018-10-28 10:32:53
15.399999618530273 10 10699.419921875 161 52.684704847633846 4.698256468400358 2.743000030517578 2018-10-28 10:32:54
15.600000381469727 10 10702.3095703125 160 52.68467886373401 4.698254121467472 3.032000064849853 2018-10-28 10:32:55
15.800000190734865 10 10705.25 159 52.68465237691999 4.698256887495519 3.059999942779541 2018-10-28 10:32:56
15.600000381469727 10 10708.400390625 158 52.684624129906304 4.698261078447104 3.0699999332427983 2018-10-28 10:32:57
15.600000381469727 10 10711.990234375 158 52.68459185957909 4.698263006284833 3.1440000534057617 2018-10-28 10:32:58
15.600000381469727 10 10715.240234375 157 52.68456269055605 4.698264850303532 3.3220000267028813 2018-10-28 10:32:59
15.399999618530273 10 10717.919921875 157 52.68453855067492 4.698263173922896 3.3220000267028813 2018-10-28 10:33:00
15.199999809265135 10 10720.75 157 52.68451306968928 4.698261916637421 3.088000059127808 2018-10-28 10:33:01
15 10 10723.580078125 157 52.684487337246544 4.698260743170977 2.994999885559082 2018-10-28 10:33:02
14.800000190734865 10 10726.4599609375 157 52.684461520984776 4.698255965486169 3.0789999961853027 2018-10-28 10:33:03
14.600000381469727 10 10729.1396484375 157 52.684436459094286 4.698256803676487 3.032000064849853 2018-10-28 10:33:04
14.600000381469727 10 10731.75 158 52.68441391177476 4.698244817554952 3.032000064849853 2018-10-28 10:33:05
14.600000381469727 10 10733.66015625 158 52.68439555540681 4.698238614946604 2.7809998989105225 2018-10-28 10:33:06
14.800000190734865 10 10736.2001953125 160 52.68437401391565 4.698225958272816 2.743000030517578 2018-10-28 10:33:07
14.800000190734865 10 10739.48046875 161 52.684344844892614 4.698215480893849 2.743000030517578 2018-10-28 10:33:08
14.800000190734865 10 10742.3095703125 161 52.68431944772601 4.698211960494518 2.874000072479248 2018-10-28 10:33:09
15 10 10744.7001953125 162 52.684298409149044 4.698218666017056 2.874000072479248 2018-10-28 10:33:10
15 10 10747.0595703125 161 52.6842768676579 4.698222856968642 2.7249999046325684 2018-10-28 10:33:11
15.199999809265135 10 10749.6103515625 161 52.68425406888127 4.698230735957623 2.63100004196167 2018-10-28 10:33:12
15.199999809265135 10 10752.58984375 160 52.68422774970532 4.698239956051111 2.63100004196167 2018-10-28 10:33:13
15.199999809265135 10 10755.8896484375 160 52.68420411273837 4.6982696279883385 2.8929998874664307 2018-10-28 10:33:14
15.199999809265135 10 10758.7099609375 158 52.684180224314325 4.698285050690174 2.8929998874664307 2018-10-28 10:33:15
15.199999809265135 10 10761.25 157 52.68415843136609 4.698297204449774 2.882999897003174 2018-10-28 10:33:16
15.199999809265135 10 10763.73046875 157 52.684141919016845 4.698338359594344 2.882999897003174 2018-10-28 10:33:17
15.199999809265135 10 10766.2001953125 157 52.684122556820505 4.6983565483242264 2.82699990272522 2018-10-28 10:33:18
15.199999809265135 10 10769.3896484375 157 52.68409498035908 4.698371049016714 2.8929998874664307 2018-10-28 10:33:19
15 10 10772 157 52.6840767916292 4.698397284373642 2.7809998989105225 2018-10-28 10:33:20
15 10 10774.4697265625 157 52.68405927345157 4.698419999331237 2.7809998989105225 2018-10-28 10:33:21
14.800000190734865 10 10776.650390625 157 52.68404150381684 4.698434248566628 2.650000095367432 2018-10-28 10:33:22
14.800000190734865 10 10778.759765625 156 52.684029182419174 4.698461489751934 2.565999984741211 2018-10-28 10:33:23
14.800000190734865 10 10781.3095703125 156 52.6840139273554 4.69849057495594 2.556999921798706 2018-10-28 10:33:24
14.800000190734865 10 10783.349609375 156 52.68400403670967 4.698517145588994 2.500999927520752 2018-10-28 10:33:25
14.800000190734865 10 10786.0703125 156 52.68399615772069 4.6985556185245505 2.556999921798706 2018-10-28 10:33:26
14.800000190734865 10 10788.33984375 157 52.683991966769106 4.698589397594334 2.565999984741211 2018-10-28 10:33:27
14.800000190734865 10 10791.7099609375 157 52.68398568034172 4.698638264089824 2.63100004196167 2018-10-28 10:33:28
14.600000381469727 10 10794.7900390625 158 52.68397461622954 4.6986801736056805 3.1070001125335693 2018-10-28 10:33:29
14.800000190734865 10 10798.6103515625 158 52.683963384479284 4.698733650147915 3.125999927520752 2018-10-28 10:33:30
14.800000190734865 10 10801.5498046875 159 52.6839523203671 4.698774134740233 3.125999927520752 2018-10-28 10:33:31
15 10 10804.6298828125 159 52.683931533247225 4.6988044772297135 3.032000064849853 2018-10-28 10:33:32
15.199999809265135 10 10807.830078125 159 52.68390789628029 4.698831299319863 2.994999885559082 2018-10-28 10:33:33
15.199999809265135 10 10811.2197265625 159 52.68388685770332 4.698868095874786 2.986000061035156 2018-10-28 10:33:34
15.399999618530273 10 10813.7998046875 159 52.683868166059256 4.698890643194319 2.9210000038146973 2018-10-28 10:33:35
15.399999618530273 10 10816.8798828125 159 52.683849222958095 4.6989243384450665 2.874000072479248 2018-10-28 10:33:36
15.600000381469727 10 10819.5498046875 159 52.683832375332706 4.698952585458756 2.7709999084472656 2018-10-28 10:33:37
15.800000190734865 10 10821.490234375 159 52.68381795845925 4.698968846350908 2.5850000381469727 2018-10-28 10:33:38
16 10 10824.08984375 159 52.683800524100654 4.698995668441056 2.509999990463257 2018-10-28 10:33:39
16.200000762939453 10 10826.419921875 160 52.68378459848464 4.699018215760589 2.4820001125335693 2018-10-28 10:33:40
16.200000762939453 10 10828.66015625 161 52.68376825377345 4.6990391705185175 2.4260001182556152 2018-10-28 10:33:41
16.399999618530273 10 10832.169921875 161 52.683745371177785 4.699077475816011 2.4260001182556152 2018-10-28 10:33:42
16.399999618530273 10 10834.919921875 162 52.683722991496325 4.699094910174608 2.7709999084472656 2018-10-28 10:33:43
16.399999618530273 10 10837.58984375 162 52.683701617643244 4.6991133503615865 2.818000078201294 2018-10-28 10:33:44
16.399999618530273 10 10840.7900390625 162 52.68367672339082 4.699138244614005 2.818000078201294 2018-10-28 10:33:45
16.399999618530273 10 10843.23046875 162 52.683656355366104 4.6991525776684275 2.818000078201294 2018-10-28 10:33:46
16.200000762939453 10 10845.6103515625 161 52.683634394779794 4.699170682579278 2.6689999103546143 2018-10-28 10:33:47
16.200000762939453 10 10848.51953125 161 52.68360824324191 4.699174789711833 2.6689999103546143 2018-10-28 10:33:48
16 10 10851.9404296875 161 52.68357764929533 4.699184009805323 2.845999956130981 2018-10-28 10:33:49
15.600000381469727 10 10854.2802734375 160 52.683556862175465 4.6991913858801135 2.845999956130981 2018-10-28 10:33:50
15.399999618530273 10 10857.4599609375 160 52.68352878279985 4.699200941249729 2.9670000076293945 2018-10-28 10:33:51
15 10 10859.7998046875 160 52.683508163318045 4.699207479134202 2.697000026702881 2018-10-28 10:33:52
14.800000190734865 10 10862 159 52.68348846584559 4.699210580438376 2.697000026702881 2018-10-28 10:33:53
14.600000381469727 10 10864.5302734375 158 52.68346566706896 4.699210664257407 2.697000026702881 2018-10-28 10:33:54
14.199999809265135 10 10867.9296875 158 52.68343515694141 4.699207982048391 2.7809998989105225 2018-10-28 10:33:55
14 10 10870.849609375 157 52.683409340679646 4.6991996839642525 2.9019999504089355 2018-10-28 10:33:56
13.800000190734865 10 10873.7802734375 157 52.683384278789156 4.699186272919178 2.9479999542236333 2018-10-28 10:33:57
13.600000381469727 10 10876.4501953125 156 52.68336148001254 4.699174119159579 2.9019999504089355 2018-10-28 10:33:58
13.600000381469727 10 10878.7197265625 156 52.683341531082995 4.699167162179948 2.809000015258789 2018-10-28 10:33:59
13.600000381469727 10 10880.8603515625 156 52.68332208506764 4.699161043390632 2.6410000324249268 2018-10-28 10:34:00
13.399999618530273 10 10884.25 156 52.68329232931137 4.6991495601832876 2.6410000324249268 2018-10-28 10:34:01
13.399999618530273 10 10887.48046875 157 52.683263579383485 4.69914679415524 2.9210000038146973 2018-10-28 10:34:02
13.600000381469727 10 10890.6201171875 157 52.683235080912716 4.699149392545222 2.9210000038146973 2018-10-28 10:34:03
13.600000381469727 10 10893.5595703125 157 52.683206498622894 4.699148051440716 2.836999893188477 2018-10-28 10:34:04
13.600000381469727 10 10895.9404296875 157 52.68318470567465 4.699138579890132 2.76200008392334 2018-10-28 10:34:05
13.600000381469727 10 10897.509765625 157 52.683173390105374 4.699124582111836 2.61299991607666 2018-10-28 10:34:06
13.600000381469727 10 10899.580078125 157 52.6831560395658 4.6991130989044905 2.2950000762939453 2018-10-28 10:34:07
13.600000381469727 10 10901.830078125 157 52.683137599378824 4.699097340926529 2.2950000762939453 2018-10-28 10:34:08
13.600000381469727 10 10904.5 157 52.683116644620895 4.699075799435377 2.434999942779541 2018-10-28 10:34:09
13.600000381469727 10 10906.849609375 158 52.68309887498617 4.6990533359348765 2.5190000534057617 2018-10-28 10:34:10
13.600000381469727 10 10909.169921875 159 52.68308160826564 4.6990314591676 2.5190000534057617 2018-10-28 10:34:11
13.600000381469727 10 10911.740234375 160 52.68306417390704 4.699004888534546 2.5290000438690186 2018-10-28 10:34:12
13.600000381469727 10 10915.080078125 160 52.683039866387844 4.698975635692476 2.556999921798706 2018-10-28 10:34:13
13.800000190734865 10 10918.4404296875 161 52.683017402887344 4.6989419404417285 2.9110000133514404 2018-10-28 10:34:14
13.800000190734865 10 10922.2099609375 160 52.68299250863493 4.698903635144233 2.938999891281128 2018-10-28 10:34:15
14 10 10925.490234375 160 52.68297281116247 4.6988669224083415 3.0510001182556152 2018-10-28 10:34:16
14.199999809265135 10 10928.6103515625 159 52.682955795899026 4.698829958215357 3.059999942779541 2018-10-28 10:34:17
14.199999809265135 10 10931.6796875 158 52.682941798120744 4.698789473623037 2.9670000076293945 2018-10-28 10:34:18
14.399999618530273 10 10933.8095703125 158 52.682930901646614 4.6987626515328875 2.865000009536743 2018-10-28 10:34:19
14.399999618530273 10 10936.4404296875 158 52.68291547894478 4.698730967938901 2.6589999198913574 2018-10-28 10:34:20
14.399999618530273 10 10938.7099609375 158 52.68290064297617 4.698704229667784 2.565999984741211 2018-10-28 10:34:21
14.600000381469727 10 10940.5400390625 158 52.68288924358786 4.698678581044078 2.5380001068115234 2018-10-28 10:34:22
14.800000190734865 10 10942.7197265625 159 52.6828832924366 4.698647148907185 2.4260001182556152 2018-10-28 10:34:23
python pandas
asked Nov 15 '18 at 19:34
ChrisvdbergeChrisvdberge
5652822
I'm plotting a seaborn heatmap and for the dataset I'm retrieving records from MySQL. When I run the script it crashes with the following error:
Process finished with exit code 139 (interrupted by signal 11:
SIGSEGV)
At first I thought it had to be in the data, as 300K records is not that much right? But ordering the dataset the other way around when selecting from the database I'm seeing the same behaviour: 300K records is fine, 310K records does cause a crash.
Secondly I tried disabling all the different things in the code to see what is causing the crash. As far as I can tell it's is the groupby that is causing the problem. Hard to be sure as other parts of the code won't work without the groupby, but disabling the merge and the pivot were no solution so it really seems to be the groupby.
Is there another way I should be doing the groupby? or perhaps splitting it or something?
My code:
import pandas as pd
from sqlalchemy import create_engine
import matplotlib.pyplot as plt
import seaborn as sns
import numpy as np
from matplotlib import rcParams
#mysql create engine and retrieve data and put in dataframe
engine = create_engine('mysql+mysqlconnector://[connection data]', echo=False)
connection = engine.connect()
query = connection.execute("SELECT * FROM ActivityRawData order by Time asc limit 0, 310000")
df = pd.DataFrame(query.fetchall())
df.columns = query.keys()
df['Date'] = pd.to_datetime(df['Time'])
df.set_index('Date',inplace=True)
#round for the groupby
df = df.round(1)
#groupby HR and Speed
df = df.groupby(['HR','Speed']).count().reset_index()
#create continuous values and merge to fill up and make sure plot is continuous
dfY = pd.DataFrame({'HR':np.arange(min(df['HR']), max(df['HR']),1)})
dfX = pd.DataFrame({'Speed':np.arange(min(df['Speed']), max(df['Speed']),0.1)})
df = pd.merge(df,dfY, how='outer', on='HR')
df = pd.merge(df,dfX, how='outer', on='Speed')
df = df.round(1)
data = df.pivot("HR","Speed","Distance").sort_values(by=['HR'],ascending=False).drop(np.nan,axis=1).drop(np.nan,axis=0)
#######################
## Create the graph ##
#######################
sns.set(style="white")
#plt.style.use('dark_background')
fig, ax = plt.subplots(figsize=(8,6))
ax=sns.heatmap(data,xticklabels="auto",ax = ax, robust=True, cmap='RdBu_r',cbar_kws={"shrink":.8, "label": "Count"})
#configure and set title
rcParams['axes.titlepad']=20
ax.set_title('Running Efficiency', fontsize=16, fontdict={})
#configure the x and y ticks
plt.xticks(fontsize="9", rotation=45)
plt.yticks(rotation=0, fontsize="10")
plt.ylabel("Average heart rate")
plt.xlabel("Average speed (m/s)")
#set labelsize of the colorbar
cbar = ax.collections[0].colorbar
cbar.ax.tick_params(labelsize=10)
plt.show()
And 100 rows of the data as example:
Alt Cadence Distance HR Lat Lon Speed Time
14.600000381469727 10 10670.7802734375 162 52.684949431568384 4.698166446760297 3.0510001182556152 2018-10-28 10:32:44
14.399999618530273 10 10674.26953125 161 52.68491967581213 4.6981824561953545 3.181999921798706 2018-10-28 10:32:45
14.199999809265135 10 10677.83984375 161 52.6848918478936 4.6982124634087095 3.23799991607666 2018-10-28 10:32:46
14.199999809265135 10 10680.6103515625 161 52.68487055785953 4.6982402075082055 3.23799991607666 2018-10-28 10:32:47
14.399999618530273 10 10684.169921875 161 52.68484013155103 4.698260156437755 3.125999927520752 2018-10-28 10:32:48
14.399999618530273 10 10686.75 162 52.684817416593425 4.698268957436085 3.003999948501587 2018-10-28 10:32:49
14.600000381469727 10 10689.4501953125 162 52.684792522341006 4.698286643251778 2.809000015258789 2018-10-28 10:32:50
14.800000190734865 10 10691.58984375 162 52.68477332778274 4.698283877223731 2.7709999084472656 2018-10-28 10:32:51
15 10 10693.3896484375 162 52.68475765362382 4.698276836425067 2.575000047683716 2018-10-28 10:32:52
15.199999809265135 10 10695.9599609375 161 52.684735022485256 4.69826971180737 2.565999984741211 2018-10-28 10:32:53
15.399999618530273 10 10699.419921875 161 52.684704847633846 4.698256468400358 2.743000030517578 2018-10-28 10:32:54
15.600000381469727 10 10702.3095703125 160 52.68467886373401 4.698254121467472 3.032000064849853 2018-10-28 10:32:55
15.800000190734865 10 10705.25 159 52.68465237691999 4.698256887495519 3.059999942779541 2018-10-28 10:32:56
15.600000381469727 10 10708.400390625 158 52.684624129906304 4.698261078447104 3.0699999332427983 2018-10-28 10:32:57
15.600000381469727 10 10711.990234375 158 52.68459185957909 4.698263006284833 3.1440000534057617 2018-10-28 10:32:58
15.600000381469727 10 10715.240234375 157 52.68456269055605 4.698264850303532 3.3220000267028813 2018-10-28 10:32:59
15.399999618530273 10 10717.919921875 157 52.68453855067492 4.698263173922896 3.3220000267028813 2018-10-28 10:33:00
15.199999809265135 10 10720.75 157 52.68451306968928 4.698261916637421 3.088000059127808 2018-10-28 10:33:01
15 10 10723.580078125 157 52.684487337246544 4.698260743170977 2.994999885559082 2018-10-28 10:33:02
14.800000190734865 10 10726.4599609375 157 52.684461520984776 4.698255965486169 3.0789999961853027 2018-10-28 10:33:03
14.600000381469727 10 10729.1396484375 157 52.684436459094286 4.698256803676487 3.032000064849853 2018-10-28 10:33:04
14.600000381469727 10 10731.75 158 52.68441391177476 4.698244817554952 3.032000064849853 2018-10-28 10:33:05
14.600000381469727 10 10733.66015625 158 52.68439555540681 4.698238614946604 2.7809998989105225 2018-10-28 10:33:06
14.800000190734865 10 10736.2001953125 160 52.68437401391565 4.698225958272816 2.743000030517578 2018-10-28 10:33:07
14.800000190734865 10 10739.48046875 161 52.684344844892614 4.698215480893849 2.743000030517578 2018-10-28 10:33:08
14.800000190734865 10 10742.3095703125 161 52.68431944772601 4.698211960494518 2.874000072479248 2018-10-28 10:33:09
15 10 10744.7001953125 162 52.684298409149044 4.698218666017056 2.874000072479248 2018-10-28 10:33:10
15 10 10747.0595703125 161 52.6842768676579 4.698222856968642 2.7249999046325684 2018-10-28 10:33:11
15.199999809265135 10 10749.6103515625 161 52.68425406888127 4.698230735957623 2.63100004196167 2018-10-28 10:33:12
15.199999809265135 10 10752.58984375 160 52.68422774970532 4.698239956051111 2.63100004196167 2018-10-28 10:33:13
15.199999809265135 10 10755.8896484375 160 52.68420411273837 4.6982696279883385 2.8929998874664307 2018-10-28 10:33:14
15.199999809265135 10 10758.7099609375 158 52.684180224314325 4.698285050690174 2.8929998874664307 2018-10-28 10:33:15
15.199999809265135 10 10761.25 157 52.68415843136609 4.698297204449774 2.882999897003174 2018-10-28 10:33:16
15.199999809265135 10 10763.73046875 157 52.684141919016845 4.698338359594344 2.882999897003174 2018-10-28 10:33:17
15.199999809265135 10 10766.2001953125 157 52.684122556820505 4.6983565483242264 2.82699990272522 2018-10-28 10:33:18
15.199999809265135 10 10769.3896484375 157 52.68409498035908 4.698371049016714 2.8929998874664307 2018-10-28 10:33:19
15 10 10772 157 52.6840767916292 4.698397284373642 2.7809998989105225 2018-10-28 10:33:20
15 10 10774.4697265625 157 52.68405927345157 4.698419999331237 2.7809998989105225 2018-10-28 10:33:21
14.800000190734865 10 10776.650390625 157 52.68404150381684 4.698434248566628 2.650000095367432 2018-10-28 10:33:22
14.800000190734865 10 10778.759765625 156 52.684029182419174 4.698461489751934 2.565999984741211 2018-10-28 10:33:23
14.800000190734865 10 10781.3095703125 156 52.6840139273554 4.69849057495594 2.556999921798706 2018-10-28 10:33:24
14.800000190734865 10 10783.349609375 156 52.68400403670967 4.698517145588994 2.500999927520752 2018-10-28 10:33:25
14.800000190734865 10 10786.0703125 156 52.68399615772069 4.6985556185245505 2.556999921798706 2018-10-28 10:33:26
14.800000190734865 10 10788.33984375 157 52.683991966769106 4.698589397594334 2.565999984741211 2018-10-28 10:33:27
14.800000190734865 10 10791.7099609375 157 52.68398568034172 4.698638264089824 2.63100004196167 2018-10-28 10:33:28
14.600000381469727 10 10794.7900390625 158 52.68397461622954 4.6986801736056805 3.1070001125335693 2018-10-28 10:33:29
14.800000190734865 10 10798.6103515625 158 52.683963384479284 4.698733650147915 3.125999927520752 2018-10-28 10:33:30
14.800000190734865 10 10801.5498046875 159 52.6839523203671 4.698774134740233 3.125999927520752 2018-10-28 10:33:31
15 10 10804.6298828125 159 52.683931533247225 4.6988044772297135 3.032000064849853 2018-10-28 10:33:32
15.199999809265135 10 10807.830078125 159 52.68390789628029 4.698831299319863 2.994999885559082 2018-10-28 10:33:33
15.199999809265135 10 10811.2197265625 159 52.68388685770332 4.698868095874786 2.986000061035156 2018-10-28 10:33:34
15.399999618530273 10 10813.7998046875 159 52.683868166059256 4.698890643194319 2.9210000038146973 2018-10-28 10:33:35
15.399999618530273 10 10816.8798828125 159 52.683849222958095 4.6989243384450665 2.874000072479248 2018-10-28 10:33:36
15.600000381469727 10 10819.5498046875 159 52.683832375332706 4.698952585458756 2.7709999084472656 2018-10-28 10:33:37
15.800000190734865 10 10821.490234375 159 52.68381795845925 4.698968846350908 2.5850000381469727 2018-10-28 10:33:38
16 10 10824.08984375 159 52.683800524100654 4.698995668441056 2.509999990463257 2018-10-28 10:33:39
16.200000762939453 10 10826.419921875 160 52.68378459848464 4.699018215760589 2.4820001125335693 2018-10-28 10:33:40
16.200000762939453 10 10828.66015625 161 52.68376825377345 4.6990391705185175 2.4260001182556152 2018-10-28 10:33:41
16.399999618530273 10 10832.169921875 161 52.683745371177785 4.699077475816011 2.4260001182556152 2018-10-28 10:33:42
16.399999618530273 10 10834.919921875 162 52.683722991496325 4.699094910174608 2.7709999084472656 2018-10-28 10:33:43
16.399999618530273 10 10837.58984375 162 52.683701617643244 4.6991133503615865 2.818000078201294 2018-10-28 10:33:44
16.399999618530273 10 10840.7900390625 162 52.68367672339082 4.699138244614005 2.818000078201294 2018-10-28 10:33:45
16.399999618530273 10 10843.23046875 162 52.683656355366104 4.6991525776684275 2.818000078201294 2018-10-28 10:33:46
16.200000762939453 10 10845.6103515625 161 52.683634394779794 4.699170682579278 2.6689999103546143 2018-10-28 10:33:47
16.200000762939453 10 10848.51953125 161 52.68360824324191 4.699174789711833 2.6689999103546143 2018-10-28 10:33:48
16 10 10851.9404296875 161 52.68357764929533 4.699184009805323 2.845999956130981 2018-10-28 10:33:49
15.600000381469727 10 10854.2802734375 160 52.683556862175465 4.6991913858801135 2.845999956130981 2018-10-28 10:33:50
15.399999618530273 10 10857.4599609375 160 52.68352878279985 4.699200941249729 2.9670000076293945 2018-10-28 10:33:51
15 10 10859.7998046875 160 52.683508163318045 4.699207479134202 2.697000026702881 2018-10-28 10:33:52
14.800000190734865 10 10862 159 52.68348846584559 4.699210580438376 2.697000026702881 2018-10-28 10:33:53
14.600000381469727 10 10864.5302734375 158 52.68346566706896 4.699210664257407 2.697000026702881 2018-10-28 10:33:54
14.199999809265135 10 10867.9296875 158 52.68343515694141 4.699207982048391 2.7809998989105225 2018-10-28 10:33:55
14 10 10870.849609375 157 52.683409340679646 4.6991996839642525 2.9019999504089355 2018-10-28 10:33:56
13.800000190734865 10 10873.7802734375 157 52.683384278789156 4.699186272919178 2.9479999542236333 2018-10-28 10:33:57
13.600000381469727 10 10876.4501953125 156 52.68336148001254 4.699174119159579 2.9019999504089355 2018-10-28 10:33:58
13.600000381469727 10 10878.7197265625 156 52.683341531082995 4.699167162179948 2.809000015258789 2018-10-28 10:33:59
13.600000381469727 10 10880.8603515625 156 52.68332208506764 4.699161043390632 2.6410000324249268 2018-10-28 10:34:00
13.399999618530273 10 10884.25 156 52.68329232931137 4.6991495601832876 2.6410000324249268 2018-10-28 10:34:01
13.399999618530273 10 10887.48046875 157 52.683263579383485 4.69914679415524 2.9210000038146973 2018-10-28 10:34:02
13.600000381469727 10 10890.6201171875 157 52.683235080912716 4.699149392545222 2.9210000038146973 2018-10-28 10:34:03
13.600000381469727 10 10893.5595703125 157 52.683206498622894 4.699148051440716 2.836999893188477 2018-10-28 10:34:04
13.600000381469727 10 10895.9404296875 157 52.68318470567465 4.699138579890132 2.76200008392334 2018-10-28 10:34:05
13.600000381469727 10 10897.509765625 157 52.683173390105374 4.699124582111836 2.61299991607666 2018-10-28 10:34:06
13.600000381469727 10 10899.580078125 157 52.6831560395658 4.6991130989044905 2.2950000762939453 2018-10-28 10:34:07
13.600000381469727 10 10901.830078125 157 52.683137599378824 4.699097340926529 2.2950000762939453 2018-10-28 10:34:08
13.600000381469727 10 10904.5 157 52.683116644620895 4.699075799435377 2.434999942779541 2018-10-28 10:34:09
13.600000381469727 10 10906.849609375 158 52.68309887498617 4.6990533359348765 2.5190000534057617 2018-10-28 10:34:10
13.600000381469727 10 10909.169921875 159 52.68308160826564 4.6990314591676 2.5190000534057617 2018-10-28 10:34:11
13.600000381469727 10 10911.740234375 160 52.68306417390704 4.699004888534546 2.5290000438690186 2018-10-28 10:34:12
13.600000381469727 10 10915.080078125 160 52.683039866387844 4.698975635692476 2.556999921798706 2018-10-28 10:34:13
13.800000190734865 10 10918.4404296875 161 52.683017402887344 4.6989419404417285 2.9110000133514404 2018-10-28 10:34:14
13.800000190734865 10 10922.2099609375 160 52.68299250863493 4.698903635144233 2.938999891281128 2018-10-28 10:34:15
14 10 10925.490234375 160 52.68297281116247 4.6988669224083415 3.0510001182556152 2018-10-28 10:34:16
14.199999809265135 10 10928.6103515625 159 52.682955795899026 4.698829958215357 3.059999942779541 2018-10-28 10:34:17
14.199999809265135 10 10931.6796875 158 52.682941798120744 4.698789473623037 2.9670000076293945 2018-10-28 10:34:18
14.399999618530273 10 10933.8095703125 158 52.682930901646614 4.6987626515328875 2.865000009536743 2018-10-28 10:34:19
14.399999618530273 10 10936.4404296875 158 52.68291547894478 4.698730967938901 2.6589999198913574 2018-10-28 10:34:20
14.399999618530273 10 10938.7099609375 158 52.68290064297617 4.698704229667784 2.565999984741211 2018-10-28 10:34:21
14.600000381469727 10 10940.5400390625 158 52.68288924358786 4.698678581044078 2.5380001068115234 2018-10-28 10:34:22
14.800000190734865 10 10942.7197265625 159 52.6828832924366 4.698647148907185 2.4260001182556152 2018-10-28 10:34:23
python pandas
python pandas
asked Nov 15 '18 at 19:34
ChrisvdbergeChrisvdberge
5652822
asked Nov 15 '18 at 19:34
ChrisvdbergeChrisvdberge
5652822
asked Nov 15 '18 at 19:34
ChrisvdbergeChrisvdberge
5652822
asked Nov 15 '18 at 19:34
ChrisvdbergeChrisvdberge
5652822
asked Nov 15 '18 at 19:34
ChrisvdbergeChrisvdberge
5652822
5652822
what are thedtypesof the df that contains the query results?
– Rafael Barros
Nov 15 '18 at 19:36
print(df.dtypes) gives: Alt float64 Cadence float64 Distance float64 HR float64 Lat float64 Lon float64 Speed float64 Time datetime64[ns] dtype: object
– Chrisvdberge
Nov 15 '18 at 19:48
add a comment |
what are thedtypesof the df that contains the query results?
– Rafael Barros
Nov 15 '18 at 19:36
print(df.dtypes) gives: Alt float64 Cadence float64 Distance float64 HR float64 Lat float64 Lon float64 Speed float64 Time datetime64[ns] dtype: object
– Chrisvdberge
Nov 15 '18 at 19:48
what are the
dtypes of the df that contains the query results?– Rafael Barros
Nov 15 '18 at 19:36
what are the
dtypes of the df that contains the query results?– Rafael Barros
Nov 15 '18 at 19:36
print(df.dtypes) gives: Alt float64 Cadence float64 Distance float64 HR float64 Lat float64 Lon float64 Speed float64 Time datetime64[ns] dtype: object
– Chrisvdberge
Nov 15 '18 at 19:48
print(df.dtypes) gives: Alt float64 Cadence float64 Distance float64 HR float64 Lat float64 Lon float64 Speed float64 Time datetime64[ns] dtype: object
– Chrisvdberge
Nov 15 '18 at 19:48
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53326750%2fpython-crashing-when-working-with-more-than-300-000-records%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53326750%2fpython-crashing-when-working-with-more-than-300-000-records%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
what are the
dtypesof the df that contains the query results?– Rafael Barros
Nov 15 '18 at 19:36
print(df.dtypes) gives: Alt float64 Cadence float64 Distance float64 HR float64 Lat float64 Lon float64 Speed float64 Time datetime64[ns] dtype: object
– Chrisvdberge
Nov 15 '18 at 19:48