30年記念誌の作成 (その2)

こちらのページの続編です。
[30年記念誌の作成(その1)](https://qiita.com/ekzemplaro/items/a3733dc632f2112b2929)

収集したデータを Google Spread Sheet に集めます。
その中から、必要なデータを抽出します。

## 元データ
![hanekawa01.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/179446/2e1c800d-f5cd-9930-1c2d-a2c4c6e0a19e.png)

## 抽出したデータ
![hanekawa02.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/179446/2d664a44-5691-b12c-bac8-e5a6926b8ec0.png)

## 変換プログラム

次の3本を使います。

<ul>
<li>スプレッドシートがら、JSON に変換</li>
<li>JSON からデータを抽出し JSON に保存</li>
<li>JSON から、Google Spread Sheet に変換</li>
</ul>

## スプレッドシートがら、JSON に変換

```py:spread_to_json.py
#! /usr/bin/python
#
#	spread_to_json.py
#
#					Nov/29/2024
# ------------------------------------------------------------------
import sys
import os

# pipx でインストールされたパッケージのパスを取得

package_path = os.path.expanduser('~/.local/share/pipx/venvs/gspread/lib/python3.12/site-packages')

sys.path.append(package_path)

package_path = os.path.expanduser('~/.local/share/pipx/venvs/gspread-dataframe/lib/python3.12/site-packages')

sys.path.append(package_path)

import gspread
from oauth2client.service_account import ServiceAccountCredentials
import pandas as pd
from gspread_dataframe import get_as_dataframe, set_with_dataframe

# ------------------------------------------------------------------

credentials = ServiceAccountCredentials.from_json_keyfile_name('./service_account.json')
#
client = gspread.authorize(credentials)

spreadsheet_id = '1eK0hZ_kI6FO2J2RZ94hizP7VZ1lkYObX5N8Z9****'

workbook = client.open_by_key(spreadsheet_id)
worksheet = workbook.sheet1

df_read = get_as_dataframe(worksheet, header=None)
df_json = df_read.to_json()
print(df_json)
#
# ------------------------------------------------------------------
```

## JSON からデータを抽出し JSON に保存

```py:convert_json.py
#! /usr/bin/python
#
#	omit_head.py
#
#					Nov/29/2024
# ------------------------------------------------------------------
import sys
import os
# import json
import pandas as pd

# ------------------------------------------------------------------
# [4]:
def omit_header_proc(df_in):
	row_index = df_in.loc[df_in.iloc[:,0] == '学年'].index[0]
	sys.stderr.write("row_index = %d\n" % row_index)

	df_dropped = df_in.iloc[row_index+1:]
	return df_dropped
# ------------------------------------------------------------------
file_json="./in01.json"
#
df_json = pd.read_json(file_json)

#print(df_json.iloc[:,1])

df_dropped = omit_header_proc(df_json)
df_dropped.iloc[:, 0] = df_dropped.iloc[:, 0].ffill()
df_dropped.iloc[:, 1] = df_dropped.iloc[:, 1].ffill()
df_filtered = df_dropped[df_dropped.iloc[:,2] == '素話']
df_omit = df_filtered.iloc[:, :4]
df_omit.iloc[:, 3] = df_omit.iloc[:, 3].str.split('\n', expand=True)[0]
json_out = df_omit.to_json()
print(json_out)
#
# ------------------------------------------------------------------
```

## JSON から、Google Spread Sheet に変換

```py:json_to_spread.py
#! /usr/bin/python
#
#	from_json.py
#
#						Nov/29/2024
# ------------------------------------------------------------------
import sys
import os

package_path = os.path.expanduser('~/.local/share/pipx/venvs/gspread/lib/python3.12/site-packages')
sys.path.append(package_path)

package_path = os.path.expanduser('~/.local/share/pipx/venvs/gspread-dataframe/lib/python3.12/site-packages')
sys.path.append(package_path)

import pandas as pd
import gspread
from gspread_dataframe import get_as_dataframe, set_with_dataframe
#
# ------------------------------------------------------------------
sys.stderr.write("*** 開始 ***\n")

file_json = sys.argv[1]
# Google Sheets APIの認証情報
service_account_json = 'service_account.json'

sheet_id = '1eK0hZ_kI6FO2J2RZ94hizP7VZ1lkYObX5N8Z9*****'

write_sheet_name = 'Sheet2'

# Google Sheets APIの認証
gc = gspread.service_account(filename=service_account_json)

df_json = pd.read_json(file_json)

spreadsheet = gc.open_by_key(sheet_id)
ws_list = spreadsheet.worksheets()
print(ws_list)
sheet_names = []
for tt in ws_list:
	sheet_names.append(tt.title)
print(sheet_names)
if not write_sheet_name in sheet_names:
	spreadsheet.add_worksheet(title=write_sheet_name, rows=50, cols=10)
#
worksheet = spreadsheet.worksheet(write_sheet_name)
set_with_dataframe(worksheet, df_json, include_column_header=False)
#
sys.stderr.write("*** 終了 ***\n")
# ------------------------------------------------------------------
```