CODEWITHSUNDEEP
pythonmysqlpandassqlalchemypivot
JonDoe
JonDoe

Reputation: 107

Group Pivot Pandas data from mysql database python

I am pretty new to python and would like to read data from my MySQL database using sqlalchemy in Python. How can I read the data into pandas and use pandas pivot? The database structure looks like the following:

Date_String Experiment  Experiment_Type RESET_FREQUENCY MEASURE_LENGTH  Value   Date_Integer
28-Sep-16   A   FORWARD_Detector    1 Minute    1   0.99994 20160928
28-Sep-16   A   FORWARD_Detector    1 Minute    7   0.99959 20160928
28-Sep-16   A   FORWARD_Detector    1 Minute    14  0.99917 20160928
28-Sep-16   A   FORWARD_Detector    1 Minute    21  0.99876 20160928
28-Sep-16   A   FORWARD_Detector    1 Minute    30  0.99823 20160928
28-Sep-16   A   FORWARD_Detector    1 Minute    60  0.99647 20160928
28-Sep-16   A   FORWARD_Detector    1 Minute    90  0.99469 20160928
28-Sep-16   A   FORWARD_Detector    1 Minute    120 0.99288 20160928
29-Sep-16   A   FORWARD_Detector    1 Minute    1   0.99994 20160929
29-Sep-16   A   FORWARD_Detector    1 Minute    7   0.99959 20160929
29-Sep-16   A   FORWARD_Detector    1 Minute    14  0.99918 20160929
29-Sep-16   A   FORWARD_Detector    1 Minute    21  0.99877 20160929
29-Sep-16   A   FORWARD_Detector    1 Minute    30  0.99824 20160929
29-Sep-16   A   FORWARD_Detector    1 Minute    60  0.99646 20160929
29-Sep-16   A   FORWARD_Detector    1 Minute    90  0.99472 20160929
29-Sep-16   A   FORWARD_Detector    1 Minute    120 0.99287 20160929
30-Sep-16   A   FORWARD_Detector    1 Minute    1   0.99994 20160930
30-Sep-16   A   FORWARD_Detector    1 Minute    7   0.99959 20160930
30-Sep-16   A   FORWARD_Detector    1 Minute    14  0.99918 20160930
30-Sep-16   A   FORWARD_Detector    1 Minute    21  0.99877 20160930
30-Sep-16   A   FORWARD_Detector    1 Minute    30  0.99824 20160930
30-Sep-16   A   FORWARD_Detector    1 Minute    60  0.99647 20160930
30-Sep-16   A   FORWARD_Detector    1 Minute    90  0.99469 20160930
30-Sep-16   A   FORWARD_Detector    1 Minute    120 0.99286 20160930
...

The code looks like the following:

import sqlalchemy as sqlal
import matplotlib.pyplot as plt
import pandas as pd
mysql_engine = sqlal.create_engine('mysql+mysqlconnector://xxx@localhost/rates data',poolclass=sqlal.pool.NullPool)
mysql_engine.echo = False
mysql_engine.connect()
metadata = sqlal.MetaData()
'''
experiment_data = sqlal.Table('experiment_data', metadata,
                                             sqlal.Column('Date_String', sqlal.Date(), nullable=True), 
                                             sqlal.Column('Experiment', sqlal.String(3), nullable=True),
                                             sqlal.Column('Experiment_Type', sqlal.String(8), nullable=True),
                                             sqlal.Column('RESET_FREQUENCY', sqlal.String(3), nullable=True),
                                             sqlal.Column('MEASURE_LENGHT', sqlal.Integer(), nullable=True),
                                             sqlal.Column('Value', sqlal.Float(), nullable=True),
                                             sqlal.Column('Date_Integer', sqlal.Integer(), nullable=True)
                                             )
'''
#print(mysql_engine.table_names())
Data_exp = sqlal.Table('experiment_data', metadata, autoload=True, autoload_with=mysql_engine)
stmt = sqlal.select([Data_exp])
results = mysql_engine.execute(stmt).fetchall()
data_dataframe = pd.DataFrame(results)
mysql_engine.dispose()

