Python
備忘録
自分用メモ
Jupyter

excelからpythonへ移行するためのメモ

備忘録メモ

私は非エンジニアで、会社では企画などをやっています。
普段の業務ではエクセルメインでたまにAccessといったところですが
業務効率化とクオリティ向上のためにpythonを活用したデータ分析ができるようになりたいと考え勉強中です。

自分的にちょくちょく触るときに思い出しに使えるように簡単にサマリます

※将来的にexcelにpythonが組み込まれる未来も見据えて、excel,accessとの違いを意識してメモを残します
ExcelにPythonが搭載?

まずはインストール

前提としてpython/jupyterのインストールが必要
※私はanacondaを利用

pythonで実施する工程を決める

すべてpythonでやるのではなく、pythonが得意なところを置き換える。
下記の処理がpythonによる威力を発揮する。

  • データ処理
  • 初期分析(EDA)
  • グラフ描画

逆にpythonで実施しないこと

  • アウトプットデータの保存
    • 分析し終わったらアウトプットに使ったdataframeをCSVに吐き出す
  • 最終的にアウトプットする表組みの作成
    • 表の表現、見せ方はエクセルの方が優れている
  • マスタ作成/マスタ管理
    • データ処理用のマスタはエクセルで作成/持っていたほうが応用が効く、管理がし易い

excel/accessと比較した際のpythonを使うメリット/デメリット

メリット

  • データ→グラフ化が容易で、グラフを見ながら分析できる
    • Accessだと一度excelへ吐き出してからグラフ描画する必要があり、手間だしファイルが増える。
    • Excelも同様でどの表とグラフが一致しているかわかりにくいことも多々。
  • どのようにデータ処理したかが残るため、後で処理を追いやすい
    • 仕事も型化しやすい
  • ファイル数が少なくて済む/管理が楽
    • Access/Excelだと多くのファイル、シートが必要になるケースがあり、その管理が大変。
  • データ処理が早い
    • (Accessよりも早い、、、気がする。ただ企画職の場合はせいぜい数百万行くらいのデータ処理しかしないので大きく業務効率化はしないかなと。)

デメリット

  • 仕事が引き継げない/理解してもらえない
    • 周りにpythonでデータ処理しようなんて人は少ないため、仕事が引き継げない
  • ちょっとした作業はexcelの方が断然早い
  • 欠損値の補完や名寄せはエクセル/目検の方が早いケースあり
    • データを見ながらよしなに補完する必要があるとき/社名や商品名などの目検が必要なケースは、行数にもよるが目検の方が早いケースあり

excel⇔pythonのデータやり取り

excel/csvからの取り込み/出力

取り込み

import pandas as pd

#Excelファイルを読み込む 
df = pd.read_excel('sample.xlsx')

#シート名を指定する
df = pd.read_excel('sample.xlsx', sheetname='sample1')

#行を指定する
df = pd.read_excel('sample.xlsx', sheetname='sample1', skiprows=5, skip_footer=5)

#列を指定する
df = pd.read_excel('sample.xlsx', sheetname='sample1', parse_cols="A:C, E:H")  #A,B,C,E,F,G,H列を取り込む

# csvファイルの読み込み
df = pd.read_csv('sample.csv')

# csvファイルの読み込みで日本語が混じっているとエラー(Initializing from file failed)になることがある
# 下記のように追記するとうまくいったのでメモ
df = pd.read_csv('あいうえお.csv', engine='python')
df = pd.read_csv('あいうえお.csv', engine='cp932') #cp932のときもある

#日本語が文字ばけするときは以下を追加
df = pd.read_csv('あいうえお.csv', engine='python', encoding="SHIFT-JIS")


Python3.6のpandasで「Initializing from file failed」が起きた場合の対策

書き込み

import pandas as pd

# Excelファイルに書き込む
df.to_excel("sample2.xlsx")

取り込んだデータの整形

まずは例示するためにデータフレームを作成します

from pandas import DataFrame

df = DataFrame([['東京','男性','10代','201801',10000],
                    ['東京','男性','20代','201801',20000],
                   ['東京','女性','10代','201801',30000],
                   ['東京','女性','20代','201801',40000],
                   ['神奈川','男性','10代','201801',20000],
                    ['神奈川','男性','20代','201801',20000],
                   ['神奈川','女性','10代','201801',60000],
                   ['神奈川','女性','20代','201801',40000],
                   ['千葉','男性','10代','201801',5000],
                    ['千葉','男性','20代','201801',2000],
                   ['千葉','女性','10代','201801',8000],
                   ['千葉','女性','20代','201801',4000]],
                  columns = ['都道府県','性別','年齢','年月','人口'])
