CODEWITHSUNDEEP
pythonpandasmatplotlib
acosta1556
acosta1556

Reputation: 53

Plotting a histogram from a database using matplot and python

So from the database, I'm trying to plot a histogram using the matplot lib library in python. as shown here:

cnx = sqlite3.connect('practice.db')

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

which outputs This

From the sql table, I try to extract the columns using a for loop and place them in array.

distance =[]
counts = []

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

OUTPUT:
distance = [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18]

counts = [57136, 4711, 6569, 7268, 6755, 5757, 7643, 6175, 7954, 9418, 4945, 4178, 2844, 2104, 1829, 9, 4, 1, 3]

When I plot a histogram

plt.hist(counts,bins=distance)

I get this out put: click here

My question is, how do I make it so that the count is on the Y axis and the distance is on the X axis? It doesn't seem to allow me to put it there.

Upvotes: 1

Views: 1246

Answers (4)

Q.T
Q.T

Reputation: 98

you could also skip the for loop and plot direct from your pandas dataframe using

sql.bin.plot(kind='hist', weights=sql['count(*)'])

or with the for loop

import matplotlib.pyplot as plt 
import pandas as pd 

distance =[]
counts = []

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


plt.hist(distance, bins=distance, weights=counts)

Upvotes: 1

Krishna75
Krishna75

Reputation: 17

Use :

plt.bar(distance,counts)

Upvotes: 0

EMiller
EMiller

Reputation: 837

You can skip the middle section where you count the instances of each distance. Check out this example:

import pandas as pd
import numpy as np
df = pd.DataFrame({'distance':np.round(20 * np.random.random(100))})
df['distance'].hist(bins = np.arange(0,21,1))

Pandas has a built-in histogram plot which counts, then plots the occurences of each distance. You can specify the bins (in this case 0-20 with a width of 1).

[1]: https://i.sstatic.net/ivYMl.png

Upvotes: 1

David Erickson
David Erickson

Reputation: 16683

If you are not looking for a bar chart and are looking for a horizontal histogram, then you are looking to pass orientation='horizontal':

distance = [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18]
# plt.style.use('dark_background')
counts = [57136, 4711, 6569, 7268, 6755, 5757, 7643, 6175, 7954, 9418, 4945, 4178, 2844, 2104, 1829, 9, 4, 1, 3]
plt.hist(counts,bins=distance, orientation='horizontal')

enter image description here

Upvotes: 0

Related Questions