Creating a histogram

So I’m trying to create a histogram from the data in my created sqlite database.
I extract the data in the form of a histogram type table:

cnx = sqlite3.connect('practice.db')
#pd.read_sql_query
sql = pd.read_sql_query('''
SELECT CAST((deliverydistance/1)as int)*1 as bin, count(*)
FROM orders
group by 1
order by 1;
''',cnx)

The output looks something like this in terminal:

Now when I extract using python, I use the following code to put the values in an array:

distance = []
counts = []
for x,y in sql.iterrows():
    y = y["count(*)"]
    counts.append(y)
    distance.append(x)
print(distance)
print(counts)

Resulting in this:

distance = [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18]
count = [57136, 4711, 6569, 7268, 6755, 5757, 7643, 6175, 7954, 9418, 4945, 4178, 2844, 2104, 1829, 9, 4, 1, 3]

Now when I plot the histogram, I would like count to be on the y axis and the distance to be on the x axis.

plt.hist(counts, bins = distance)

I get the following graph:

How would I get my the ‘counts’ on the y axis and distance on the x axis?

Hi @gianmaranon2703,

plt.hist() plots the frequency automatically. For instance, you have a list of [1,21,2,2,1,2,3,1,3], plt.hist() will count the occurrence of each element and plot it as bars. That’s why in your plot the bars all have the height of 1, the occurrences of values in counts are all once only. Since you already have the distance & counts, you can simply plot with plt.bar(x = distance, height = counts).

Hope this helps! :grinning:

1 Like

Thank you, this helped!!

I also found a solution :slight_smile:

plt.hist(distance, bins=distance, weights=counts)
plt.show()
1 Like

@gianmaranon2703 awesome job finding your own solution! I didn’t know that one. Thanks for sharing. :grinning:

1 Like