----------------------------------------------
#   都道府県    性別  年齢  年月  人口
#0  東京  男性  10代   201801  10000
#1  東京  男性  20代   201801  20000
#2  東京  女性  10代   201801  30000
#3  東京  女性  20代   201801  40000
#4  神奈川   男性  10代   201801  20000
#5  神奈川   男性  20代   201801  20000
#6  神奈川   女性  10代   201801  60000
#7  神奈川   女性  20代   201801  40000
#8  千葉  男性  10代   201801  5000
#9  千葉  男性  20代   201801  2000
#10 千葉  女性  10代   201801  8000
#11 千葉  女性  20代   201801  4000

行の削除

#都道府県カラムの”東京”というデータのみ削除する
df = df[df["都道府県"] != '東京']  #東京というデータのみを選択して代入する

----------------------------------------------
#   都道府県    性別  年齢  年月  人口
#4  神奈川   男性  10代   201801  20000
#5  神奈川   男性  20代   201801  20000
#6  神奈川   女性  10代   201801  60000
#7  神奈川   女性  20代   201801  40000
#8  千葉  男性  10代   201801  5000
#///省略

指定したデータの上書き

データの指定 → loc[行条件,列条件]

df.loc[(df["都道府県"] == "東京")&(df['性別'] =='男性'),'人口'] = 50000
#都道府県カラムが「東京」 かつ 性別カラムが「男性」 の 人口に50,000を代入する

----------------------------------------------
#   都道府県    性別  年齢  年月  人口
#0  東京  男性  10代   201801  50000
#1  東京  男性  20代   201801  50000
#2  東京  女性  10代   201801  30000
#3  東京  女性  20代   201801  40000
#4  神奈川   男性  10代   201801  20000
#///省略

文字列の変換

#”東京”というデータを”TOKYO”へ変換する
df = df.replace('東京', 'TOKYO')

----------------
#   都道府県    性別  年齢  年月  人口
#0  TOKYO   男性  10代   201801  10000
#1  TOKYO   男性  20代   201801  20000
#2  TOKYO   女性  10代   201801  30000
#3  TOKYO   女性  20代   201801  40000
#4  神奈川   男性  10代   201801  20000
#5  神奈川   男性  20代   201801  20000
#///省略

型変換

#astypeに辞書型で変換するカラムとデータ型を渡す
df = df.astype({'人口':float})
df.info()
----------------------
#都道府県    12 non-null object
#性別      12 non-null object
#年齢      12 non-null object
#年月      12 non-null object
#人口      12 non-null float64

#データフレームまるごと変換することもできる
df = df.astype(str)
df.info()
----------------------
#都道府県    12 non-null object
#性別      12 non-null object
#年齢      12 non-null object
#年月      12 non-null object
#人口      12 non-null object

重複の削除

#データ全体の重複チェック
df.duplicated().any()
# False

#特定のカラムの重複チェック
df.duplicated(['都道府県', '性別']).any()
# True

#データを削除するときはdrop_duplicates
df.drop_duplicates(['都道府県', '性別'], keep=last)
#デフォルトは前のデータ残しで最期のデータが消される
#keep=lastを入れると、最期のデータが残されて、前が消される

DataFrame や Series の重複データをチェック・削除-python
【Python】list, pandas.Seriesの重複を削除する

文字列の分割

df["sample"].split("a")

Python, splitでカンマ区切り文字列を分割、空白を削除しリスト化

列名の振り直し

df = df.rename(columns={'都道府県': 'PREF', '性別': 'SEX'})
#もしくは
df.rename(columns={'都道府県': 'PREF', '性別': 'SEX'},inplace=True)

pandas.DataFrameの行名・列名の変更

インデックスの振り直し

df = df.reset_index(drop=True) 

列の順番の振り直し

df = df.ix[:,['都道府県','性別', "人口",'年月']]

こういう列の順番の振り直しはexcelとかだとスームズにできないので
よいかも。

excelだったらこうできるのに、、、

excelでの処理・やり方をpythonでやる場合の逆引き

文字列の検索

df[df['都道府県'].str.contains('東',na=False)] #na=Falseをつけないと、nan値を含む場合、エラーとなってしまうため注意
------------------
#   都道府県    性別  年齢  年月  人口
#0  東京  男性  10代   201801  10000
#1  東京  男性  20代   201801  20000
#2  東京  女性  10代   201801  30000
#3  東京  女性  20代   201801  40000

データフレーム文字列検索

vlookup(マスタの反映)

辞書型からmapでやる方法

dic = {'東京' : '03', '神奈川' : '045', '千葉' : '043'}
df['市外局番'] = df['都道府県'].map(dic)

dataframe同士をmergeする方法

#df側のカラムの「都道府県」とdf2側のカラムの「とどうふけん」をvlookupのように当てる
df = pd.merge(df, df2 , how="inner", left_on="都道府県", right_on="とどうふけん")

