PythonとPandasを使って、Excel/スプレッドシートの読み書きと、DataFrameの条件検索の練習。メモ

今回おこなうこと

主に次のようなことを学びます

PandasでExcelブックにDataFrameを書き込む方法
PandasでExcelブックを読み込む方法
DataFrameを並び替える方法
簡単な条件に当てはまるデータの抽出方法

このような手順で行います

3枚のシートがあるExcelブックを新規作成する
1. 意味のない値の入ったシート
2. 売上データ的なシート
3. 意味のないシート
2枚目のシート(売上データ的な)をDataFrameに取り込む
- 顧客ごとに並び替える(sort_values())
- 顧客ごとの名前を抽出する
- 顧客ごとに売上データを抽出する
ブックに顧客ごとに分けてシートを追加する

【注意】
間違ったことを書いていることが多々あります。責任を負いかねます。ここに書いているコードを使って重要なデータの入ったExcelブックを直接読み書きせぬよう、くれぐれもご注意下さい。コピーするなり～

今回おこなうこと
- 主に次のようなことを学びます
- このような手順で行います
目次
3枚シートのExcelブックを新規作成する
ブックをシート毎に読み込む
- お客様名でDataFrameを並び替える
お客様名ごとにシート作成とブックへの追加
- お客様名の取得
- お客様名ごとにデータを抽出し、シートを追加する
おわりに

【実行環境】

Android
Termux
Python3.9
外部ライブラリとアプリ
- Pandas、Excel/スプレッドシート

Windows10
Anaconda
JupyterLab
古いExcel

3枚シートのExcelブックを新規作成する

実験用のExcelブックを作成します。中身は適当。
実行すると、Excelファイル「test.xlsx」が作業中のカレントディレクトリに保存されます。
シートを3枚含みます。

# ライブラリのインポート
import pandas as pd
import os

#------------------
# 新規ブック作成関数
#------------------
def make_samplebook():
    fname="test.xlsx"
    dirname=os.getcwd()
    savename = os.path.join(dirname, fname)

    # sheet1 
    data_1 = [[1,2,3],
              [4,5,6],
              [7,8,9]]
    df_1 = pd.DataFrame(data_1)

    # sheet2 
    data_2 = [["日付","お客様名","品名","個数"],
              ["2021/01/05","田中商店","塩","5"],
              ["2021/01/05","田中商店","酒","2"],
              ["2021/01/06","鈴木商店","apple","8"],
              ["2021/01/02","株式会社坂田","peach","3"],
              ["2021/01/20","四つ菱重工業","rocket","1"],
              ["2021/01/09","四つ菱重工業","beer","2"],
              ["2021/01/11","田中商店","tomato","2"]
             ]
    
    df_2 = pd.DataFrame(data_2)

    
    # sheet3 
    data_3 = [["™","®","©"],
              ["¶","√","Π"],
              ["~","<","€"]
             ]
    
    df_3 = pd.DataFrame(data_3)

    
    # ブック書き込み作成 カレントディレクトリにtext.xlsxで保存
    with pd.ExcelWriter(savename, mode="w",engine="openpyxl") as w:
        # sheet1
        df_1.to_excel(w, sheet_name="1")
        # sheet2
        df_2.to_excel(w, sheet_name="2", header=0, index=False)
        # sheet3
        df_3.to_excel(w, sheet_name="3", index=False, header=False)

with文の書き込み箇所で、シート毎にheaderやindexの設定を変えています。違いが分かるかなと。

実験用のExcelブックを作成する関数ができました。実行するとカレントディレクトリに「test.xlsx」なるExcelブックが作成されます。
実行します。

# 実行
make_samplebook()

一応、できたか確認します。

%ls | grep xlsx

test.xlsx

Google スプレッドシートの画像(スマホで確認)

1枚目シート

2枚目のシート

3枚目のシート

ちゃんとシートが3枚出来ています。

ブックをシート毎に読み込む

pd.read_excel("ファイル名.xlsx", sheet_name="シート名")
でシートごとに読み取りDataFrameにして表示させてみます。

#---------------------------
# Excelブック読み込み・表示関数
#---------------------------
def read_samplebook():
    filename = "test.xlsx"
    df_sheet1 = pd.read_excel(filename, sheet_name="1")
    df_sheet2 = pd.read_excel(filename, sheet_name="2", header=0)
    df_sheet3 = pd.read_excel(filename, sheet_name="3")
    
    return df_sheet1, df_sheet2, df_sheet3

関数ができました。実行します。

# 実行
df_sheet1, df_sheet2, df_sheet3 = read_samplebook()
# sheet1 表示
df_sheet1

	Unnamed: 0	0	1	2
0	0	1	2	3
1	1	4	5	6
2	2	7	8	9

# sheet2 表示
df_sheet2

	日付	お客様名	品名	個数
