bibi/src/read_backendDB.py

import pandas as pd
from pathlib import Path

'''
讀取後臺資料庫檔案，並轉存
'''

proj_path = Path.cwd().parents[0]
print(f'working path is {str(proj_path)}')
# pth_rawdb = sorted(proj_path.joinpath('backend-DB/raw').glob('*.csv'))[0]
pth_rawdb = proj_path.joinpath('backend-DB/raw/srda20231016.xlsx')

rawdb = pd.read_excel(pth_rawdb)
rawdb['作者'] = rawdb['作者'].str.replace('　', '')  # 移除全形空白
key_columns = ['出版年', '原始題目', '著作性質', '出處']
rawdb[key_columns] = rawdb[key_columns].fillna(method='ffill')

author = rawdb.dropna(subset=['作者']).groupby(key_columns, as_index=False)[
    '作者'].apply(lambda x: '; '.join(x.astype(str)))
dataNb = rawdb.dropna(subset=['登錄號']).groupby(key_columns, as_index=False)[
    '登錄號'].apply(lambda x: '; '.join(x.astype(str)))
# NOTE: 有人填錯欄位了

cluster = rawdb.dropna(subset=['引用資料']).groupby(key_columns, as_index=False)[
    '引用資料'].apply(lambda x: '; '.join(x.astype(str)))
df = rawdb.dropna(subset=['前台是否顯示']).drop(['作者', '登錄號', '引用資料'], axis=1)
# 合併新的作者、叢集資訊
merged_data = pd.merge(df, author, on=key_columns, how='left')
merged_data = pd.merge(merged_data, dataNb, on=key_columns, how='left')
merged_data = pd.merge(merged_data, cluster, on=key_columns, how='left')
# arrange the order of columns
merged_data = merged_data[rawdb.columns].reset_index(drop=True)

# duplicated
duplicate_rows = merged_data[merged_data.duplicated(
    subset=['出版年', '原始題目', '著作性質', '出處'], keep=False)]
print(f'重複資料筆數: {duplicate_rows.shape[0]}')


# Export
expth = proj_path.joinpath('backend-DB/SRDA-BibData10.csv')
merged_data.to_csv(expth, index=False, encoding='utf8')


# TODO: 作者欄位要改成dict，輸出成json