linuxBean14.04(76)MySQL5.6からPython3.4で散布図をプロットする

前の関連記事：linuxBean14.04(75)matplotlibのインタラクティブなグラフ

mysql-connector-pythonのインストール

pq@pq-VirtualBox:~$ conda search mysql-connector-python
Fetching package metadata: ....
mysql-connector-python       2.0.3                    py35_0  defaults        
                             2.0.3                    py34_0  defaults        
                             2.0.3                    py33_0  defaults        
                             2.0.3                    py27_0  defaults        
                             2.0.3                    py26_0  defaults 

matplotlibのグラフで日本語フォントが表示されるようにする

import matplotlib
matplotlib.matplotlib_fname()

pq@pq-VirtualBox:~/anaconda3/lib/python3.4/site-packages/matplotlib/mpl-data/fonts/ttf$ fc-list | grep Takao
/usr/share/fonts/truetype/takao-mincho/TakaoExMincho.ttf: TakaoEx明朝,TakaoExMincho:style=Regular
/usr/share/fonts/truetype/takao-mincho/TakaoPMincho.ttf: Takao P明朝,TakaoPMincho:style=Regular
/usr/share/fonts/truetype/fonts-japanese-gothic.ttf: Takao Pゴシック,TakaoPGothic:style=Regular
/usr/share/fonts/truetype/takao-gothic/TakaoExGothic.ttf: TakaoExゴシック,TakaoExGothic:style=Regular
/usr/share/fonts/truetype/arphic/uming.ttc: Takao Pゴシック,TakaoPGothic:style=Regular
/usr/share/fonts/truetype/fonts-japanese-mincho.ttf: Takao P明朝,TakaoPMincho:style=Regular
/usr/share/fonts/truetype/takao-gothic/TakaoPGothic.ttf: Takao Pゴシック,TakaoPGothic:style=Regular
/usr/share/fonts/truetype/takao-gothic/TakaoGothic.ttf: Takaoゴシック,TakaoGothic:style=Regular
/usr/share/fonts/truetype/takao-mincho/TakaoMincho.ttf: Takao明朝,TakaoMincho:style=Regular

font.family:TakaoGothic

pandasを0.17.0にアップデートする

pq@pq-VirtualBox:~$ conda list pandas
# packages in environment at /home/pq/anaconda3:
#
pandas                    0.16.2               np19py34_0  
pq@pq-VirtualBox:~$ conda update pandas
Fetching package metadata: ....
Solving package specifications: .
Package plan for installation in environment /home/pq/anaconda3:

The following packages will be downloaded:

    package                    |            build
    ---------------------------|-----------------
    numpy-1.10.0               |           py34_0         5.3 MB
    requests-2.8.0             |           py34_0         629 KB
    six-1.10.0                 |           py34_0          17 KB
    conda-3.18.1               |           py34_0         176 KB
    pandas-0.17.0              |      np110py34_0        13.5 MB
    ------------------------------------------------------------
                                           Total:        19.6 MB

The following packages will be UPDATED:

    conda:    3.17.0-py34_0     --> 3.18.1-py34_0     
    numpy:    1.9.3-py34_0      --> 1.10.0-py34_0     
    pandas:   0.16.2-np19py34_0 --> 0.17.0-np110py34_0
    requests: 2.7.0-py34_0      --> 2.8.0-py34_0      
    six:      1.9.0-py34_0      --> 1.10.0-py34_0     

Proceed ([y]/n)? y

MySQL5.6サーバからPython3.4で取得したデータをpandasのデータフレーム型にする

#!~/anaconda3/bin/python3.4
# -*- coding: utf-8 -*-
import mysql.connector
from mysql.connector import errorcode
config = {
    'user': 'User01',
    'password': 'user01_pass',
    'database': 'world'
}
try:
    cnx = mysql.connector.connect(**config)
except mysql.connector.Error as err:
    if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
        print("ユーザー名かパスワードが間違っています。")
    elif err.errno == errorcode.ER_BAD_DB_ERROR:
        print("データベース「{}」が存在しません。".format(config['database']))
    else:
        print(err)
else:
    cursor = cnx.cursor()
    query = 'SELECT Name, Continent, Population, LifeExpectancy, GNP FROM Country'
    cursor.execute(query)
    for (Name, Continent, Population, LifeExpectancy, GNP) in cursor:
        print("{}, {}, {}, {}, {}".format(Name, Continent, Population, LifeExpectancy, GNP))
    cursor.close()
    cnx.close()

    cursor.execute(query)
    rows = cursor.fetchall()
    cursor.close()
    cnx.close()
import pandas as pd
df = pd.DataFrame(rows)
df.rename(columns={0: '国', 1: '大陸', 2: '人口', 3: '平均余命', 4:'国民総生産'}, inplace=True)

else:
    import pandas as pd
    query = 'SELECT Name, Continent, Population, LifeExpectancy, GNP FROM Country'
    df=pd.read_sql(query,cnx)
    df.rename(columns={'Name': '国', 'Continent': '大陸', 'Population': '人口', 'LifeExpectancy': '平均余命', 'GNP':'国民総生産'}, inplace=True)
    cnx.close()

df.dropna(inplace=True)

#!~/anaconda3/bin/python3.4
# -*- coding: utf-8 -*-
import mysql.connector
from mysql.connector import errorcode
config = {
    'user': 'User01',
    'password': 'user01_pass',
    'database': 'world'
}
try:
    cnx = mysql.connector.connect(**config)
except mysql.connector.Error as err:
    if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
        print("ユーザー名かパスワードが間違っています。")
    elif err.errno == errorcode.ER_BAD_DB_ERROR:
        print("データベース「{}」が存在しません。".format(config['database']))
    else:
        print(err)
else:
    import pandas as pd
    query = 'SELECT Name, Continent, Population, LifeExpectancy, GNP FROM Country'
    df=pd.read_sql(query,cnx)
    df.rename(columns={'Name': '国', 'Continent': '大陸', 'Population': '人口', 'LifeExpectancy': '平均余命', 'GNP':'国民総生産'},inplace=True)
    cnx.close()

df.dropna(inplace=True)

In [3]:

df.plot(x='国民総生産',y='平均余命',kind='scatter',figsize=(12,8),title='平均余命 対 国民総生産',grid=True,logx=True,xlim=(100,20000000),fontsize=15,s=df['人口']/100000,alpha=0.5)

Out[3]:

<matplotlib.axes._subplots.AxesSubplot at 0xafd941cc>

/home/pq/anaconda3/lib/python3.4/site-packages/matplotlib/collections.py:590: FutureWarning: elementwise comparison failed; returning scalar instead, but in the future will perform elementwise comparison
  if self._edgecolors == str('face'):

In [4]:

import matplotlib.pyplot as plt
fig, ax = plt.subplots(figsize=(12, 8))
ax.grid(True)
ax.scatter(x=df['国民総生産'],y=df['平均余命'],s=df['人口']/100000,alpha=0.5)
ax.set_xlim((100,20000000))
ax.set_xlabel('国民総生産',size=15)
ax.set_xscale("log")
ax.set_ylabel('平均余命',size=15)
ax.set_title('平均余命 対 国民総生産', size=20)
ax.tick_params(labelsize=15)
plt.show()

/home/pq/anaconda3/lib/python3.4/site-packages/matplotlib/collections.py:590: FutureWarning: elementwise comparison failed; returning scalar instead, but in the future will perform elementwise comparison
  if self._edgecolors == str('face'):

参考にしたサイト