# Print the Dataframe
print(data_dataframe)



data_test= pd.pivot_table(Data_IR,index=["Date_String","MEASURE_LENGTH"],values=["Value"])

#optional way to get a pivot table
#data_test= pd.pivot_table(Data_IR,index=["Date_String"],columns=["MEASURE_LENGTH"],values=["Value"])

How can I use the resulting pivot table to plot my results as shown in the figure below?

Desired Graph Output

Upvotes: 1

Views: 632

Answers (1)

Parfait
Parfait

Reputation: 107687

Simply use pandas.DataFrame.plot on the pivot_table object, specifying a line graph. Also, assign Date_String in pivot_table's columns and leave MEASURE_LENGTH for index:

Below includes a data rebuild with pd.read_table() to reproduce your posted data but can be ignored since you source table from MySQL. Also look into pandas.read_sql which can read sqlAlchemy objects.

Data_IR = pandas.read_sql(stmt, con=mysql_engine)

Reproduced Data (data slightly adjusted to not result exactly same for all three dates)

from io import StringIO
import pandas as pd

txt="""
Date_String Experiment  Experiment_Type RESET_FREQUENCY MEASURE_LENGTH  Value   Date_Integer
28-Sep-16   A   FORWARD_Detector    "1 Minute"    1   0.99974 20160928
28-Sep-16   A   FORWARD_Detector    "1 Minute"    7   0.99939 20160928
28-Sep-16   A   FORWARD_Detector    "1 Minute"    14  0.99897 20160928
28-Sep-16   A   FORWARD_Detector    "1 Minute"    21  0.99856 20160928
28-Sep-16   A   FORWARD_Detector    "1 Minute"    30  0.99803 20160928
28-Sep-16   A   FORWARD_Detector    "1 Minute"    60  0.99627 20160928
28-Sep-16   A   FORWARD_Detector    "1 Minute"    90  0.99449 20160928
28-Sep-16   A   FORWARD_Detector    "1 Minute"    120 0.99268 20160928
29-Sep-16   A   FORWARD_Detector    "1 Minute"    1   0.99994 20160929
29-Sep-16   A   FORWARD_Detector    "1 Minute"    7   0.99959 20160929
29-Sep-16   A   FORWARD_Detector    "1 Minute"    14  0.99918 20160929
29-Sep-16   A   FORWARD_Detector    "1 Minute"    21  0.99877 20160929
29-Sep-16   A   FORWARD_Detector    "1 Minute"    30  0.99824 20160929
29-Sep-16   A   FORWARD_Detector    "1 Minute"    60  0.99646 20160929
29-Sep-16   A   FORWARD_Detector    "1 Minute"    90  0.99472 20160929
29-Sep-16   A   FORWARD_Detector    "1 Minute"    120 0.99287 20160929
30-Sep-16   A   FORWARD_Detector    "1 Minute"    1   0.99954 20160930
30-Sep-16   A   FORWARD_Detector    "1 Minute"    7   0.99919 20160930
30-Sep-16   A   FORWARD_Detector    "1 Minute"    14  0.99878 20160930
30-Sep-16   A   FORWARD_Detector    "1 Minute"    21  0.99837 20160930
30-Sep-16   A   FORWARD_Detector    "1 Minute"    30  0.99784 20160930
30-Sep-16   A   FORWARD_Detector    "1 Minute"    60  0.99607 20160930
30-Sep-16   A   FORWARD_Detector    "1 Minute"    90  0.99429 20160930
30-Sep-16   A   FORWARD_Detector    "1 Minute"   120 0.99246 20160930
"""

Data_IR = pd.read_table(StringIO(txt), sep="\\s+")

Plot

import matplotlib.pyplot as plt

data_test= pd.pivot_table(Data_IR,index=["MEASURE_LENGTH"], columns=["Date_String"], values="Value")
data_test.plot(kind='line')

Plot Output

Upvotes: 1

Related Questions