Cucco’s Compute Hack

コンピュータ関係の記事を書いていきます。

sqlite3のexecutemanyの動作確認

1回で10万件を挿入するのに、0.35秒。
別実行した1件ソートの繰り返しでは0.42秒。大した差ではないが、ループを組まなくていいメリットあり。

値を埋めたいところは、"INSERT INTO users VALUES(?,?,?)" のようにハテナにしておく。
リストのリストや、タプルのリストにしておけば、全件Insertしてくれるようす。

プログラム
import sqlite3
from faker import Faker
import random
from datetime import datetime

con = sqlite3.connect(":memory:")
con.isolation_level = None  # None で自動コミットモード
cur = con.cursor()
cur.execute('PRAGMA temp_store=MEMORY;')
cur.execute('PRAGMA journal_mode=MEMORY;')

listsize = 100000

sql_create = "CREATE TABLE users(number INTEGER, name TEXT, score REAL)"
sql_instert = "INSERT INTO users VALUES(?,?,?)"
sql_select = "SELECT number,name,score FROM users ORDER BY number LIMIT 3"
sql_select_last = "SELECT number,name,score FROM users ORDER BY number LIMIT 3 OFFSET {0}".format(listsize-3)

list1 = [None] * listsize
fake = Faker()

print("データ準備 開始")
for i in range(listsize):
    list1[i] = [i, fake.name(), random.random()]
print("データ準備 完了")

cur.execute(sql_create)
print("テーブルの定義を表示")
tmp = cur.execute("SELECT * FROM sqlite_master WHERE type='table'").fetchall()
print(tmp)

# ここからexecutemany時間計測
tic = datetime.now()

print("データの挿入")
cur.executemany(sql_instert, list1)

# executemany時間計測終わり
toc = datetime.now()

cur.execute(sql_select)
print("テーブルの中身を表示")
print(cur.fetchone())
print(cur.fetchone())
print(cur.fetchone())
print()
cur.execute(sql_select_last)
print(cur.fetchone())
print(cur.fetchone())
print(cur.fetchone())

print()
print("executemany {0}件 処理時間 {1}".format(listsize, toc-tic))

con.close()
実行結果
(base) C:\dev\SampleCodes\hello_sqlite>python execmeny.py
データ準備 開始
データ準備 完了
テーブルの定義を表示
[('table', 'users', 'users', 2, 'CREATE TABLE users(number INTEGER, name TEXT, score REAL)')]
データの挿入
テーブルの中身を表示
(0, 'Dawn West', 0.5244752623689407)
(1, 'Marvin Chambers', 0.8354440852719125)
(2, 'Clinton Lowe', 0.4116042084368037)

(99997, 'Aaron Conley', 0.795391969430207)
(99998, 'Alyssa Brooks', 0.17316654138026266)
(99999, 'Marisa Peterson', 0.018786705346994004)

executemany 100000件 処理時間 0:00:00.350097

sqlite3とpandasの連動確認

やっていること

0. sqliteのDB(インメモリ)の用意
1. データ準備
2. sqliteのDB(インメモリ)に書き込み
3. sqliteのDB(インメモリ)のテーブル定義を確認
4. sqliteのDB(インメモリ)のテーブルの内容を確認 ORDER BY numberでソート。
5. fillnaでNaN値を補完
6. ソートしなおして、NaNを補完した値で、sqliteに書き込み&内容確認

ソースコード
import sqlite3
import pandas as pd

con = sqlite3.connect(":memory:")
con.isolation_level = None  # None で自動コミットモード
cur = con.cursor()
cur.execute('PRAGMA temp_store=MEMORY;')
cur.execute('PRAGMA journal_mode=MEMORY;')

list1 = [[1, 'alpha', 10.0], [2, 'Bravo', None], [3, 'Charlie', 8.5], [5, 'Echo', 9.8], [4, 'Delta', 3.6]]
#list1 = [[1,'alpha', 10],[2,'Bravo', None],[3,'Charlie', 8], [5,'Foxtrot', 9],[None,'Echo', 3]]
#list1 = [[1,'alpha', 10],[2,'Bravo', 7],[3,'Charlie', 8], [5,'Foxtrot', 9],[4,'Echo', 3]]
df1 = pd.DataFrame(list1, columns=['number', 'name', 'score'])
print(df1)
print(df1.dtypes)

