MBasith MBasith - 1 month ago 12
Python Question

Python Plotting Pandas SQL Dataframe with Seaborn

I am new to data visualization and attempting to make a simple time series plot using an SQL output and seaborn. I am having difficulty inserting the data retrieved from the SQL query into Seaborn. Is there some direction you can give me on how to visualize this dataframe using Seaborn?

My Python Code:

#!/usr/local/bin/python3.5

import cx_Oracle
import pandas as pd
from IPython.display import display, HTML
import matplotlib.pyplot as plt
import seaborn as sns

orcl = cx_Oracle.connect('sql_user/sql_pass//sql_database_server.com:9999/SQL_REPORT')

sql = '''
select DATETIME, FRUIT,
COUNTS
from FRUITS.HEALTHY_FRUIT
WHERE DATETIME > '01-OCT-2016'
AND FRUIT = 'APPLE'
'''

curs = orcl.cursor()

df = pd.read_sql(sql, orcl)
display(df)

sns.kdeplot(df)
plt.show()


Dataframe (df) output:

DATETIME FRUIT COUNTS
0 2016-10-02 APPLE 1.065757e+06
1 2016-10-03 APPLE 1.064369e+06
2 2016-10-04 APPLE 1.067552e+06
3 2016-10-05 APPLE 1.068010e+06
4 2016-10-06 APPLE 1.067118e+06
5 2016-10-07 APPLE 1.064925e+06
6 2016-10-08 APPLE 1.066576e+06
7 2016-10-09 APPLE 1.065982e+06
8 2016-10-10 APPLE 1.072131e+06
9 2016-10-11 APPLE 1.076429e+06


When I try to run plt.show() I get the following error:

TypeError: cannot astype a datetimelike from [datetime64[ns]] to [float64]

Answer

Instead of sns.kdeplot try the following:

# make time the index (this will help with plot ticks)
df.set_index('DATETIME', inplace=True)

# make figure and axis objects
fig, ax = sns.plt.subplots(1, 1, figsize=(6,4))
df.plot(y='COUNTS', ax=ax, color='red', alpha=.6)
fig.savefig('test.pdf')
plt.show()

The function kdeplot() is not what you want if you're trying to make a line graph. It does make a line, but the line is intended to approximate the distribution of a variable rather than show how a variable changes over time. By far the easiest way to make a line plot is from pandas df.plot(). If you want the styling options of seaborn, you can use sns.plt.subplots to create your axis object (what I do). You can also use sns.set_style() like in this question.

Comments