<ブロッククオート

著者 リトルミン

日々の仕事の中で、様々なテーブルの結合の要件に出会うことがあります。これらの要件は、核心原理さえ理解すれば簡単で、本質も同じなので、ほとんどの読者はほとんど解決できると思います。

基本的な考え方

1. マージが必要なファイルを繰り返し処理する。
2. データを読み込んでマージする(pandasが一番簡単で便利)
3. データの保存
   1. スタイルが不要で、Pandasオブジェクトを使って直接書き込むことができる
   2. スタイルの要件、テンプレートのロードにopenpyxlを使用すること
   3. 元のフォームと全く同じスタイルが必要な場合、VBAのコピー＆ペーストを使用する（この記事では実装していません）。

まず、よりシンプルなファイルのトラバースの方法について見てみましょう。

ドキュメントをトラバースする例

カレントディレクトリにあるExcelのうち、接尾辞がxlsxのファイルを、~またはrで始まるファイルを除いて、繰り返し処理します。

from glob import glob

glob("[! ~r]*.xlsx")

['merge-result.xlsx', 'multi-sheet table merge.xlsx', 'merge-with-sheet-header-style.xlsx']

glob("[! ~r]*.xls*")

['test.xls', 'merge result.xlsx', 'multi-sheet table merge.xlsx', 'merge with table header style.xlsx']

また、xls 形式のファイルも含めたい。

glob("**/[! ~r]*.xls*", recursive=True)

['test.xls',
 'Merge results.xlsx',
 'Multi-sheet table merge.xlsx',
 'Merge with header style.xlsx',
 'Excel multi-sheet merge\\excel3.xlsx',
 'Excel multi-sheet merge \\excel4.xlsx',
 'Excel multi-sheet merge\\new folder\\excel3.xlsx',
 'Excel multi-sheet merge \\\new folder\\\excel4.xlsx',
 'Excel multi-sheet merge\\\new folder\\\\new folder\\\\excel3.xlsx',
 'Excel multi-sheet merge\\new folder\\\new folder\\\excel4.xlsx',
 'Merge with style \\HB area.xlsx',
 'Merge with style \\HN area.xlsx',
 'Merge with style \\XN area.xlsx',
 'Merge with style \\ summary table.xlsx']

import os 
path = os.path.expanduser("~/Documents/WeChat Files")
glob(f"{path}/**/[! ~r]*.xls*", recursive=True)

サブフォルダを含む現在のフォルダを再帰的に走査する。

from glob import glob
import pandas as pd

dfs = [pd.read_csv(file, skiprows=1, sep="|", compression="gzip")
       for file in glob("gzip/*.csv.gz")]
df = pd.concat(dfs, ignore_index=True)
df.to_csv("merged csv compressed file .csv.gz", index=False, compression="gzip")

from glob import glob
import pandas as pd
import numpy as np

columns = ['Date_ID', 'erbs', 'EUtranCell']
dfs = [pd.read_csv(file, usecols=[0, 2, 3]).values for file in glob("csv/*.csv")]
df = pd.DataFrame(np.vstack(dfs), columns=columns)
df.to_csv("merged csv file.csv", index=False)

指定されたフォルダを再帰的に走査する (例: WeChat で受信したローカルに記録されたすべての Excel ファイルを検索する)。

import pandas as pd
import glob

dfs = [
    pd.read_csv(file).rename(
        columns=lambda x:"EUtranCell" if x.startswith("EUtranCell") else x)
    for file in glob.glob("csv/*.csv")
]
df = pd.concat(dfs, ignore_index=True)
df.to_csv("merged csv file 2.csv", index=False)

ファイルのトラバースの基本的な使い方をマスターしたら、正式にファイルのマージを始めましょう。

スタイルフリーな単一ファイルのマージの例

ケース1． gzip で圧縮された csv ファイルの束があり、新しい csv ファイルにマージする必要があります。

解凍されたテキスト形式。

gzip圧縮されたcsvファイルの束を、最終的に1つのgzip圧縮されたcsvファイルにマージする。

import pandas as pd
from pathlib import Path

