koji/メガネ男の日誌

日々の学び、活動状況を記録します。仕事のことは少なめ。

スプレッドシートの計算をPandasで再現してみた

f:id:kj_man666:20200516141538j:plain

スプレッドシートでやる計算を、軽量化のためにpythonのPandasで実装しようとしたら、思いの他詰まったのでまとめたいと思います。

取り込んだ統計データのcsv等を使ってシミュレーションを組みたいときに活用できるかと思います。

なおサンプルとして、口座から引落と預入をして残高を算定するスプレッドシートを挙げています。

引落し額の上限を3,000円と設定しており、入力した引落し額が3,000円を超えている場合は、自動的に3,000円を引落し額として設定しています。

docs.google.com

pandas化のテクニックとして取り上げているのは、以下の2つになります。

  • 2つのセルを比較して小さい（大きい）数字をピックアップする

    pandasの列（又は）行の中で一番小きい（大きい）値をピックアップする方法はたくさん紹介されているのですが、Aという列の要素とBという列の要素を比較して、小さい（大きい）数値を入力する列を作成する手法を見つけるのに苦労しました。

  • 1つ上の行の計算結果を参照する （前月末の残高を当月頭の残高にするイメージ）

なお、コードをダウンロードする場合はこちら。

import pandas as pd

# 使用する日付、引落入力、預入のDataFrameを作成。
# 実務ではこちらのデータはcsv等で取り込むイメージです
df1 = pd.DataFrame({
                "date":["4/1", "4/8", "4/15", "4/22", "4/29", "5/6", "5/13", "5/20", "5/27", "6/3"],
                "withd_inp":[2000, 0, 4000, 0, 1500,3500,0,6000,0,4000],
                "deposit":[0,3000,0,0,2000,0,0,0,8000,0]
                  })

色々調べたのですが、for文を使う以外に変数とpandasの要素を比較して最小値、最大値を抽出する方法が見つからなかったので、引出制限 3,000円をpandasの要素として設定しています。

見た目を気にする場合は、最後に削除するとOK。

# 引落入力と比較するための"引出制限 3,000円" をlimitカラムに入力
# 次にdf の要素に min関数を適用するためにカラムに入力している 
df1["limit"] = 3000

# 要素同士を比較して小さい方を実際の引落に入力
df1["withd_act"] = df1[["withd_inp", "limit"]].min(axis=1)

# for文を適用するために 今週頭の残高と、今週終の残高のカラムを作成する
# None を入力する必然性はないが、数字と区別するために None を入力しておく
df1["accnt_head"] = None
df1["accnt_tail"] = None

出力結果はこうなります。

f:id:kj_man666:20200516114952p:plain

# 今週頭の残高と、今週終の残高の作成
for i in range(len(df1)):
    if i == 0:
        df1["accnt_head"][0] = 3000
    else:
        df1["accnt_head"][i] = df1["accnt_tail"][i-1]        
    df1["accnt_tail"][i] = df1["accnt_head"][i] - df1["withd_act"][i] + df1["deposit"][i]

以下のようにエラーが出てしまいますが、一応計算されているようです。

C:\path\lib\site-packages\ipykernel_launcher.py:4: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy after removing the cwd from sys.path.

SettingWithCopyWarningの参考サイト note.nkmk.me

f:id:kj_man666:20200516115018p:plain

スプレッドシートと並びが違いますので、気になる方は以下の処理をご参考ください。

# 順番が気になるので並び替え
df2 = pd.DataFrame(df1["date"])
df2 = pd.concat([df2, df1.iloc[:,-2]], axis=1)
df2 = pd.concat([df2, df1.iloc[:,1]], axis=1)
df2 = pd.concat([df2, df1.iloc[:,4]], axis=1)
df2 = pd.concat([df2, df1.iloc[:,2]], axis=1)
df2 = pd.concat([df2, df1.iloc[:,-1]], axis=1)

f:id:kj_man666:20200516115100p:plain

また、スプレッドシートでは使用していないのでgithub中のコードでは省略しましたが、pandasの要素に自作の関数等を使うテクニックもご紹介します。

# pandasの要素に、自作の関数を適用したい場合
def 自作の関数:
     設定したい処理

df['X'] = df['A'].map(自作の関数)

# pandasの要素に、無名関数 lambda を適用したい場合
df["X"] = df["A"].map(lambda x : 設定したい処理 )

# 単純にmax()、min()関数を設定するだけなら関数を作成する必要はありませんが、要素を計算した後の数値に対してmax()、min()を設定したい場合には、以下のような方法も使えます。
def 関数名(row): #rowはDataFrame型にあたります
    return max((row['A'] - row['B'])*0.9, 0) 

df['X'] = df.apply(関数名, axis=1)

以上になります、最後までお読みいただきありがとうございました。

参考サイト

note.nkmk.me

note.nkmk.me