从 Excel 到 Python

mappings = {'loan identifier': 'loan_id',               'amt': 'amount',                ...               'init fees': 'initial_fees'}    data.rename(mappings, axis=1, inplace=True) 

import openpyxl # 加载工作簿 wb = openpyxl.load_workbook("sheet.xlsx", data_only=True) # 创建工作簿对象 ws = wb.active # 获取单元格E4的值 value = ws['E4'].value 

# 首先，我们设置Excel文件的路径 path = r".\documents\tool_setup.xlsx" # 加载文件，创建工作簿对象 wb = openpyxl.load_workbook(path, data_only=True) # 找到目标工作表的索引 idx = [i for i, name in wb.sheetnames if name == 'Mapping'][0] # 将目标工作表设置为当前工作表 wb.active = idx ws = wb.active 

mappings = {} mappings['Amount'] = ws["E4"].value mappings['Term'] = ws["E5"].value 

# 定义一个函数，用于查找openpyxl工作簿对象中的给定列 def search_col(sheet, column, value, limit=100):      # 从1开始，逐行查找，直到达到limit限制     for row in range(1, limit+1):                if sheet[f"column{row}"].value == value:             # 找到想要的单元格，返回单元格的列和行             return (col, row) 

search_col(ws, 'B', 'Internal') [Out]: ('B', 12) 

empty = 0  # 初始化空单元格数量 while empty < 2:     # 增加行计数     row += 1     # 赋值     internal = ws[f'B{row}'].value          if internal is None:         empty += 1  # 遇到空单元格就增加空单元格计数     else:         # 加入mappings         mappings[internal] = ws[f'D{row}'].value         empty = 0  # also re-initialize the empty counter 

{     'Loan ID': 'loan identifier',     'Product': 'product type',      ...     'Initial Fees': 'init fees' } 

row, _ = search_col(ws, 'C', 'Filepath') mappings['filepath'] = ws[f'D{row}].value 

data = pd.read_csv(mappings['Filepath']) 

# 翻转 inv_mappings = {mappings[key]: key for key in mappings} 

data.rename(inv_mappings, axis=1, inplace=True) 

data = pd.read_csv(mappings['Filepath']) # 转成小写，剔除不必要的空格 data.rename({col: col.strip().lower() for col in data.columns},             axis=1, inplace=True) # inv_mappings也是一样 # 内部标签使用蛇形命名方式 (不是必需的) inv_mappings = {     mappings[key].strip().lower():         key.strip().lower().replace(' ', '_')     for key in mappings } # 现在安全了 data.rename(inv_mappings, axis=1, inplace=True) 

"Loan ID" -> "loan_id" "Initial Rate" -> "initial_rate"