【まとめ】PythonでExcelの関数を使用する ※随時更新

PythonでExcelの関数を使用する Python

前提 ※対象読者:コードの理解ではなくとりあえず使いたい人

  • 環境:Google Colaboratory
  • OS:Mac
  • df という変数にデータを代入(以下ソースコード)※datetime, pandas, mathライブラリ、モジュールのimport
import datetime
import pandas as pd
import math
df = pd.read_excel("sampleデータベース.xlsx")
  • 使用データ(データサイズ:1000 ✖︎ 10)※以下、上から5行だけを表示(シート名:”元データ”)

※データを手元に置きたい場合は以下からダウンロードをお願いしますhttps://docs.google.com/spreadsheets/d/1VL6U2NsykiHfCcTzO7NgKaU9N2qGEXkF/edit?usp=sharing&ouid=111634202063271684226&rtpof=true&sd=true

※再現しようとして逆にゴチャゴチャになっているのもあることから、pythonでそんなことしないよ…って思われるものは許容していただけると幸いです(筆者曰く、こんなやり方なら別の方法使うわ…ってなったものもあります)

見方(”結果”の下の箇所で値が一致しているかの確認)

Excel
Python
左記にあるExcelの関数と同様の結果を出力するpythonのコード

※コメントがあれば随時追加します

sum(合計)

利用金額の合計値“の出力

Excel
Python
df["利用金額"].sum(axis=0)

※補足(axis=0とaxis=1の向き)

sumif(条件付き合計)

40歳以上の利用金額の合計値“の出力

Excel
Python
df["利用金額"][df["年齢"] >= 40].sum(axis=0)

sumifs(条件付き合計)※複数条件

A型で40歳以上の利用金額の合計値“の出力(and)

Excel
Python
df["利用金額"][(df["血液型"] == "A型") & (df["年齢"] >= 40)].sum(axis=0)

average(平均)

利用金額の平均値“の出力

Excel
Python
df["利用金額"].mean(axis=0)

averageif(条件付き平均)

40歳以上の利用金額の平均値“の出力

Excel
Python
df["利用金額"][df["年齢"] >= 40].mean(axis=0)

averageifs(条件付き平均)※複数条件

A型で40歳以上の利用金額の平均値“の出力(and)

Excel
Python
df["利用金額"][(df["血液型"] == "A型") & (df["年齢"] >= 40)].mean(axis=0)

max(最大値)/ min(最小値)

利用金額の最大値“と”利用金額の最小値“の出力

Excel
Python
# 最大値 (max)
df["利用金額"].max(axis=0)
# 最小値 (min)
df["利用金額"].min(axis=0)

count(個数、カウント)

データの個数“の出力
※注意:ExcelのCOUNT関数は数値データのみしか数えないことから、例えば血液型をA列に設定してCOUNT(A2:A1001) とした場合、結果は0になる。対してPythonのコードは、文字や数字に関係なく個数を数えていることから、そこに違いがある。

Excel
Python
df["利用金額"].count()

countif(条件付き個数、条件付きカウント)

利用金額40000円以上のデータの個数“の出力

Excel
Python
df["利用金額"][df["利用金額"] >= 40000].count()

countifs(条件付き個数、条件付きカウント)※複数条件

40歳以上で利用金額40000円以上のデータの個数“の出力(and)

Excel
Python
df["利用金額"][(df["年齢"] >= 40) & (df["利用金額"] >= 40000)].count()

if(条件分岐)※条件1つ

利用金額が40,000円以上なら優良顧客」“と出力(そうでなければ”非優良顧客”)

Excel
Python
df["利用金額"].apply(lambda x : "優良顧客" if x >= 40000 else "非優良顧客")

※上記結果を変数に入れたり、そのまま元のデータと結合したりする

not関数

出身地が大阪府以外なら「県外の顧客」“と出力(そうでなければ”県内の顧客”)

Excel
Python
df["出身地"].apply(lambda x : "県外の顧客" if not x == "大阪府" else "県内の顧客")

if(条件分岐)※複数条件(2つ)

利用金額が80,000円以上なら「超優良顧客」、40,000円以上79,999円以下なら「優良顧客」“と出力(そうでなければ「非優良顧客」)

Excel
Python
def func1(money):
    if money >= 80000:
        return "超優良顧客"
    elif money >= 40000:
        return "優良顧客"
    else:
        return "非優良顧客"

df.apply(lambda df: func1(df["利用金額"]), axis=1)

※関数を作成してそれを適用

and関数

利用金額が80,000円以上で購買場所がネット店舗なら「業者“と出力(そうでなければ「それ以外」)

Excel
Python
def func2(money, place):
    if money >= 80000 and place == "ネット店舗":
        return "業者"
    else:
        return "それ以外"

df.apply(lambda df: func2(df["利用金額"], df["購買場所"]), axis=1)

or関数

利用金額が40,000円以上、もしくは購買場所がネット店舗なら「力を入れたい顧客」“と出力(そうでなければ「それ以外」)

Excel
Python
def func3(money, place):
    if money >= 40000 or place == "ネット店舗":
        return "力を入れたい顧客"
    else:
        return "それ以外"

df.apply(lambda df: func3(df["利用金額"], df["購買場所"]), axis=1)

round(四捨五入)/roundup(切り上げ)/rounddown(切り捨て)

直近購買日(ヶ月)を小数点第一位で四捨五入、小数点第一位で切り上げ、小数点第一位で切り下げた値“の出力
※注意:pythonの方は行方向の全てのデータにROUND3つを適用しているExcelと同様のことをしているため、通常の「math.ceil(3.14) → 4」のようなことはしていない。また、pythonのmath.ceilとmath.floorでは小数点第○位で切り上げ、切り捨てを行うという処理ができない