mergeだとデータフレームの全カラムが結合されてしまうので、
vlookupのように追加する列を限定したい場合は、一度マージ用に結合用のカラムだけのデータフレームを一時的に作成するのが現時点の個人的な解。

Pandas でデータフレームの結合 (マージ, JOIN)
Python pandas 図でみる データ連結 / 結合処理)

ピボットテーブル

df_pivot = df.pivot_table(values = ['人口'],  #値
                 index = ['都道府県'],        #行
                 columns = ['年月'],            #列・カラム
                  aggfunc = 'sum',fill_value = 0, margins = True)
#aggfuncは集計方法 sum, count, mean, max, min などが取れる
#fill_valueはnull値に当てる数字を指定できる
#margins = Trueとすると小計を表示できる

----------------
#           人口
#年月         201801          All
#都道府県       
#TOKYO          100000.0    100000.0
#千葉            19000.0      19000.0
#神奈川          140000.0    140000.0
#All            259000.0    259000.0

DataFrame から ピボットテーブルを作成-Python pandas
Python pandas でのグルーピング/集約/変換処理まとめ

ピボットテーブル2(クロスタブ)

#クロスタブの基本
pd.crosstab(df['都道府県'], df['性別'])
---------------
#性別 女性  男性
#都道府県       
#千葉 2   2
#東京 2   2
#神奈川  2   2

#クロスタブて、列を複数指定したり、集計方法を変えることができる
pd.crosstab(df['都道府県'], #行の指定
      [df['性別'],df['年齢']], #列の指定 今回は2カラム
           values=df['人口'], #値・集計対象
           aggfunc='sum', #集計方法
           margins=True #小計の表示)
--------------------
#性別 女性  男性  All
#年齢 10代   20代   10代   20代   
#都道府県                   
#千葉 8000.0  4000.0  5000.0  2000.0  19000.0
#東京 30000.0 40000.0 10000.0 20000.0 100000.0
#神奈川  60000.0 40000.0 20000.0 20000.0 140000.0
#All    98000.0 84000.0 35000.0 42000.0 259000.0

#さらにクロスタブで割合を求め、色味をつけて概要をつかむ
pd.crosstab(df['都道府県'],
           [df['性別'],df['年齢']],
           values=df['人口'],
           aggfunc='sum').apply(lambda r:r/r.sum(),axis=1).style.background_gradient(cmap='summer', axis=1)

#apply(lambda r : r/r.sum()) で値を合計から割り算し、割合を計算
#axis=1 で横方向(行方向)の合計値に対して割合を計算
#style.background_gradient(cmap='summer', axis=1)で表に色付をする。cmapはカラーマップを選択、axis=1は横方向の合計値に対してgradientの色味をつけている

image.png

sumifs/countifs

# 都道府県・性別でグループ化して合計
df.groupby(['都道府県','性別']).sum()

#as_index=false でインデックス化しないでデータフレームにできる
df.groupby(['都道府県','性別'],as_index=false).sum()

excelだとできないがpythonだとスピーディにできること

ラベリング(ビニング/Binning)

データの値に応じて、分類を設定できる。
すなわち、[10,23,34,27,34,43,30,53]みたいなデータを
[10代、20代、30代、40代、50代]みたいに分けることができる

#cutは基準にそって分類する
df['Age_Range']=pd.qcut(df['Age'],[0,10,20,30,40,50,999],labels=['10歳未満','10代','20代','30代','40代','50代以上'])

#qcutは値の大きさ順にn等分する
df['Age_Range']=pd.qcut(df['Age'],7)

pandas の cut、qcut でデータ解析-python

縦持ち横持ち変換

for文をの記述で比較的かんたんに縦持ち・横持ちを変換できる
横持ちデータが意外と多く、ピボットテーブルでもその扱いはめんどくさい。

横持ち

City 2000 2001 2002 2003
Tokyo 1 2 3 4
Osaka 5 6 7 8
Nagoya 9 10 11 12


縦持ち

City Year Value
Tokyo 2000 1
Tokyo 2001 2
Tokyo 2002 3
Tokyo 2003 4
Osaka 2000 5
Osaka 2001 6

※データは以下省略

Excel ファイルをPythonで読み込んで整理されたデータフレームにする

個人的メモ

・groupbyのときのas_index=Falseはよく使う
・リネームやデータ加工のときの inplace=Trueもよく使う
・データを確認・検索するためのdf[(df['A']=='XXX')&(df['B']==XXX)&(df['C']=='XXX')]というのもよくつかう
・日付型変換 datetime.datetime.strptime(x, '%Y%m%d')もよく使う

列の中身に対して関数を適用したいときはmap関数

map(str, list)

(参考)

Python(Pandas/XlsxWriter)を使ってExcelファイルを操作してみる②

【Python】pandasでExcelを読み込む
Python で Excel ファイルを読み込む (xlrd)