94253


どのようにプログラムを組み立てたらよいか、概略をご教示いただきたく質問いたしました。

### 実現したいこと

「ichiran.xlsx」ファイルと「kyou.xlsx」ファイルがあります。

「ichiran.xlsx」ファイルに「kyou.xlsx」ファイルの内容を反映させることを実現したいと考えています。

### 試したこと

以下を試しましたが、2つのファイルに同じbangouがあっても、同じセルにないと認識されませんでした。

[code]

    import openpyxl as excel
    import pandas as pd
    import numpy as np
    
    wb1 = excel.load_workbook(r'kinou.xlsx')
    wb2 = excel.load_workbook(r'kyou.xlsx')
    
    ws1 = wb1.active
    ws2 = wb2.active 
    
    for row in ws1:
        for cell in row:
            if ws1[cell.coordinate].value != ws2[cell.coordinate].value:
                print(cell.coordinate)
    
[/code]

そこで以下のようにプログラムの立て方を考えました。しかしこれでは何かが不足していると思います。  
どのようにプログラムを組み立てたらいいかご教示いただきたくお願いいたします。

### プログラムの組み立て方

  * 「ichiran.xlsx」ファイルと「kyou.xlsx」ファイルを読み込む。
  * 「ichiran.xlsx」ファイルと「kyou.xlsx」ファイルそれぞれに関し、1行分を辞書にしてリストをfor文で格納する。
  * if文で「ichiran.xlsx」ファイルのbangouと「kyou.xlsx」ファイルのbangouが一致すればkaishusuとkanryosuを「ichiran.xlsx」ファイルに上書きする。
  * 「ichiran.xlsx」ファイルのbangouと「kyou.xlsx」ファイルのbangouが一致しなければ「ichiran.xlsx」ファイルにその行を最後に追加する。

### 対象のExcelファイル