Excel
Python
# 四捨五入 (round) ※小数点第一位
round(df["直近購買日(ヶ月)"], 0)
# 切り上げ (math.ceil)
df.apply(lambda df: math.ceil(df["直近購買日(ヶ月)"]), axis=1)
# 切り捨て (math.floor)
df.apply(lambda df: math.floor(df["直近購買日(ヶ月)"]), axis=1)

int(整数、小数点以下の切り捨て)

直近購買日(ヶ月)を元の数値より小さい数値に切り捨てた値“の出力

Excel
Python
df.apply(lambda df: int(df["直近購買日(ヶ月)"]), axis=1)

vlookup(検索と一致)

※hlookupやxlookupをpythonで行うことは比較的困難なので触れません(ご要望がありましたら記載します)

vlookup用のデータを別に用意

※別シートにデータを用意(データサイズ:10000 ✖︎ 2)※以下、上から10行だけを表示(シート名:”元データ2″)

df2 という変数にデータを代入(以下ソースコード)

df2 = pd.read_excel("sampleデータベース.xlsx", sheet_name="元データ2")

ユーザーIDを基準に名前を検索、一致させる

Excel
Python
df2.merge(df[["ユーザーID", "名前"]], on="ユーザーID", how="left")

index(”指定した行、列番号”のデータの出力)

3行2列目の“の出力

Excel
Python
df.iloc[2, 1]

※注意:Excelでは (3, 2) なのに対してpythonでは (2, 1) となっていることについて、次のmatchも同じだが、これは互いのインデックスの初期値が異なることが原因(Excelは1から始まり、pythonは0から始まる)

match(”指定したデータの”行番号、列番号の出力)

赤松 りえ はB列(名前の列)の何行目の値か、3行目の何列の値か“の出力

Excel
Python
# 指定したデータの行番号の出力
df.set_index("名前").index.get_loc("赤松 りえ")
# 指定したデータの列番号の出力
df[df["名前"] == "赤松 りえ"].columns.get_loc("名前")

※index関数のところの”注意”と同じ

row(行番号の出力)/column(列番号の出力)

赤松 りえの行番号と列番号“の出力

Excel
Python
search = "赤松 りえ"
for i in range(len(df)):
    for j in range(len(df.columns)):
        if df.iloc[i, j] == search:
            print(f"{i}行{j}列")
            break

※注意:pythonではindex番号がheaderからではなくデータがあるところから振られていること(以下の画像の赤枠部分を参照)と、カラムが0から振られているということの2点に注意

mid(文字列の抽出)/right(〃)/left(〃)

携帯電話番号の左から3つ、真ん中4つ、右から4つ“の出力

Excel
Python
# 左から3つを抽出 (left)
df["携帯"].apply(lambda x : x[:3])
# 真ん中4つを抽出 (mid)
df["携帯"].apply(lambda x : x[4:8])
# 右から4つを抽出 (right)
df["携帯"].apply(lambda x : x[9:])

find(文字位置の抽出)

メールアドレスの@が左から数えて何文字目にくるか“の出力

Excel
Python
df["メールアドレス"].apply(lambda x : x.find("@"))

※index関数のところの”注意”と同じ

year(年の出力)/month(月の〃)/day(日の〃)/minute(秒の〃)/today(今日の日付の出力)/now(今日の日付と時刻の出力)

誕生日の年、月、日、“と”今日の年月日と今日の年月日と今の時間“の出力

Excel
Python
# 年 (year)
df["誕生日"] = pd.to_datetime(df["誕生日"], errors='coerce')
df["誕生日"].apply(lambda x : x.year)
# 月 (month)
df["誕生日"] = pd.to_datetime(df["誕生日"], errors='coerce')
df["誕生日"].apply(lambda x : x.month)
# 日 (day)
df["誕生日"] = pd.to_datetime(df["誕生日"], errors='coerce')
df["誕生日"].apply(lambda x : x.day)
# 時 (hour)
df["誕生日"] = pd.to_datetime(df["誕生日"], errors='coerce')
df["誕生日"].apply(lambda x : x.hour)
# 分 (minute)
df["誕生日"] = pd.to_datetime(df["誕生日"], errors='coerce')
df["誕生日"].apply(lambda x : x.minute)
# 秒 (second)
df["誕生日"] = pd.to_datetime(df["誕生日"], errors='coerce')
df["誕生日"].apply(lambda x : x.second)
# 今日の年月日
datetime.date.today()
# 今日の年月日と時刻
datetime.datetime.now()

weekday(曜日の出力)※数字で返す場合

月曜日を0、火曜日を1、水曜日を2、…、日曜日を6とした際の誕生日の曜日“の出力

Excel
Python
df["誕生日"] = pd.to_datetime(df["誕生日"], errors='coerce')
df["誕生日"].apply(lambda x : x.weekday())

text(曜日の出力)※文字で返す場合

誕生日の曜日を「○曜日」形式“で出力

Excel
Python
def func4(dt):
    w_list = ['月曜日', '火曜日', '水曜日', '木曜日', '金曜日', '土曜日', '日曜日']
    return(w_list[dt.weekday()])

df.apply(lambda df: func4(df["誕生日"]), axis=1)

concatenate(文字列の結合)、textjoin(〃)

出身地と購買場所を連結したもの、性別、血液型、年齢を連結したもの“の出力

Excel
Python
# 2文字連結
df["出身地"] + "-" + df["購買場所"]
# 3文字連結
df["性別"].str.cat([df["血液型"], df["年齢"].astype(str)], sep="-")

※年齢は数値型なので、文字列型と連結するためにstr型に変更している

タイトルとURLをコピーしました