0	2021/01/05	田中商店	塩	5
1	2021/01/05	田中商店	酒	2
2	2021/01/06	鈴木商店	apple	8
3	2021/01/02	株式会社坂田	peach	3
4	2021/01/20	四つ菱重工業	rocket	1
5	2021/01/09	四つ菱重工業	beer	2
6	2021/01/11	田中商店	tomato	2

# sheet3 表示
df_sheet3

	™	®	©
0	¶	√	Π
1	~	<	€

ここからは、適当に売上データ的なものを入れた2枚目シート(df_sheet2)を使って行きます。

お客様名でDataFrameを並び替える

2枚目シートに「お客様名」という項目の列があります。これを基準にしてDataFrameを並び替えてみます。

# シート2 表示
df_sheet2

	日付	お客様名	品名	個数
0	2021/01/05	田中商店	塩	5
1	2021/01/05	田中商店	酒	2
2	2021/01/06	鈴木商店	apple	8
3	2021/01/02	株式会社坂田	peach	3
4	2021/01/20	四つ菱重工業	rocket	1
5	2021/01/09	四つ菱重工業	beer	2
6	2021/01/11	田中商店	tomato	2

#---------------------------------
# 2枚目シート「お客様名」で並び替え関数
#---------------------------------
def sort_okyaku(df, col):
    df_sort_okyaku = df.sort_values(col)
    
    return df_sort_okyaku

実行します。

# 引数
df = df_sheet2 # シート2のdataframe
col = "お客様名"  # 並び替え項目名

# 関数実行
df_sort_okyaku = sort_okyaku(df, col)
# 表示
df_sort_okyaku

	日付	お客様名	品名	個数
4	2021/01/20	四つ菱重工業	rocket	1
5	2021/01/09	四つ菱重工業	beer	2
3	2021/01/02	株式会社坂田	peach	3
0	2021/01/05	田中商店	塩	5
1	2021/01/05	田中商店	酒	2
6	2021/01/11	田中商店	tomato	2
2	2021/01/06	鈴木商店	apple	8

「お客様名」で全体が並び替えられましたが、「日付」は並んでいません。
次は２ついっぺんに日付でも並び替えます。

#----------------------------------
# 2枚目「お客様名」「日付」で並び替え関数
#----------------------------------
def sort_okyaku_hiduke(df, *col):
    df_sorted = df.sort_values(*col)

    return df_sorted

実行します。

# 引数
df = df_sheet2 #シート2のdataframe
col = ["お客様名","日付"]

# 実行
df_sorted = sort_okyaku_hiduke(df, col)
# 表示
df_sorted

	日付	お客様名	品名	個数
5	2021/01/09	四つ菱重工業	beer	2
4	2021/01/20	四つ菱重工業	rocket	1
3	2021/01/02	株式会社坂田	peach	3
0	2021/01/05	田中商店	塩	5
1	2021/01/05	田中商店	酒	2
6	2021/01/11	田中商店	tomato	2
2	2021/01/06	鈴木商店	apple	8

「お客様名」と「日付」に基づいて並び替えられました。

お客様名ごとにシート作成とブックへの追加

お客様名の取得

お客様名から重複しないユニーク値を取得します。
df[項目名].unique()
で、項目名Seriesからユニーク値を取り出せます。

#-----------------------
# 項目名のユニーク値取得関数
#-----------------------
def unique_name(df, col):
    return df['お客様名'].unique()

# 引数
df = df_sorted # お客様名と日付で並び替え済
col = "お客様名" # ユニーク値を取得したい項目
# 実行
name_list =  unique_name(df, col)
# 表示
name_list

array(['四つ菱重工業', '株式会社坂田', '田中商店', '鈴木商店'], dtype=object)

お客様名の一覧が抽出できた。

お客様名ごとにデータを抽出し、シートを追加する

上でお客様名の一覧が取得できたので、それら毎にデータを抽出したい。DataFrameから条件検索するにはdf.query('条件')を使えばできる。
お客様名をfor文で回してquery()関数の条件に嵌め込んで行けばできると思ったが上手く行かなかったので、別の方法で行った。
お客様別のシート作成と追加まで一気に行う。

#-------------------------
# お客様名ごとにシート作成関数
#-------------------------
def write_eachdata(df, name_list, col):
    
    for name in name_list:
        # エラー こちらの方法はここでは使えない
        #sorted_df.query('お客様名 == name')
        each_df = df[df[col]==name]

        # excelブックにシート追加書き込み
        with pd.ExcelWriter("test.xlsx", mode="a",engine="openpyxl") as w:

            # 書き込み
            each_df.to_excel(w, sheet_name=name, index=False)

実行します。

# 引数
df = df_sorted #お客様名と日付で並び替え済
name_list = name_list # ユニークなお客様名一覧
col = "お客様名" #条件検索の為の項目名

# 実行
each_df = write_eachdata(df, name_list, col)
# 表示
each_df

Excelブックを確認します。

お客様名ごとに追加されたシート画像

f:id:chayarokurokuro:20210123112654j:plain