ichiran.xlsxファイル  
[![画像の説明をここに入力](https://i.stack.imgur.com/T4G5E.png)](https://i.stack.imgur.com/T4G5E.png)

kyou.xlsx  
[![画像の説明をここに入力](https://i.stack.imgur.com/QvLGD.png)](https://i.stack.imgur.com/QvLGD.png)


CC BY-SA 4.0
2023-03-19T17:21:36.533
94249
2023-03-23T12:25:15.213
2023-03-20T01:07:33.410
3060
57596
question
python アルゴリズム
PythonでExcelファイルを比較し更新箇所を上書きするプログラムの組み立て方を教えてください


もっと効率的なやり方はあると思いますが、このような形で作れます。

ichiran.xlsx->data.xlsx  
kyou.xlsx->data01.xlsx  
とし、カラム名は、番号、改修、完了と置き換え、番号列をデータキーとして判断しています。  
シート名が同じであるという前提条件も入れています。  
オリジナルデータにない、シート名、カラム名は無視されることになります。  
更新/追記を伴って、番号列はソートされて新しいエクセルファイルが生成されるようにしています。  
カラムをもと通りの順にするのは、面倒で省略してしまいました。

[code]

    import openpyxl as excel
    import pathlib
    import re
    
    path = str(pathlib.Path(__file__).parent)
    
    origin = excel.load_workbook(path+'/data.xlsx')
    update = excel.load_workbook(path+'/data01.xlsx')
    
    origin_sheetnames = origin.sheetnames
    update_sheetnames = update.sheetnames
    
    # シート名が同じであるものを抽出
    common_sheetnames = list(set(origin_sheetnames) & set(update_sheetnames))
    
    # キーとなるカラム名
    key = '番号'
    
    
    # 行列文字列のうち、列を示すアルファベット部分のみ取り出す関数
    def _get_clm_index_(text):
        re_alpha = re.compile(r"([A-Z])*", re.A)
    
        match_key = re_alpha.match(text)
    
        if match_key:
            return text[:match_key.lastindex]
    
    
    new_book_data = dict()
    
    # シート内容を比較
    for sheet_name in common_sheetnames:
        _origin_ = origin[sheet_name]
        _update_ = update[sheet_name]
    
        # 1行目にヘッダー（カラム名）があるものとする。
        _origin_head_ = _origin_["1"]
        _update_head = _update_["1"]
    
        # それぞれのカラム名と行列文字列を辞書型で抽出
        _origin_dcit = {
            cell.value: cell.coordinate
            for cell in _origin_head_}
        _update_dcit = {
            cell.value: cell.coordinate
            for cell in _update_head}
    
        # keyがカラム名にあること。
        if all(
            [
                key in _origin_dcit,
                key in _update_dcit,
            ]
        ):
            # 同じカラム名を抽出
            common_key = list(set(_origin_dcit.keys()) & set(_update_dcit.keys()))
    
            _temp_origin_ = dict()
            _temp_update_ = dict()
    
            # 列名のみに変換
            for ck in common_key:
                okey = _get_clm_index_(_origin_dcit[ck])
                ukey = _get_clm_index_(_update_dcit[ck])
                _temp_origin_[ck] = [v.value for v in _origin_[okey]]
                _temp_update_[ck] = [v.value for v in _update_[okey]]
    
            for index, num in enumerate(_temp_update_[key][1:]):
                if num in _temp_origin_[key]:
                    # udpate
                    oindex = _temp_origin_[key].index(num)
                    for k in common_key:
                        if k != key:
                            _temp_origin_[k][oindex] = _temp_update_[k][index+1]
                else:
                    # insert
                    _temp_origin_[key].append(num)
                    for k in common_key:
                        if k != key:
                            _temp_origin_[k].append(_temp_update_[k][index+1])
    
            new_book_data[sheet_name] = _temp_origin_
    
    
    if new_book_data:
        wb = excel.Workbook()
        for sheet_name in new_book_data:
            sheet = wb.create_sheet(title=sheet_name)
            sheet.title = sheet_name
            sheet_data = new_book_data[sheet_name]
    
            # update/insertされたデータをkeyの列でソートして書き出します。
            if key in sheet_data:
                keys = [key,] + list(set(sheet_data.keys())-set([key,]))
                sheet.append(keys)
                num_data = sheet_data[key][1:]
                num_data = sorted(num_data)
                for num in num_data:
                    add_data = [num,]
                    index = sheet_data[key].index(num)
                    for k in keys:
                        if k != key:
                            v = sheet_data[k][index]
                            add_data.append(v)
                    sheet.append(add_data)
    
        # 最初に作られるシートの削除
        ws = wb.active
        wb.remove_sheet(ws)
        # 新しいファイルに書き出します。
        wb.save(path+'/merge.xlsx')
    
    
[/code]


CC BY-SA 4.0
2023-03-20T05:40:51.563
94253
2023-03-20T05:40:51.563
32891
94249
answer


pandasでの処理
([`combine_first`](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.combine_first.html)
を使用)  
(CSVで確認)

[code]

    import pandas as pd
    import io
    
    CSV_ichiran = '''
    bangou,kaishusu,kanryosu
    A0103,0,0
    A0105,0,0
    A0110,0,0
    A0104,0,0
    '''
    CSV_kyou = '''
    bangou,kaishusu,kanryosu
    A0104,1,1
    A0200,2,2
    A0105,2,0
    '''
    
    #df = pd.read_excel('ichiran.xlsx', engine='openpyxl')   # heet_name='sheet1'
    df = pd.read_csv(io.StringIO(CSV_ichiran))   #, index_col='bangou')
    df2 = pd.read_csv(io.StringIO(CSV_kyou))   #, index_col='bangou')
    
    df = df2.set_index('bangou').combine_first(df.set_index('bangou')).reset_index()
    df.to_excel('ichiran.xlsx', sheet_name='new_sheet_name')
    
    #   bangou  kaishusu    kanryosu
    # 0 A0103   0   0
    # 1 A0104   1   1
    # 2 A0105   2   0
    # 3 A0110   0   0
    # 4 A0200   2   2
    
[/code]

※  
もしも 結果の順番に何か問題があるようなら, index利用しない方法になるかもしれません。  
また, 「ichiran.xlsx」ファイルにその行を最後に追加する  
というのは質問の条件に加わるのか判断できないため無視しています(index順 のはず)

* * *

#### 追記: 処理内容について

上記リンクには処理内容が記されています

> Update null elements with value in the same location in other.  
>  Combine two DataFrame objects by filling null values in one DataFrame with
> non-null values from other DataFrame. The row and column indexes of the
> resulting DataFrame will be the union of the two. The resulting dataframe
> contains the ‘first’ dataframe values and overrides the second one values
> where both `first.loc[index, col]` and `second.loc[index, col]` are not
> missing values, upon calling `first.combine_first(second)`.

適当に訳すと  
2つを 結びつける際に, 片方に存在しない場合は他のもう一方を持ってくる  
(例えば 上記コードで, 片方にしか存在しないデータは)

[code]

    In [1]: ichiran_s = {'A0103', 'A0105', 'A0110', 'A0104'}
       ...: kyou_s = {'A0104', 'A0200', 'A0105'}
    
    In [2]: ichiran_s.symmetric_difference(kyou_s)   # もしくは `ichiran_s ^ kyou_s`
    Out[2]: {'A0103', 'A0110', 'A0200'}
    
[/code]

結果は union (和集合)  
(例えば 上記コードの例では)

[code]

    In [3]: ichiran_s.union(kyou_s)   # もしくは `ichiran_s | kyou_s`
    Out[3]: {'A0103', 'A0104', 'A0105', 'A0110', 'A0200'}
    
[/code]

どちらも存在する場合, **最初の** kyou側(`df2`)を採る (置き換える側のデータ)


CC BY-SA 4.0
2023-03-20T07:58:13.490
94255
2023-03-23T12:25:15.213
2023-03-23T12:25:15.213
43025
43025
94249
answer