EXCELでの重複データ処理結果の書き出し

投稿者: | 2021年5月27日

前の2回の記事ではデータの重複やマージ(和集合)について説明してきました。
今回はその結果をエクセルファイル内に書き出す処理について説明します。いったんこの処理を作ってしまえば毎回データの重複除去やマージなどの作業が一瞬でできるようになり作業効率があがるかと思います。

前回の振り返り

前回はA列、B列にデータが入力されたエクセルファイルの処理を行いました。処理対象となるエクセルファイルは以下です。

これを以下の処理により①A列のみ、②B列のみ、③A列B列のAND、④A列B列の和集合を取得しました。コードのみ掲載します。解説は前回記事をご参照ください。

# openpyxl がインストールされていない方は以下を実行(コメントアウトを外す)
# pip install openpyxl

import openpyxl

# エクセルファイルのパス(置き場所)を指定します。自身の環境に合わせてください。
workbook = openpyxl.load_workbook(r'C:\python_ip\20210522\pubnumber_list.xlsx')

# 先頭のシートを取得
worksheet = workbook.worksheets[0]

def get_coldata(col):
    # 入力された最終行を取得
    max_row =worksheet.max_row

    # データのセルを取得
    cells = worksheet[col+'1':col+str(max_row)]
    
    # 関数の返り値のリストを用意
    data = []

    for c in cells:
        if c[0].value is not None: #データの存在をチェック None(空白)でない
            data.append(c[0].value) #セルの値を順次追加
    
    return data
## ここまでが関数 get_coldata


# 上記の関数でデータを取得。取得後タイトル行を除外する
listA = get_coldata('A')[1:]
listB = get_coldata('B')[1:]

setA = set(listA)
setB = set(listB)

# 以下で各演算結果を取得
diffA = list(setA - setB) #Aのみ
diffB = list(setB - setA) #Bのみ
unionAB = list(setA | setB) # A or B 
interAB = list(setA & setB) # A and B

データのセルへの書き出し

上記のプログラムで得られた値を各列に書き出します。
プログラムの記載ミスなどで元データを上書きする可能性があるので別シート’result’を作成しそのA列~D列に書き出すようにします。もちろん同じシートの別の個所に指定して書き出すこともできます。

列とデータの対応は、diffA – A列、diffB – B列、unionAB – C列、interAB – D列 となるように書き出します。得られたリストをループによって処理し、1行ずつ書き出していきます。

先に挙げたpythonのコードの続きを以下のように記載します。なお、処理対象のエクセルファイルは閉じておかないとエラーになりますのでご注意ください。

workbook.active

# 'result'という名称のシートを新規に作成
result_sheet = workbook.create_sheet(title='result')

# タイトル行の入力
result_sheet.cell(row=1, column=1).value = 'Aのみ'
# リストでループしながらセルに値を代入する
for i in range(1,len(diffA)):
    result_sheet.cell(row=i+1, column=1).value = diffA[i]
    
result_sheet.cell(row=1, column=2).value = 'Bのみ'
for i in range(1,len(diffB)):
    result_sheet.cell(row=i+1, column=2).value = diffB[i]
    
result_sheet.cell(row=1, column=3).value = 'A or B'
for i in range(1,len(unionAB)):
    result_sheet.cell(row=i+1, column=3).value = unionAB[i]
    
result_sheet.cell(row=1, column=4).value = 'A and B'
for i in range(1,len(interAB)):
    result_sheet.cell(row=i+1, column=4).value = interAB[i]

workbook.save(r'C:\python_ip\20210522\pubnumber_list.xlsx')

ここで注意したいのはリストのインデックスとセルのrow指定の番号との差異です。python上のリストは先頭からインデックスが0によって指定されるのですが、エクセルのセルは開始が1になっているために差異が生じます。
したがって、演算結果のリストをインデックス変数のiによってループさせつつデータにアクセスしていますが、エクセルのセルのrow指定はi+1になっています。

この処理により新たに生成された’result’という名称のシートに各演算の結果が入力されてエクセルファイルが更新されます。処理の結果は以下のようになります。

重複する処理を整理する

上記のプログラムで目的は達成されましたが、コードの記載がやや冗長な感じがします。
同じ処理を列ごとに同じ処理を繰り返し記載しているので、整理してすっきりした形にしたいと思います。

workbook.active

# 'result_new'という名称のシートを新規に作成
result_sheet = workbook.create_sheet(title='result_new')

title = ['Aのみ','Bのみ','A or B','A and B']
alldata = [diffA,diffB,unionAB,interAB]

for i in range(len(title)):
    result_sheet.cell(row=1, column=i+1).value = title[i]
    for j in range(1, len(alldata[i])):
        result_sheet.cell(row=j+1, column=i+1).value = alldata[i][j]

workbook.save(r'C:\python_ip\20210522\pubnumber_list.xlsx')

ここでの違いは、4つの結果のリストをさらにまとめた1つのリストを作成して2次元のリストを作成してループさせて処理した点です。各列ごとに重複していた記載していた処理が1回の記載で済むようになりました。

まとめ

今回はエクセルにおけるデータの書き出しについて説明しました。今回は同じファイル内の別シートに結果を記載しましたが、別途のエクセルファイル(workbook)を新規に作成してデータを保存することもできます。興味のある方は参考リンクを見て試してみてください。

また、今回はA列、B列の2つの集合の演算でしたが、集合はいくつでも演算することが可能です。例えばA,B,C,Dの列にデータがあるとして、D列のデータのうちA~C列のいずれにも含まれていないデータを抽出などの処理もできます。より手持ちのデータに即した実践的な形で処理することをお勧めします。

次回は正規表現について少しふれたいと思います。
実は今回の公報番号の演算ではフォーマットの違いを吸収できないため例えば「特開2000-123」と「特開2000-000123」は別のデータとして処理されてしまいます。その際のデータの取り扱いに便利なのが正規表現です。

参考リンク

■openpyxl – A Python library to read/write Excel 2010 xlsx/xlsm files https://openpyxl.readthedocs.io/en/stable/#

■PythonでOpenPyXLを使ってExcelのシートを追加・削除する方法 https://tonari-it.com/python-openpyxl-beginner-add-excel-sheet/

■【Python】Excelのセルに書き込みする https://pg-chain.com/python-excel-cell-write