来自mysql数据库的Pivot Pandas数据集python

问题描述：

我对Python很新，想从我的MySQL数据库中读取数据，使用Python中的sqlalchemy。我如何将数据读入熊猫并使用熊猫枢轴？数据库结构如下所示：来自mysql数据库的Pivot Pandas数据集python

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 
...

的代码如下所示：

import sqlalchemy as sqlal 
import matplotlib.pyplot as plt 
import pandas as pd 
mysql_engine = sqlal.create_engine('mysql+mysqlconnector://[email protected]/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"])

如何使用所产生的数据透视表如下图所示的图形绘制我的结果？

@Parfait，非常感谢。我更新了这个例子。我不知道如何绘制我的数据透视表？ – JonDoe

答

的pivot_table对象上只需使用pandas.DataFrame.plot，指定线曲线图。此外，在分配pivot_table的列Date_String离开MEASURE_LENGTH为指数：

下面包括与pd.read_table()数据重建重现您发布的数据，但可以因为从MySQL你源表中被忽略。还可以看看可以读取sqlAlchemy对象的pandas.read_sql。

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

重现数据（数据略作调整，以不会导致所有三个日期完全相同）

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+")

情节

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')

来自mysql数据库的Pivot Pandas数据集python

相关推荐