目录
Schema Mapping
import pandas as pd
# 读取CSV文件(假设这些文件已经存在)
table_1 = pd.read_csv('hospital.csv')
# table_2 = pd.read_csv('table_2.csv')
table_3 = pd.read_csv('hospital_mapping.csv')
table1_colums = list(table_1.columns)
table_new_columns = list(table_3 .columns)
table1_eg = table_1.head(3).values.tolist()
# 构建消息
message_template = "I now have one table, the columns of table 1 are named: {table1_columns}. The first 3 rows of this table are: {table1_eg}.I want to map it to a new table 3. And the columns of table 3 are named {table_new_columns}. Please give the mapping method according to your understanding of the column names and context of each table,some columns may be merged or truncated and tell me why you do this.Give the code of column mapping, and package it into a function map_tables, remember to import the package you need"
# 使用字符串的格式化方法,将实际的列名填充到消息中
message = message_template.format(
table1_eg = table1_eg,
table1_columns=table1_colums, # 假设表1的列名为 columnA, columnB, columnC
# table2_columns=table2_colums,
table_new_columns= table_new_columns # 假设新表的列名为 columnX, columnY, columnZ
)
# 打印消息
# print(message)
import os
import openai
# 读取环境变量 "OPENAI_API_KEY"
openai.api_key = os.getenv("OPENAI_API_KEY")
# 创建一个 GPT-3 请求
response = openai.ChatCompletion.create(
model="gpt-3.5-turbo",
messages=[
{"role": "system", "content": "You are a data analyst, please analyze according to the content I provide"},
{"role": "user", "content": message}
]
)
# 输出 GPT-3 的回答
print(response.choices[0].message)
print(response.choices[0].message["content"].strip())
# Separate text and code parts
content = response.choices[0].message["content"].strip()
text_part = content.split("```")[0] # Extracting text before the first occurrence of ```
code_part = "```" + content.split("```")[1] + "```"
def remove_first_line(text):
# 找到第一个换行符的位置
first_newline = text.find('\n')
if first_newline != -1:
# 使用切片操作删除第一行并返回结果
return text[first_newline+1:]
else:
# 如果没有换行符,则返回空字符串或原始字符串,取决于您的需求
return ''
def remove_last_line(text):
# 从右侧开始拆分字符串
lines = text.rsplit('\n', 1)
print(lines)
# 选择保留拆分结果中的第一个子字符串,即除了最后一行之外的所有内容
return lines[0]
after_code = remove_first_line(code_part)
# 将提取出的代码保存到一个.py文件中
with open("extracted_code_student.py", "w") as file:
file.write(after_code.strip("```"))
print("代码已保存到 extracted_code_student.py 文件中")
from extracted_code_student import map_tables
# b = table_1.values.tolist()
a = map_tables(table_1)
a.to_csv('new_hospital.csv')
实验结果
后续几种映射方式代码省略,只展示prompt和结果