result = []
for file in Path("csv/PT0004B_LOG").glob("*.csv"):
    df = pd.read_csv(file, header=None, usecols=[1, 2], index_col=0)
    t = df[2].str[2:]
    r = [str(file.name[:-4])]
    r.extend(t.loc["0xa17":"0xa1a"].values)
    r.extend(t.loc["0xa1c":"0xa20":2].values +
             t.loc["0xa1b":"0xa20":2].str.zfill(2).values)
    result.append(r)
df = pd.DataFrame(
    result, columns=["file", "R", "GR", "GB", "B", "RG_L_H", "BG_L_H", & quot;GG_L_H"])
df.to_excel("combine.xlsx", index=False)

df.head()

最終的なマージ結果です。

ケース2 csvファイルの束で、カラムの3つだけを取り、テーブル名は可変だが、相対的な順序は同じである

path = "Excel multi-sheet merge"
dfs = [pd.read_excel(file) for file in glob.glob(f"{path}/**/[! ~]*.xls*", recursive=True)]
df = pd.concat(dfs, ignore_index=True)
df.to_excel("merge_results.xlsx", index=False)

ケース3． 非常に多くの列を持つcsvファイルの束で、列名を変更した列が1つだけある場合

Excel multi-sheet merge

その他のメソッド（通常はこのように書きません。）

列名をあらかじめ定義できる場合は、以下の2つの書き分けを推奨します。

ケース4 レジスタデータの加工とマージ

要求事項の説明

1. 10個のレジスタ0xa17~0xa20のデータを読み出す必要があり、最初の4個のレジスタは4列のR,GR,RB,Bに保存されています。
2. 0xa1b~0xa20 6レジスタの後、2つずつRG_L_H,BG_L_H,GG_L_H 3カラムにマージされる
3. 各行のデータが読み込まれるファイル名を特定する。

以下に示すとおりです。

特殊なケース

ファイル名の取得を容易にするために、pathlibを使用してグロブトラバーサルを行います。

import pandas as pd
from glob import glob

path = "Excel multi-sheet merge"
data = {}
for file in glob(f"{path}/**/[! ~]*.xls*", recursive=True):
    for name, df in pd.read_excel(file, sheet_name=None).items():
        data.setdefault(name, []).append(df)

with pd.ExcelWriter("Multi-sheet table merge.xlsx") as write:
    for name, dfs in data.items():
        pd.concat(dfs).to_excel(write, name, index=False)

import pandas as pd
from glob import glob

path = "merge with style"
# Define the sheet name to be read and the number of rows to be skipped
sheet_start_num = {'03': 3, '06': 2, '07': 3, '08': 3}
data = {}
for file in glob(f"{path}/**/[! ~r sink]*.xls*", recursive=True):
    for sheet_name, skiprows in sheet_start_num.items():
        excel = pd.ExcelFile(file)
        df = excel.parse(sheet_name=sheet_name, skiprows=skiprows, header=None)
        data.setdefault(sheet_name, []).append(df.values)

<イグ

同じ名前を持つ複数シートのテーブルをスタイルなしでマージする

フォルダ内のすべてのデフォルトのExcelシートを再帰的にマージすることは、たった4行のコードで非常に簡単にできます。

from openpyxl import load_workbook

workbook = load_workbook(filename="merge with style/summary table.xlsx")

for sheet_name, sheet_data in data.items():
    sheet = workbook[sheet_name]
    sheet.delete_rows(sheet_start_num[sheet_name] + 1, sheet.max_row)
    for row in np.vstack(sheet_data).tolist():
        sheet.append(row)
workbook.save(filename="merge.xlsx")

次のリクエストは、フォルダ内のすべてのExcelテーブルに対するもので、すべてのシートを別々にマージすることを要求しています。

データの例は以下の通りです。

マージされるフォルダーの名前が、仮に from tkinter import filedialog filedialog.askdirectory(initialdir=". ") filedialog.asksaveasfilename(title="save", initialdir=". ", defaultextension="xlsx", filetypes=[("Excel Workbook", "*.xlsx"), ("Excel 97-2003 workbook", "*.xls")]) の場合、マージコードは次のようになります。

from tkinter import filedialog
import pandas as pd
from glob import glob

path = filedialog.askdirectory(initialdir=". ")
data = {}
for file in glob(f"{path}/**/[! ~]*.xls*", recursive=True):
    for name, df in pd.read_excel(file, sheet_name=None).items():
        data.setdefault(name, []).append(df)

save_name = filedialog.asksaveasfilename(title="save",
                             initialdir=". ",
                             defaultextension="xlsx",
                             filetypes=[("Excel Workbook", "*.xlsx"),
                                        ("Excel 97-2003 workbook", "*.xls")])
with pd.ExcelWriter(save_name) as write:
    for name, dfs in data.items():
        pd.concat(dfs).to_excel(write, name, index=False)

結果をマージします。

複数シートのテーブルをヘッダースタイルを維持したまま同名でマージする。

元のスタイルとの完全なマージを必要とする場合、この記事はデモンストレーションされず、具体的な実際のニーズがあるときに別の記事が検討されることになります。

要求事項の説明

地域別テーブルがたくさんある。

今度は、各地域のテーブルの黄色いシートを統合して、サマリーテーブルにする必要があります。

ヘッダーのスタイルを統一するために、あらかじめテンプレートを作成しておくか、マージされるファイルのどれかをテンプレートとして選択すればよいのです。

まず、データを読み込む。

from glob import glob
import pandas as pd

from gooey import Gooey, GooeyParser


def combine_excel(path, save_name):
    data = {}
    for file in glob(f"{path}/**/[! ~]*.xls*", recursive=True):
        for name, df in pd.read_excel(file, sheet_name=None).items():
            data.setdefault(name, []).append(df)
    with pd.ExcelWriter(save_name) as write:
        for name, dfs in data.items():
            pd.concat(dfs).to_excel(write, name, index=False)

@Gooey
def main():
    parser = GooeyParser(description="Multi-Excel Multi-Sheet Merge Program - @SmallMind")
    parser.add_argument('path', help="Excel file directory to be merged", widget="DirChooser")
    parser.add_argument('save_name', help="File to be saved after merge (as Excel file)", widget="FileSaver")
    args = parser.parse_args()
    print("Input path: ", args.path)
    print("Save location: ", args.save_name)
    combine_excel(args.path, args.save_name)
    print("Merge complete! ")


if __name__ == '__main__':
    main()

次に、openpyxlを介してテンプレートを読み込み、以下のように個々のサブテーブルにデータを書き込む。

from glob import glob
import pandas as pd

from gooey import Gooey, GooeyParser


def combine_excel(path, save_name):
    data = {}
    files = glob(f"{path}/**/[! ~]*.xls*", recursive=True)
    for i, file in enumerate(files, 1):
        for name, df in pd.read_excel(file, sheet_name=None).items():
            data.setdefault(name, []).append(df)
        yield f"merge progress: {i}/{len(files)}"
    with pd.ExcelWriter(save_name) as write:
        items = data.items()
        for i, (name, dfs) in enumerate(items, 1):
            pd.concat(dfs).to_excel(write, name, index=False)
            yield f"Save progress: {i}/{len(items)}"


@Gooey(progress_regex=r"^. Progress:(?P
\d+)/(?P
\d+)$"
,
       progress_expr="current / total * 100",
       timing_options={
           'show_time_remaining': False,
           'hide_time_remaining_on_complete': True,
       })
def main():
    parser = GooeyParser(description="Multi-Excel Multi-Sheet Merge Program - @SmallMind")
    parser.add_argument('path', help="Excel file directory to be merged", widget="DirChooser")
    parser.add_argument('save_name', help="File to be saved after merge (as Excel file)", widget="FileSaver")
    args = parser.parse_args()
    print("Input path: ", args.path)
    print("Save location: ", args.save_name)
    for msg in combine_excel(args.path, args.save_name):
        print(msg)
    print("Merge complete! ")


if __name__ == '__main__':
    main()

最終的には、ヘッダスタイルを持つマルチシートマージになります。

グラフィカルなインターフェイスは、指定されたディレクトリを選択する

グラフィカルなインターフェースにしたい場合は、tkの以下のコンポーネントを使って、マージするディレクトリや、保存先を選択します。

from tkinter import filedialog

filedialog.askdirectory(initialdir=". ")

filedialog.asksaveasfilename(title="save",
                             initialdir=". ",
                             defaultextension="xlsx",
                             filetypes=[("Excel Workbook", "*.xlsx"),
                                        ("Excel 97-2003 workbook", "*.xls")])

Excelの複数シートのマージを例にとると、以下のようなコードでpyスクリプトを書くことができます。

from tkinter import filedialog
import pandas as pd
from glob import glob

path = filedialog.askdirectory(initialdir=". ")
data = {}
for file in glob(f"{path}/**/[! ~]*.xls*", recursive=True):
    for name, df in pd.read_excel(file, sheet_name=None).items():
        data.setdefault(name, []).append(df)

save_name = filedialog.asksaveasfilename(title="save",
                             initialdir=". ",
                             defaultextension="xlsx",
                             filetypes=[("Excel Workbook", "*.xlsx"),
                                        ("Excel 97-2003 workbook", "*.xls")])
with pd.ExcelWriter(save_name) as write:
    for name, dfs in data.items():
        pd.concat(dfs).to_excel(write, name, index=False)

のGooyツールを使ってGUIに変換することも検討してください。

from glob import glob
import pandas as pd

from gooey import Gooey, GooeyParser


def combine_excel(path, save_name):
    data = {}
    for file in glob(f"{path}/**/[! ~]*.xls*", recursive=True):
        for name, df in pd.read_excel(file, sheet_name=None).items():
            data.setdefault(name, []).append(df)
    with pd.ExcelWriter(save_name) as write:
        for name, dfs in data.items():
            pd.concat(dfs).to_excel(write, name, index=False)

@Gooey
def main():
    parser = GooeyParser(description="Multi-Excel Multi-Sheet Merge Program - @SmallMind")
    parser.add_argument('path', help="Excel file directory to be merged", widget="DirChooser")
    parser.add_argument('save_name', help="File to be saved after merge (as Excel file)", widget="FileSaver")
    args = parser.parse_args()
    print("Input path: ", args.path)
    print("Save location: ", args.save_name)
    combine_excel(args.path, args.save_name)
    print("Merge complete! ")


if __name__ == '__main__':
    main()

マージの進行状況は、Gooey を介して以下の場所でも表示できます。

from glob import glob
import pandas as pd

from gooey import Gooey, GooeyParser


def combine_excel(path, save_name):
    data = {}
    files = glob(f"{path}/**/[! ~]*.xls*", recursive=True)
    for i, file in enumerate(files, 1):
        for name, df in pd.read_excel(file, sheet_name=None).items():
            data.setdefault(name, []).append(df)
        yield f"merge progress: {i}/{len(files)}"
    with pd.ExcelWriter(save_name) as write:
        items = data.items()
        for i, (name, dfs) in enumerate(items, 1):
            pd.concat(dfs).to_excel(write, name, index=False)
            yield f"Save progress: {i}/{len(items)}"


@Gooey(progress_regex=r"^. Progress:(?P
\d+)/(?P
\d+)$"
,
       progress_expr="current / total * 100",
       timing_options={
           'show_time_remaining': False,
           'hide_time_remaining_on_complete': True,
       })
def main():
    parser = GooeyParser(description="Multi-Excel Multi-Sheet Merge Program - @SmallMind")
    parser.add_argument('path', help="Excel file directory to be merged", widget="DirChooser")
    parser.add_argument('save_name', help="File to be saved after merge (as Excel file)", widget="FileSaver")
    args = parser.parse_args()
    print("Input path: ", args.path)
    print("Save location: ", args.save_name)
    for msg in combine_excel(args.path, args.save_name):
        print(msg)
    print("Merge complete! ")


if __name__ == '__main__':
    main()