# sqliteに書き込み
df1.to_sql(name='users', con=con, index=False,
           schema='number INTEGER, name TEXT, score REAL')
tmp = cur.execute("SELECT * FROM users").fetchall()

print(tmp)

# schemaに'number TEXT, name TEXT, score REAL'を指定しても、
# 'number TEXT, name TEXT, score REAL'になるので、dataframeの設定が優先される様子。
# 値にNoneがあると、INTEGERではなくREALになる。
tmp = cur.execute("SELECT * FROM sqlite_master WHERE type='table'").fetchall()

print(tmp)

# read_sql_table only supported for SQLAlchemy connectable.
df2 = pd.read_sql(sql='SELECT * FROM users ORDER BY number', con=con)

# df3.to_sql(・・・,if_exists="replace")を実行したとき、
# pandas.io.sql.DatabaseError: Execution failed on sql 'DROP TABLE "users"': database table is locked
# になることがある。再実行するか、curを閉じるとよい。

print(df2)
print(df2.dtypes)

df3 = df2.fillna(method="ffill")
print(df3)

df3.to_sql(name='users', con=con, index=False,
           schema='number INTEGER, name TEXT, score REAL', if_exists="replace")
tmp = cur.execute("SELECT * FROM users").fetchall()
cur.close()
print(tmp)

con.close()
実行結果
   number     name  score
0       1    alpha   10.0
1       2    Bravo    NaN
2       3  Charlie    8.5
3       5     Echo    9.8
4       4    Delta    3.6
number      int64
name       object
score     float64
dtype: object
[(1, 'alpha', 10.0), (2, 'Bravo', None), (3, 'Charlie', 8.5), (5, 'Echo', 9.8), (4, 'Delta', 3.6)]
[('table', 'users', 'users', 2, 'CREATE TABLE "users" (\n"number" INTEGER,\n  "name" TEXT,\n  "score" REAL\n)')]
   number     name  score
0       1    alpha   10.0
1       2    Bravo    NaN
2       3  Charlie    8.5
3       4    Delta    3.6
4       5     Echo    9.8
number      int64
name       object
score     float64
dtype: object
   number     name  score
0       1    alpha   10.0
1       2    Bravo   10.0
2       3  Charlie    8.5
3       4    Delta    3.6
4       5     Echo    9.8
[(1, 'alpha', 10.0), (2, 'Bravo', 10.0), (3, 'Charlie', 8.5), (4, 'Delta', 3.6), (5, 'Echo', 9.8)]

可変引数のテスト**kwargs

可変引数のテスト

プログラム
def func1(**kwargs):
    print(kwargs)
    # キーに一致する値の取得。キーがない場合はNoneになる。
    print(kwargs.get("a"))
    print(kwargs.get("b"))
    print(kwargs.get("c"))
    print(kwargs.get("key1"))
    print(kwargs.get("key2"))
    print(kwargs.get("key3"))


print("func1(key1=1, key2=2)の場合")
func1(key1=1, key2=2)

# 辞書を引数として渡せる
print("func1(**dict1)の場合")
dict1 = {"a": 1, "b": "bb"}
func1(**dict1)
実行結果
func1(key1=1, key2=2)の場合
{'key1': 1, 'key2': 2}
None
None
None
1
2
None
func1(**dict1)の場合
{'a': 1, 'b': 'bb'}
1
bb
None
None
None
None

pythonでyamlファイルの読み込み(コンフィグファイルとして)

yamlに書いた内容が、文字列や数値がその型で取得できる。

辞書型と、リストにはいった辞書のyamlの書き方を追記。

ソースコード

import yaml
import os
import pprint

# yamlファイルは、このhello_yaml.pyと同じディレクトリにある想定
default_yaml_filename = "config.yaml"


class config():
    def __init__(self):
        self.foo = None
        self.bar = None
        self.hoge = None
        self.piyo = None
        
        self.dics = None
        self.dics_list=None
        self.default_yaml_fullpath = os.path.dirname(
            os.path.abspath(__file__)) + os.sep + default_yaml_filename

        self.load()

    def load(self):
        try:
            # 念のため絶対パスでアクセス
            with open(self.default_yaml_fullpath,"r",encoding="utf-8") as f:
                y = yaml.load(stream=f, Loader=yaml.SafeLoader)
                self.foo = y.get("foo")
                self.bar = y.get("bar")
                self.hoge = y.get("hoge")
                self.piyo = y.get("piyo")
                self.dics = y.get("dics")
                self.dics_list = y.get("dics_list")
                
        except:
            print("error", self.default_yaml_fullpath)
            raise


