# -*- coding: utf-8 -*- # @Time : 2024/2/2 10:45 # @Author : zhaoxiangpeng # @File : BCR_20240201.py import os from copy import deepcopy import pandas as pd import data_process_tool from bcr.utils import read_file, str2float, str2int from config import KEEP_COLUMNS, REDUCE_COLUMNS, ROOT_PATH # ROOT_PATH = "Y:\\zhaoxiangpeng\\2024BCR" # ROOT_PATH = "Y:\\zhaoxiangpeng\\BCR202403" # ROOT_PATH = "Y:\\BCR\\202407" C_COLUMNS = ['DOI', 'ISBN RAW', '2022', '2023', '2024', 'Grand Total', 'ISBN'] def main(): table = read_file(os.path.join(ROOT_PATH, 'MergeFile')) t2 = pd.read_csv(os.path.join(ROOT_PATH, '补充数据填充2021年total.txt'), sep='\t') table = pd.concat([table, t2]) table.drop_duplicates(subset=['EID'], keep='last', inplace=True) # 把数量统计标准化 table['2021'] = table['2021'].apply(str2float) table['2022'] = table['2022'].apply(str2float) table['2023'] = table['2023'].apply(str2float) table['Grand Total'] = table['Grand Total'].apply(str2float) step2_table = step2(table, export=True) step3_table, no_data_table = step3(step2_table, export=True) step4(no_data_table) def process1(table: pd.DataFrame): TABLE2 = deepcopy(table) # 表头重命名 # new_columns = data_process_tool.rename_head(TABLE2, postfix='-Other') # TABLE2.rename(columns=new_columns, inplace=True) # 根据doi去重只保留一个用于doi匹配 DOI_PROED = TABLE2.dropna(subset=['DOI']) DOI_PROED.drop_duplicates(subset=['DOI'], inplace=True) # 把doi为空的删掉，没有doi的用isbn匹配 ISBN_PROED = TABLE2[TABLE2['DOI'].isnull()] ISBN_PROED.drop_duplicates(subset=['ISBN'], inplace=True) return DOI_PROED, ISBN_PROED def process_func2(table: pd.DataFrame): """ isbn分列 """ TABLE2 = deepcopy(table) TABLE2['ISBN'] = TABLE2['ISBN'].astype(str) # 要转为str类型，不然会分不到 ISBNs = TABLE2['ISBN'].str.split('; ', expand=True) ISBNs = ISBNs.stack() # 把行转成列 ISBNs = ISBNs.reset_index(level=1, drop=True) # 重置索引, 并删除多余的索引 ISBNs.name = 'ISBN' EID_PROED: pd.DataFrame = TABLE2.rename(columns={'ISBN': 'ISBN RAW'}).join(ISBNs) return EID_PROED def process_func3(export: bool = True): """ 合并两个scopus表 """ keep_columns = [ 'Title', 'Scopus ID', 'Print ISBN', 'E-ISBN', 'Other ISBN', 'Publication year', 'Publisher imprint', 'Publisher imprints grouped to main Publisher', 'Classification 1', 'Classification 2', 'Classification 3', 'Classification 4', ] export_file_path = os.path.join(ROOT_PATH, "After\\4.两表字段合并.xlsx") if not os.path.exists(export_file_path): table1_path = os.path.join('Y:\\BCR\\202407', 'Scopusbooks04072023.xlsx') table2_path = os.path.join('Y:\\BCR\\202407', 'Scopus Books June 2023新增书目3.9种及检索式.xlsx') table1 = pd.read_excel(table1_path, sheet_name=0) table1 = table1[keep_columns] table2 = pd.read_excel(table2_path, sheet_name=0) table2 = table2[keep_columns] table0 = pd.concat([table1, table2]) table0.drop_duplicates(subset=['Print ISBN', 'E-ISBN', 'Other ISBN'], keep='last', inplace=True) table0['Scopus ID'] = table0['Scopus ID'].astype(str) if export: table0.to_excel(export_file_path, index=False) else: table0 = pd.read_excel(export_file_path, sheet_name=0) return table0 def step2(table: pd.DataFrame, export: bool = True): """ ppt第二个需求 """ group_by = table.groupby(by=['ISBN'])['2021', '2022', '2023', 'Grand Total'].sum() group_by.reset_index(inplace=True) keep_columns = table[['DOI', 'ISBN']] keep_columns.drop_duplicates(keep='first', subset=['ISBN'], inplace=True) result_table = pd.merge(left=keep_columns, right=group_by, how='right', on=['ISBN']) if export: result_table.to_excel(os.path.join(ROOT_PATH, "RESULT\\2.统计ISBN使用量(保留DOI).xlsx"), index=False) return result_table def step3(table: pd.DataFrame, export: bool = True): """ ISBN合并记录与上一年BCR总表匹配 按DOI、ISBN的顺序匹配，匹配到的记录，二个表的字段合并 """ BASE_FILE = pd.read_excel("Y:\\BCR\\2024BCR\\BCR2023数据处理\\副本BCR2022总表-20220729.xlsx", sheet_name=0) # 上一年的BCR总表 # 表头加标记 new_columns = data_process_tool.rename_head(BASE_FILE, postfix='-Other') BASE_FILE.rename(columns=new_columns, inplace=True) doi_table, isbn_table = process1(table) doi_ = pd.merge(doi_table, BASE_FILE, how='left', left_on=['DOI'], right_on=['DOI-Other']) """ # 把doi分成有数据的和没数据的 has_data = doi_[doi_['DOI-Other'].notnull()] # 匹配到数据 no_data = doi_[doi_['DOI-Other'].isnull()] # 使用doi没有匹配到的 # del doi_ no_data = no_data[table.columns.values.tolist()] # 把没有数据的多余列去除 """ # 用没有匹配到doi的数据用isbn进行匹配 isbn_ = pd.merge(isbn_table, BASE_FILE, how='left', left_on=['ISBN'], right_on=['ISBN-Other']) # 这些就不用考虑没有匹配到的了，因为没有剩下的条件了 # 合并doi匹配结果和isbn的结果 result_table = pd.concat([doi_, isbn_]) if export: result_table.to_excel(os.path.join(ROOT_PATH, 'RESULT\\3.BCR匹配结果.xlsx'), index=False) # 通过doi和isbn都没有匹配到的 all_no_data = result_table[result_table['ISBN-Other'].isnull()] all_no_data = all_no_data[table.columns.values.tolist()] # 保留基础列 if export: all_no_data.to_excel(os.path.join(ROOT_PATH, 'RESULT\\3.BCR未匹配到.xlsx'), index=False) return result_table, all_no_data def step4(table: pd.DataFrame, export: bool = True): """ ISBN合并记录与上一年BCR总表不匹配记录处理 与SCOPUS来源书目匹配 把二个表的ISBN分列，进行交叉匹配 把二个表的字段进行合并 再与OASIS记录匹配 获取作者、学科分类数据 删除敏感书目 """ df1 = process_func2(table) # 不匹配记录 df1.drop_duplicates(subset=['ISBN RAW', 'ISBN'], inplace=True) df1['ISBN'] = df1['ISBN'].astype(str) print(df1) df2 = process_func3(export=export) for col in ['Print ISBN', 'E-ISBN', 'Other ISBN']: df2[col] = df2[col].astype(str) c1 = pd.merge(df1, df2, left_on=['ISBN'], right_on=['Print ISBN'], how='left') c1_in = c1[c1['Print ISBN'].notnull()] c1_not = c1[c1['Print ISBN'].isnull()] c1_not = c1_not[C_COLUMNS] c2 = pd.merge(c1_not, df2, left_on=['ISBN'], right_on=['E-ISBN'], how='left') c2_in = c2[c2['E-ISBN'].notnull()] c2_not = c2[c2['E-ISBN'].isnull()] c2_not = c2_not[C_COLUMNS] c3 = pd.merge(c2_not, df2, left_on=['ISBN'], right_on=['Other ISBN'], how='left') c3_in = c3[c3['Other ISBN'].notnull()] c3_not = c3[c3['Other ISBN'].isnull()] # 3次匹配结果合并 r1_in = pd.concat([c1_in, c2_in, c3_in]) r1_in.drop_duplicates(subset=['ISBN RAW'], inplace=True) r1_not = c3_not r1_not = pd.concat([r1_not, r1_in, r1_in]).drop_duplicates(subset=['ISBN RAW'], keep=False) r1_not = r1_not[['DOI', 'ISBN RAW', '2022', '2023', '2024', 'Grand Total']] r1_not.rename(columns={'ISBN RAW': 'ISBN'}, inplace=True) if export: r1_in.to_excel(os.path.join(ROOT_PATH, 'RESULT\\4.与SCOPUS来源书目匹配.xlsx'), index=False) r1_not.to_excel(os.path.join(ROOT_PATH, 'RESULT\\4.与SCOPUS来源书目未匹配到.xlsx'), index=False) if __name__ == '__main__': main() """ step2_table = pd.read_excel(os.path.join(ROOT_PATH, "RESULT\\2.统计ISBN使用量(保留DOI).xlsx"), sheet_name=0) step3_table, no_data_table = step3(step2_table, export=True) step4(no_data_table) """ # ste3_table = pd.read_excel(os.path.join(ROOT_PATH, 'RESULT\\3.BCR未匹配到.xlsx'), sheet_name=0) # step4(ste3_table)