python_analytics

主にpythonやライブラリーを使ったデータ解析、機械学習、統計学などについて書いていきます

SQL の基本的な操作について

SQLの基本的な操作について

--データベース作成--

sqlite3 データベース名

sqlite3 myfriend.sqlite3

②--CSV インポート方法--

.separator ,
.import sample22.csv page1

③--接続していたデータベースから切断--

.exit

④テーブル一覧表示

.tables  

スキーマの表示

.tables 
customer_id store_id firtst_name last_name email adress_id
54 1 TERESA ROGERS TERESA.ROGERS@sakilacustomer.org 58
72 2 THERESA WATSON THERESA.WATSON@sakilacustomer.org 76
75 2 TAMMY SANDERS TAMMY.SANDERS@sakilacustomer.org 79
92 2 TINA SIMMONS TINA.SIMMONS@sakilacustomer.org 96
108 1 TRACY COLE TRACY.COLE@sakilacustomer.org 112
110 2 TIFFANY JORDAN TIFFANY.JORDAN@sakilacustomer.org 114
122 1 THELMA MURRAY THELMA.MURRAY@sakilacustomer.org 126
203 1 TARA RYAN TARA.RYAN@sakilacustomer.org 207
206 1 TERRI VASQUEZ TERRI.VASQUEZ@sakilacustomer.org 210

フィルタ条件について 例:Tを含むFirest_nameのフィルタ

select * from customer where first_name    like 'T%';

重複のみを抽出する

select * from テーブル名
where 列名 in
(select 列名 from テーブル名group by 列名
having count(*) > 1)
order by 列名 desc;

一時的にテーブルを作成する。

WITH CTE1 AS (
    SELECT 列, 列 FROM テーブル
  ), CTE2 AS (
    SELECT ID, title FROM テーブル名
  )
SELECT * FROM CTE1
UNION ALL
SELECT * FROM CTE2;
WITH cte1 AS (SELECT * FROM テーブル名)
   , cte2 AS (SELECT * FROM テーブル名)
SELECt * FROM cte2;

重複なしで結合

select
page3.ID,
page3.title
from
day
inner join
page3
on page3.ID = day.ID;

条件式で値を追加する。

select
ID,count(*),
case 
 when count(*) <5 
 then "5以下"
 when count(*) >6 
 then "6以上"
 else "その他"
end as ranking
from
page1
group by ID
;

ユニークIDのカウント

select
ID, count(*)
from
page1
group by ID
having count(*)>5
order by count(*) desc
;

dbを接続する

conn = sqlite3.connect('myfriend')
cursor = conn.cursor()

pythonでsqlite3のテーブル一覧を表示させる。

pd.io.sql.read_sql_query('select * from {table}'.format(table='page3'), conn)

グループ化したものをカウントする

SELECT visit,month, count(visit) FROM (SELECT month,ID,COUNT(month) as visit FROM fb_log GROUP by ID) as a GROUP BY visit,month;

文字列分割でカラム追加

select ID,ページ タイトル,ページ,substr(時間帯, 1, 4) as 年,substr(時間帯, 5, 2) as 月,substr(時間帯, 7, 2) as 日,substr(時間帯, 9, 2) asfrom tokushu5_1;

含むページで勝てカテゴリ列追加でフラグ立て

select  *,
case 
when page like "%aa%" then 'aa' 
when page like "bb%" then 'bb' 
when page like "%cc%" then 'cc' 
when page like "%dd%" then 'dd' 
else 'Fail' end as category from "5_tokushu"

python 正規表現について

特殊シーケンス 説明 同義のパターン
\d 任意の数字 [0-9]
\D 任意の数字以外 [^0-9]
\s 任意の空白文字 [\t\n\r\f\v]
\S 任意の空白文字以外 [^\t\n\r\f\v]
\w 任意の英数字 [a-xA-Z0-9_]
\W 任意の英数字以外 [\a-xA-Z0-9_]
\A 文字列の先頭 ^
\Z 文字列の末尾 $
文字 説明 マッチする例
? 0回または1回 ab? a, ab
. 任意の一文字 a.c abc, acc, aac
^ 文字列の先頭 ^abc abcdef
$ 文字列の末尾 abc$ defabc
* 0回以上の繰り返し ab* a, ab, abb, abbb
+ 1回以上の繰り返し ab+ ab, abb, abbb
{n} n回の繰り返し a{2} aa
和集合(または) a|b | a, b
() グループ化 (abc)+ abc, abcabc

,,,python report[report['ランディング ページ'].str.contains('12')] ,,,

文字列を含む条件分岐 ,,,python

def func_cate(x): if "test" in x: return "0" elif "test1" in x: return "1" elif test2" in x: return "2" rename1['cate'] = rename1['ページ タイトル'].apply(func_cate) rename1.tail(10) ,,,

python seleniumでスクレイピング

seleniumスクレイピングして、タイトルとURLをCSVに保存する。