config = config()

print("config.foo: {0} {1}".format(config.foo, type(config.foo)))
print("config.bar: {0} {1}".format(config.bar, type(config.bar)))
print("config.hoge: {0} {1}".format(config.hoge, type(config.hoge)))
print("config.piyo: {0} {1}".format(config.piyo, type(config.piyo)))
print("config.dics: {0} {1}".format(config.dics, type(config.dics)))
pprint.pprint(config.dics)
print("config.dics_list: {0} {1}".format(config.dics_list, type(config.dics_list)))
pprint.pprint(config.dics_list)

config.yamlファイル(ソースコードと同じディレクトリ)

foo: foo1
bar: 100
hoge: 3.14
piyo: 
  - piyo1
  - piyo2
  - 100
  - 3.14
dics:
  x: 100
  y: 200
dics_list:
  - a: 1111
    b: 2222
  - c: 3333
    d: 4444

実行結果

> >python hello_yaml.py
config.foo: foo1 <class 'str'>
config.bar: 100 <class 'int'>
config.hoge: 3.14 <class 'float'>
config.piyo: ['piyo1', 'piyo2', 100, 3.14] <class 'list'>
config.dics: {'x': 100, 'y': 200} <class 'dict'>
{'x': 100, 'y': 200}
config.dics_list: [{'a': 1111, 'b': 2222}, {'c': 3333, 'd': 4444}] <class 'list'>
[{'a': 1111, 'b': 2222}, {'c': 3333, 'd': 4444}]

補足
Loader指定のないyaml.load(f)は推奨されない。y = yaml.load(stream=f, Loader=yaml.SafeLoader)に変更。

C:\dev\SampleCodes\hello_yaml\hello_yaml.py:20: YAMLLoadWarning: calling yaml.load() without Loader=... is deprecated, as the default Loader is unsafe. Please read https://msg.pyyaml.org/load for full details.
  y = yaml.load(f)

Visual Studio Code & Anaconda環境構築

最近、VSCodePython環境を作るとデバッグ実行がうまくいかないことがあるので、試行錯誤のメモ。

path(ユーザーの環境変数)に以下を追加

C:\Users\<ユーザー名>\Anaconda3\Scripts
C:\Users\<ユーザー名>\Anaconda3\

anacondaの初期設定

「CommandNotFoundError: Your shell has not been properly configured to use 'conda activate'.」と言われたとき。

(base) C:\Users\<ユーザー名>>conda init
no change C:\Users\<ユーザー名>\Anaconda3\Scripts\conda.exe
no change C:\Users\<ユーザー名>\Anaconda3\Scripts\conda-env.exe
no change C:\Users\<ユーザー名>\Anaconda3\Scripts\conda-script.py
no change C:\Users\<ユーザー名>\Anaconda3\Scripts\conda-env-script.py
no change C:\Users\<ユーザー名>\Anaconda3\condabin\conda.bat
no change C:\Users\<ユーザー名>\Anaconda3\Library\bin\conda.bat
no change C:\Users\<ユーザー名>\Anaconda3\condabin\_conda_activate.bat
no change C:\Users\<ユーザー名>\Anaconda3\condabin\rename_tmp.bat
no change C:\Users\<ユーザー名>\Anaconda3\condabin\conda_auto_activate.bat
no change C:\Users\<ユーザー名>\Anaconda3\condabin\conda_hook.bat
no change C:\Users\<ユーザー名>\Anaconda3\Scripts\activate.bat
no change C:\Users\<ユーザー名>\Anaconda3\condabin\activate.bat
no change C:\Users\<ユーザー名>\Anaconda3\condabin\deactivate.bat
modified C:\Users\<ユーザー名>\Anaconda3\Scripts\activate
modified C:\Users\<ユーザー名>\Anaconda3\Scripts\deactivate
modified C:\Users\<ユーザー名>\Anaconda3\etc\profile.d\conda.sh
modified C:\Users\<ユーザー名>\Anaconda3\etc\fish\conf.d\conda.fish
no change C:\Users\<ユーザー名>\Anaconda3\shell\condabin\Conda.psm1
modified C:\Users\<ユーザー名>\Anaconda3\shell\condabin\conda-hook.ps1
modified C:\Users\<ユーザー名>\Anaconda3\Lib\site-packages\xonsh\conda.xsh
modified C:\Users\<ユーザー名>\Anaconda3\etc\profile.d\conda.csh
modified C:\Users\<ユーザー名>\Documents\WindowsPowerShell\profile.ps1
modified HKEY_CURRENT_USER\Software\Microsoft\Command Processor\AutoRun

