Pandas or openpyxlでエクセルにデータを追加方法

Question

やりたいこと

下記のjsonのデータからmessageId・titleを取得しております。
取得後、該当のエクセルにmessageIdを書き出したいです。

Jsonファイル

{
  {
  "tokens": [
    "アメリカ"
  ],
  "page": 1,
  "totalCount": 3,
  "recordCount": 3,
  "hasMore": false,
  "isLimited": false,
  "records": [
    {
      "roomId": 11264811,
      "messageId": 2268867581,
      "writerId": 13489121,
      "contentType": "post",
      "text": "アメリカ\n1",
      "createdAt": "2022-05-17T07:22:32.763Z",
      "updatedAt": "2022-05-17T07:22:32.778Z",
      "commentCount": 0,
      "pollId": -1,
      "postId": -1,
      "todoId": -1,
      "from": "web",
      "isThreaded": false,
      "mentions": [],
      "post": {
        "id": 2268867581,
        "title": "アメリカ",
        "body": "1"
      },
      "target": {
        "from": 2268867581,
        "id": 2268867581,
        "contentType": "post",
        "title": "アメリカ\n1",
        "roomId": 11264811,
        "contentTypeId": 2268867581,
        "linkId": 2262266649
      }
    },
    {
      "roomId": 11264811,
      "messageId": 2268868257,
      "writerId": 13489121,
      "contentType": "post",
      "text": "アメリカ\n1",
      "createdAt": "2022-05-17T07:22:40.405Z",
      "updatedAt": "2022-05-17T07:23:06.210Z",
      "commentCount": 0,
      "pollId": -1,
      "postId": -1,
      "todoId": -1,
      "from": "web",
      "isThreaded": false,
      "mentions": [],
      "post": {
        "id": 2268868257,
        "title": "アメリカ",
        "body": "1"
      },
      "target": {
        "from": 2268868257,
        "id": 2268868257,
        "contentType": "post",
        "title": "アメリカ\n1",
        "roomId": 11264811,
        "contentTypeId": 2268868257,
        "linkId": 2262267315
      }
    },
    {
      "roomId": 11264811,
      "messageId": 2268872585,
      "writerId": 13489121,
      "contentType": "post",
      "text": "アメリカ\n1",
      "createdAt": "2022-05-17T07:23:32.130Z",
      "updatedAt": "2022-05-17T07:23:32.157Z",
      "commentCount": 0,
      "pollId": -1,
      "postId": -1,
      "todoId": -1,
      "from": "web",
      "isThreaded": false,
      "mentions": [],
      "post": {
        "id": 2268872585,
        "title": "アメリカ",
        "body": "1"
      },
      "target": {
        "from": 2268872585,
        "id": 2268872585,
        "contentType": "post",
        "title": "アメリカ\n1",
        "roomId": 11264811,
        "contentTypeId": 2268872585,
        "linkId": 2262271578
      }
    }
  ]
}

元のエクセル

no_plate	title	message id
1563	アメリカ
1563	アメリカ
1341	日本	2262271545
1456	イギリス	2262271589
1456	アメリカ	2262271522
1565	アメリカ

期待する結果

Jsonから取得した順番のようにデータを下記のエクセルのようにmessage id
を書き込みしたいです。

#タイトル取得
title= [x["post"]["title"] for x in json_load["records"]]
print(title)

#メッセージID取得
messageId= [x["messageId"] for x in json_load["records"]]
print(messageId)

['アメリカ', 'アメリカ', 'アメリカ']
[2268867581, 2268868257, 2268872585]

エクセルのカラムtitleと元のJsonファイルのtitle一致しております。
titleが重複する時があります。
message idが空白の場合、message idを追記したいです。

no_plate	title	message id
1563	アメリカ	2268867581
1563	アメリカ	2268868257
1341	日本	2262271545
1456	イギリス	2262271589
1456	アメリカ	2262271522
1565	アメリカ	2268872585

現在、下記のコードでセルを指定して書き出しております。
毎回データが変化するので、セルを指定せずに自動的に該当のmessageIdに入力可能でしょうか。
pandasもしくはopenpyxlで書き込みが詳しい方教えていただけませんでしょうか。

全体コード

from email import message
import json
import pandas as pd
from openpyxl import load_workbook

json_open = open("C:\\Users\\test\\Desktop\\test.json", 'r')
json_load = json.load(json_open)

print(json_load)

#messageId取得方法　for分なし
#messageId = json_load["records"][0]["messageId"]
#messageId = json_load["records"][0]["post"]["title"]
#print(messageId)

#タイトル取得
title= [x["post"]["title"] for x in json_load["records"]]
print(title)

#メッセージID取得
messageId= [x["messageId"] for x in json_load["records"]]
print(messageId)

#エクセルファイル
excelfile="C:\\Users\\test\\Desktop\\country.xlsx"

#エクセル読み込み
df = pd.read_excel(excelfile)
print(df)

# #df.loc['アメリカ':'アメリカ', 'アメリカ'] = [messageId]  
# print(df)

# ワークブックを読み込む
wb = load_workbook(excelfile)

#シート指定 
ws = wb['Sheet1']
#value値をDataFrameに変換
df = pd.DataFrame(ws.values)

#JSONファイルから取得したタイトルとメッセージIDをエクセルに入力
ws['C2'] = json_load["records"][0]["messageId"]

ws['C3'] = json_load["records"][1]["messageId"]

ws['C6'] = json_load["records"][2]["messageId"]

#エクセル保存
wb.save(excelfile)

#エクセル読み込み
print(df)

お手数ですが、よろしくお願い致します。

Answer 1

こちらの方法で解決しました。

#エクセル読み込み
df = pd.read_excel(excelfile, dtype=str)
print(df)

for curtitle, curmsgid in zip(title, messageId):
    wdf = df.query('(title == @curtitle) and (`message id` != `message id`)')
    if len(wdf) <= 0: continue
    df.at[wdf.index[0], 'message id'] = curmsgid

df.to_excel(excelfile,index=False)
print(df)