from selenium import webdriver

browser = webdriver.Firefox()
url = "http://b.hatena.ne.jp/search/text?safe=on&q=Python&users=5"
browser.get(url)
df2 = pd.read_csv('trend.csv', index_col=0)
posts = browser.find_elements_by_css_selector(".search-result")

for post in posts:
    title = post.find_element_by_css_selector("h3").text
    bookmarks = post.find_element_by_css_selector('h3 > a').get_attribute('href')
    se = pd.Series([title,bookmarks],['title','bookmarks'])
    df2 = df2.append(se, ignore_index=True)
df2.to_csv("trend2.csv")

python pandas sortやfilterについて

pandas 集計処理のsort,filter操作について

↓フィルタ_日カラムで20以上の行を抽出。

sample[sample["日"]>=20].head()
index ID セッション 閲覧時間
0 328667572 31 16 38 5
1 70373573 24 23 37 7
5 302325623 20 8 22 2
12 1511512688 29 21 18 6
13 1842034778 28 19 18 6

↓【フィルタ】日カラムで20の行を抽出。

sample[sample["日"]==20].head()
index ID セッション
5 302325623 20 8 22
22 2034587212 20 23 16
60 737941086 20 1 13
156 1436667790 20 0 10
187 232403266 20 12 10

↓【フィルタ】全体平均以上のセッションのみを抽出。

sample[sample["セッション"]>=sample["セッション"].mean()].head()
ID セッション 閲覧時間
328667572 31 16 38 5
70373573 24 23 37 7
1839656582 12 20 28 6
1471882803 10 22 22 7
302325623 20 8 22 2

↓【並べ替え】IDカラムを昇順で表示。

sample.sort_values(by="ID",ascending=True).head()
ID セッション 閲覧時間
9460 14 15 1 4
9460 25 20 1 6
72625 15 21 1 6
72625 13 20 1 6
85657 13 14 1 4

↓【並べ替え】カラム日が20でカラム時を降順で表示。

sample[sample["日"]==20].sort_values(by="時",ascending=False).head()
ID セッション 閲覧時間
698149150 20 23 1 7
1470678968 20 23 1 7
1978534706 20 23 1 7
561150499 20 23 1 7
1978434835 20 23 1 7

↓【フィルタ】1カラムの6or10をフィルタして抽出

sample.query("時==16 or 時==10")
ID date time セッション 重複 count
0 328667572 20180331 2018-03-31 16:00:00 38 True 31 16
34 129117501 20180311 2018-03-11 10:00:00 14 True 11 10
58 570969895 20180323 2018-03-23 16:00:00 13 True 23 16
88 669719439 20180321 2018-03-21 10:00:00 12 True 21 10
147 1217010981 20180310 2018-03-10 16:00:00 10 True 10 16

↓【フィルタ】500以上且つ11月カラムのみをフィルタして抽出

ff2[(ff2["500以上"]=="500以上") & (ff2["月"]==11)]

pandas csv一括読み込み

csv一括読み込み

csv一括してDataFrameにする方法

import glob
import os

files = glob.glob(os.path.join("フォルダ名",'*.csv'))

df_list=[]
for file in files:
    tmp_df = pd.read_csv(file,encoding='cp932',parse_dates=[""],header=1])
    tmp_df['filename'] = os.path.basename(file)
    df_list.append(tmp_df)
df = pd.concat(df_list, ignore_index=True)
ff=pd.DataFrame(df)

python デコレータについて

デコレータについて

■デコレータについてト
デコレータは関数をラップすることで、元の関数は変更しないで前後に処理を挟んだり返す値を変更したりできます。

def デコレータ名(func):
    def ラップ関数名(*args, **kwargs):
        funcを用いたなんらかの動作
        return

    return ラップ関数名

■デコレータ実施

@デコレータ名
def デコレータが設定される関数名():

python for文について

for文について

■文字列のカウント

df["CommentLen"]=df["Comment"].str.len()
df

■文字列のカウント

# これはdf['Comment']の長さ=データ数である4が入ってしまう
df['CommentLen'] = len(df['Comment'])
df

■スライスの関数について

15文字までの文字列を抽出

・itertuplesを使う

slice_list=[]
for row in df.itertuples():
    slice_list.append(row.Comment[:15])
df["SlicedComment"]=slice_list

・applyを使う この処理は、一括処理されるため、forで繰り返しで処理するよりも実行速度が上がります。
Series.apply()に引数として実装した関数を渡すと、Seriesの各データが実装した関数の引数として渡された結果をシリーズとして返します。

def get_sliced_str(x):
    return x[:15]
df["SlicedComment"]=df["Comment"].apply(get_sliced_str)
def get_3range_rate(x):
    if x >= 4:
        return '好評'
    elif x == 3:
        return '普通'
    elif x <= 2:
        return '不評'
    df['Rate3range'] = df['Rate'].apply(get_3range_rate)