「Missing required dependencies ['numpy']」と言われたら以下。
ファイルのありか Python Extension Packages for Windows - Christoph Gohlke
参考サイトscipyが突然読み込まれなくなった話 - Qiita

pip install C:\Users\<ユーザー名>\Downloads\numpy-1.16.2+mkl-cp37-cp37m-win_amd64.whl

PowerShellを管理者として実行

Set-ExecutionPolicy -ExecutionPolicy RemoteSigned

pandasでcsv読みこみ datetime型の型指定で。

pandasでファイルの読み書きのテスト。datetimeの処理が必要なので、型指定の手順を確認。

読んでるファイル 'Book2.csv'
,time,x,y,z
0,2019-03-23 08:53:16,0.384126267,0.791150474,1
1,2019-03-23 08:53:16,0.121509436,0.161273729,3
2,2019-03-23 08:53:16,0.97859278,0.926904462,5
3,2019-03-23 08:53:16,0.824561636,0.455903221,7
4,2019-03-23 08:53:16,0.543611046,0.7457197440000001,9
5,2019-03-23 08:53:16,0.056624959,0.39308888200000003,0
6,2019-03-23 08:53:16,0.912447124,0.7451860359999999,-1
7,2019-03-23 08:53:16,0.354390345,0.881826662,-3
8,2019-03-23 08:53:16,0.7894431120000001,0.256685437,-5
9,2019-03-23 08:53:16,0.758507423,0.067165236,-7
10,2019-03-23 08:53:16,0.400961991,0.547244365,-9
プログラム
import pandas as pd

book2_dtypes = {'time':'str', 'x':'float', 'y':'str','z':'int'}
datetime_format= '%Y-%m-%d %H:%M:%S'
datetime_parser = lambda date: pd.datetime.strptime(date, datetime_format)

df1 = pd.read_csv('Book2.csv', index_col=0, dtype=book2_dtypes, parse_dates=[1], date_parser = datetime_parser)

print(df1["time"].dtype)
print(df1["x"].dtype)
print(df1["y"].dtype)
print(df1["z"].dtype)

print(df1)

df1.to_csv("Book3.csv",sep=",",encoding="utf_8")
実行結果
datetime64[ns]
float64
object
int32
                  time         x                    y  z
0  2019-03-23 08:53:16  0.384126          0.791150474  1
1  2019-03-23 08:53:16  0.121509          0.161273729  3
2  2019-03-23 08:53:16  0.978593          0.926904462  5
3  2019-03-23 08:53:16  0.824562          0.455903221  7
4  2019-03-23 08:53:16  0.543611   0.7457197440000001  9
5  2019-03-23 08:53:16  0.056625  0.39308888200000003  0
6  2019-03-23 08:53:16  0.912447   0.7451860359999999 -1
7  2019-03-23 08:53:16  0.354390          0.881826662 -3
8  2019-03-23 08:53:16  0.789443          0.256685437 -5
9  2019-03-23 08:53:16  0.758507          0.067165236 -7
10 2019-03-23 08:53:16  0.400962          0.547244365 -9

移動標準偏差

移動平均同様、numpyを使って、2次元のマトリックスに対する移動標準偏差の計算。
移動平均も内部で利用。

プログラム
import numpy as np

def moving_sum(data_2d,axis=1,windowsize=3):
    answer = np.zeros((data_2d.shape))
    answer[:,:] = np.nan

    v = np.ones(windowsize,)

    for i in range(data.shape[axis]):
        if axis==0:
            answer[i,windowsize-1:]=np.convolve(data_2d[i,:], v, mode = "valid")
        if axis==1:
            answer[windowsize-1:,i]=np.convolve(data_2d[:,i], v, mode = "valid")
        answer=answer
    return answer

def moving_average(data_2d,axis=1,windowsize=3):
    return moving_sum(data_2d,axis,windowsize)/windowsize

