I've got a database with latitude and senderID. I want to plot latitudes in a histogram, which is easy enough, but the thing is that rather than just plot the number of occurences pr latitude, I want to plot the density of latitudes pr unique senderID:
Number of latitude rows/Number of unique userids at that latitude
So if I have the following latitudes and users:
|Latitude | User |
70 1
70 2
70 3
70 4
70 5
80 1
80 1
80 2
The density for latitude 70 is 1, while the density for latitude 80 is clearly 1.5.
If I would do this through a database query/pseudocode I would do something like:
SELECT count(latitudes) FROM messages WHERE latitude < lastbin+binwidth AND latitude > lastbin
SELECT count(distinct userid) FROM messages WHERE latitude < lastbin+binwidth AND latitude > lastbin
The density would then be count(latitudes)/count(distinct userid). The binwidth would be about 0.1.
I have some code that I'm not quite sure of:
con = lite.connect(databasepath)
binwidth = 1
latitudes = []
userids = []
densities = []
with con:
cur = con.cursor()
cur.execute('SELECT latitude, userid FROM Message ORDER BY latitude asc')
con.commit()
while True:
tmp = cur.fetchone()
if tmp != None:
latitudes.append(float(tmp[0]))
userids.append(tmp[1])
else:
break
y = -1
for x in range(0, len(latitudes)):
y = y+1
b = x-y
if ((abs(latitudes[x])-abs(latitudes[b])) >= binwidth) or x == len(latitudes)-1:
templatitudes = latitudes[x-y:x]
tempuserids = userids[x-y:x]
tempdensity = math.floor(len(templatitudes)/len(list(set(tempuserids))))
tempdensities = [(templatitudes[0])]*int(tempdensity)
densities.extend(tempdensities)
y = -1
plt.hist(densities, stacked=True, histtype='step', bins=np.arange((min(densities)), (max(densities)) + binwidth, binwidth))
The latitudes is in the range of -90 to 90. When I run this code I get a lack of values alongside +- 10, hence my uncertainty.
To get any help with this is probably a far stretch, but I just cant organize my thoughts to do this while I'm confident there are no errors. I'm sorry if I was unclear.
Aucun commentaire:
Enregistrer un commentaire