Python Excelセルの行列範囲を自動判別して抽出する「openpyxl」

　本記事では、下図のようにエクセルファイル中の先頭行ではなくて、中途半端な位置に表データ（行列範囲）がある場合に、特に適用します。この行列範囲を自動で判別して、抽出してpandasデータフレーム形式へ読み込む雛形コードを記載しました。

　その仕組みは次の通りです。まず、先頭行はA列に対して「水準No」というキーワードで検索して、その行番号を取得します。これが表データの開始行になります。次に、最終列は、「水準No」の行に対して右側から検索して空白文字でなくなった列番号を取得します。これが表データの最終列になります。最後に、単にシート内の最終行は、「ws.max_row」で取得できます。これが表データの最終行になります。以上により、表データの行数や列数が決まってない場合でもデータを抽出することが出来ます。

■本プログラムの仕様を説明します。
上図のようなエクセルファイルがいくつかあります（下図）。

これら各々から、冒頭で述べた行列範囲の表データを自動抽出して、下図のようにひとつのcsvファイルへスタックデータにします
（備考）本例題では、スタックデータにするために全てのファイルの表データの列数が同じである必要があります。もし、処理したいエクセルファイルがひとつのみの場合には問題ありません。

そして、カテゴリ変数をラベルとして下図のように散布図を作成します。

■本プログラム

#!/usr/bin/env python
# coding: utf-8

# In[1]:


import os
import glob
import re
import openpyxl
import pandas as pd
import numpy as np

# エクセルファイルをリストで読み込む
filepath_list = glob.glob(os.getcwd() + '/excel_files/*.xlsx')
filepath_list


# In[2]:


# フォルダパスからファイル名を抽出する（ファイル名でソートするため）
filename_list = []
dir_path = ''
for i, mypath in enumerate(filepath_list):
    mypath = str(mypath)
    mydir_path, file_name = os.path.split(mypath)
    filename_list.append(file_name)
    if i == 0:
        dir_path = mydir_path

# ファイル名順にソートする
filename_list_sorted = sorted(filename_list, key=lambda x:int((re.search(r"[0-9]+", x)).group(0)))
print(filename_list_sorted)

# ファイル名順にファイルパスリストを作成する
new_filepath_list = []
for myfilename in filename_list_sorted:
    mypath = os.path.join(dir_path, myfilename)
    new_filepath_list.append(mypath)
new_filepath_list


# In[3]:

# エクセルのセル行列範囲の表データを抽出する
df_list = []
for k, filepath in enumerate(new_filepath_list, start=1):
    # エクセルファイルのインスタンス生成（オブジェクト）
    # 引数data_only=Trueで、セル内が数式の場合であっても数値で取得する
    wb = openpyxl.load_workbook(filepath, data_only=True)
    
    # ワークシートを読み込む
    ws = wb.worksheets[0]
    # ws = wb.get_sheet_by_name('シート名')
    
    # 行番号を検索により取得する
    start_row = ''
    for i, row in enumerate(ws.iter_rows(min_row=1), start=1):
        # A列（pythonでは0番目）を対象に、キーワード検索して、その行番号を取得する
        if row[0].value == '水準No': # A列で上から検索して、最小にxを見つけた時
            start_row = i
            break # forループを抜ける
    
    # エクセルシート内にある最終行を取得する
    end_row = ws.max_row
    #print('start_row, end_row', start_row, end_row)
    
    # 指定行の最終列の列番号を取得
    end_col = ''
    for j in reversed(range(1, ws.max_column)):
        if ws.cell(row=start_row, column=j).value != None:
            end_col = j
            #print('end_col', end_col)
            break
    
    # 表範囲を配列で取得する
    data_rows = []
    for rows in ws.iter_rows(min_row = start_row, min_col = 1,
                             max_row = end_row, max_col = end_col+1):
        data_cols = []
        for cell in rows:
            data_cols.append(cell.value)
        data_rows.append(data_cols)
    
    # pandasデータフレーム形式へ変換
    df = pd.DataFrame(data_rows)
    #print(df)
    
    # 1行目を取得
    column_names = df.iloc[0]
    
    # 列名を置換する。そして、元の1行目は削除
    df.columns = column_names
    df2 = df.drop(0)
    #print(df2)
    
    # カウント数をカテゴリ変数として入れる
    my_label = 'category'
    df2[my_label] = k
    #print(df2)
    
    # dfをリストへ格納
    df_list.append(df2)
    
DF = pd.concat(df_list)
DF.to_csv('df_con.csv', index=False, encoding='cp932')
DF


# In[11]:

# カテゴリ別の散布図を作成する
import matplotlib.pyplot as plt
import japanize_matplotlib
import matplotlib.cm as cm
plt.rcParams['font.size'] = 16 # グラフの基本フォントサイズの設定

my_columns = ("x", "積")

# ひとつの散布図にカテゴリ別にプロットする
colors = cm.rainbow(np.linspace(0, 1, len(DF[my_label].unique())))
for i, p in enumerate(DF[my_label].unique()):
    plt.scatter(DF.loc[DF[my_label] == p, my_columns[0]],
                DF.loc[DF[my_label] == p, my_columns[1]],
                color = colors[i],
                label = 'label ' + str(p),
               )
plt.grid(which='both')
plt.title('test')
plt.xlabel(my_columns[0])
plt.ylabel(my_columns[1])
plt.legend(bbox_to_anchor=(1, 0.95)) # 凡例の位置
plt.tick_params()
plt.savefig("graph.jpg")


# In[ ]:

以上

<広告>

リンク