def moving_std(data_2d,axis=1,windowsize=3):
    answer = np.zeros((data_2d.shape))
    answer[:,:] = np.nan

    answer = moving_sum(np.square(data_2d),axis,windowsize) -\
        np.square(moving_average(data_2d,axis,windowsize))*windowsize
    answer = answer/(windowsize)
    answer = np.sqrt(answer)

    return answer

#4列のデータが8点
#data=np.arange(32)
#data=np.random.rand(32)
#data=data.reshape(8,4)

#検算のため即値で
data=np.array([[0.86619006, 0.9130783,  0.51988756, 0.35008161],
 [0.12355818, 0.3230697,  0.70366867, 0.74275339],
 [0.58942652, 0.74948935, 0.30359438, 0.55652164],
 [0.40820522, 0.85400935, 0.29218585, 0.21874757],
 [0.06330341, 0.91181499, 0.73940466, 0.88877802],
 [0.7945424,  0.67662696, 0.44624821, 0.65392414],
 [0.26358476, 0.43238069, 0.00853011, 0.05989708],
 [0.89179866, 0.52684014, 0.14116962, 0.6934826 ]])


print("元データ")
print(data)
print("横方向で平均")
answer = moving_average(data,axis=0,windowsize=3)
print(answer)
print("縦方向で平均")
answer = moving_average(data,axis=1,windowsize=3)
print(answer)
print("横方向で標準偏差")
answer=moving_std(data,axis=0,windowsize=3)
print(answer)
print("縦向で標準偏差")
answer=moving_std(data,axis=1,windowsize=3)
print(answer)
print("検算 縦向 右下の値 標準偏差")
print(data[5:8,3])
print(np.std(data[5:8,3]))
実行結果
元データ
[[0.86619006 0.9130783  0.51988756 0.35008161]
 [0.12355818 0.3230697  0.70366867 0.74275339]
 [0.58942652 0.74948935 0.30359438 0.55652164]
 [0.40820522 0.85400935 0.29218585 0.21874757]
 [0.06330341 0.91181499 0.73940466 0.88877802]
 [0.7945424  0.67662696 0.44624821 0.65392414]
 [0.26358476 0.43238069 0.00853011 0.05989708]
 [0.89179866 0.52684014 0.14116962 0.6934826 ]]
横方向で平均
[[       nan        nan 0.76638531 0.59434916]
 [       nan        nan 0.38343218 0.58983059]
 [       nan        nan 0.54750342 0.53653512]
 [       nan        nan 0.51813347 0.45498092]
 [       nan        nan 0.57150769 0.84666589]
 [       nan        nan 0.63913919 0.59226644]
 [       nan        nan 0.23483185 0.16693596]
 [       nan        nan 0.51993614 0.45383079]]
縦方向で平均
[[       nan        nan        nan        nan]
 [       nan        nan        nan        nan]
 [0.52639159 0.66187912 0.5090502  0.54978555]
 [0.37372997 0.64218947 0.43314963 0.50600753]
 [0.35364505 0.8384379  0.44506163 0.55468241]
 [0.42201701 0.81415043 0.49261291 0.58714991]
 [0.37381019 0.67360755 0.39806099 0.53419975]
 [0.64997527 0.5452826  0.19864931 0.46910127]]
横方向で標準偏差
[[       nan        nan 0.17534819 0.23579612]
 [       nan        nan 0.24064464 0.18930211]
 [       nan        nan 0.1844338  0.18258364]
 [       nan        nan 0.24217704 0.28374409]
 [       nan        nan 0.36618303 0.07642602]
 [       nan        nan 0.14464027 0.10366564]
 [       nan        nan 0.17422663 0.1888656 ]
 [       nan        nan 0.30648191 0.23131534]]
縦向で標準偏差
[[       nan        nan        nan        nan]
 [       nan        nan        nan        nan]
 [0.30643714 0.24870894 0.16350932 0.16037833]
 [0.1917459  0.22965072 0.19134254 0.21688596]
 [0.21822617 0.06717765 0.20818406 0.27354188]
 [0.29868678 0.10006632 0.1854965  0.27758398]
 [0.30853401 0.19573988 0.30031751 0.34881834]
 [0.27608926 0.10056226 0.1832616  0.28980139]]
検算 縦向 右下の値 標準偏差
[0.65392414 0.05989708 0.6934826 ]
0.28980139385514914