엑셀파일이 있는 폴더에서 다음 코드파일을 저장 후 실행하면 됩니다.
import bardapi
import os
# API key 설정
os.environ['_BARD_API_KEY'] = "ZAgyrC_fN_Wf3RWITxC6eoqU8ckWji9tfCaWsOGg7O_x_ZEHWP3okJ_RmTKeWouOH_aW0w."
# Bard 인스턴스 생성
bard = bardapi.core.Bard()
def load_text(filename):
with open(filename, 'r', encoding='utf-8') as file:
text = file.read()
return text
text = load_text('bard.txt')
while True:
user_question = input("Please enter your question: ")
# Bard API로부터 답변 받기
response = bard.get_answer(user_question) # 여기서 'text'는 질문과 관련된 컨텍스트입니다.
print(f"Answer: {response['choices'][0]['content']}")
import openai
openai.api_key = '' # 실제 키로 바꿔주세요.
# 사용자로부터 입력을 받습니다.
message = input("Enter your message: ")
# 사용자 입력을 기반으로 AI 모델을 호출합니다.
response = openai.ChatCompletion.create(
model="gpt-3.5-turbo", # 사용 가능한 모델 이름으로 변경해야 합니다.
messages=[
{"role": "system", "content": "You are a helpful assistant."},
{"role": "user", "content": message}
]
)
# AI 모델의 응답을 출력합니다.
print(response['choices'][0]['message']['content'])
질문 답변 1쌍인 경우
import openpyxl
import os
os.chdir('C:\\\\Users\\\\tngus\\\\text')
print(os.getcwd())
class Conversation:
def __init__(self, contentName, contentType, question, answer):
self.contentName = contentName
self.contentType = contentType
self.question = question
self.answer = answer
def __str__(self):
return "질문: " + self.question + "\\n답변: " + self.answer + "\\n"
wb = openpyxl.load_workbook('goo1.xlsx')
ws = wb.active
conversations = []
for r in ws.rows:
if len(r) >= 4:
c = Conversation(r[0].value, r[1].value, r[2].value, r[3].value)
conversations.append(c)
wb.close()
for c in conversations:
print(str(c))
print('총 ', len(conversations), '개의 대화가 존재합니다.')
i = 1
prev = str(conversations[0].contentName) + str(conversations[0].contentType)
f = open(prev + '.json', 'w', encoding='UTF-8')
f.write('{ "id": "10d3155d-4468-4118-8f5d-15009af446d0", "name": "' + prev + '", "auto": true, "contexts": [], "responses": [ { "resetContexts": false, "affectedContexts": [], "parameters": [], "messages": [ { "type": 0, "lang": "ko", "speech": "' + conversations[0].answer + '" } ], "defaultResponsePlatforms": {}, "speech": [] } ], "priority": 500000, "webhookUsed": false, "webhookForSlotFilling": false, "fallbackIntent": false, "events": [] }')
f.close()
f = open(prev + '_usersays_ko.json', 'w', encoding='UTF-8')
f.write("[")
f.write('{ "id": "3330d5a3-f38e-48fd-a3e6-000000000001", "data": [ { "text": "' + conversations[0].question + '", "userDefined": false } ], "isTemplate": false, "count": 0 }')
while True:
if i >= len(conversations):
f.write("]")
f.close()
break;
c = conversations[i]
if prev == str(c.contentName) + str(c.contentType):
f.write(',{ "id": "3330d5a3-f38e-48fd-a3e6-000000000001", "data": [ { "text": "' + c.question + '", "userDefined": false } ], "isTemplate": false, "count": 0 }')
else:
f.write("]")
f.close()
prev = str(c.contentName) + str(c.contentType)
f = open(prev + '.json', 'w', encoding='UTF-8')
f.write('{ "id": "10d3155d-4468-4118-8f5d-15009af446d0", "name": "' + prev + '", "auto": true, "contexts": [], "responses": [ { "resetContexts": false, "affectedContexts": [], "parameters": [], "messages": [ { "type": 0, "lang": "ko", "speech": "' + c.answer + '" } ], "defaultResponsePlatforms": {}, "speech": [] } ], "priority": 500000, "webhookUsed": false, "webhookForSlotFilling": false, "fallbackIntent": false, "events": [] }')
f.close()
f = open(prev + '_usersays_ko.json', 'w', encoding='UTF-8')
f.write("[")
f.write('{ "id": "3330d5a3-f38e-48fd-a3e6-000000000001", "data": [ { "text": "' + c.question + '", "userDefined": false } ], "isTemplate": false, "count": 0 }')
i = i + 1
질문이 5개 답변이 1개인 엑셀파일을 리스트화 하는 법
import pandas as pd
import json
df = pd.read_excel('file_name.xlsx')
data = []
for response in df['Response'].unique():
temp_df = df[df['Response'] == response]
questions = temp_df['Question'].tolist()
for question in questions:
data.append({
"id": "unique-id",
"data": [
{
"text": question,
"userDefined": false
}
],
"isTemplate": false,
"count": 0,
"lang": "ko",
"updated": 0
})
질문 답변이 5:1인 경우(리스트가 생성된 경우 그 데이터로 제이슨파일 만들기)
import json
import uuid
data = [
['question 1-1', 'question 1-2', 'question 1-3', 'question 1-4', 'question 1-5', 'response 1'],
['question 2-1', 'question 2-2', 'question 2-3', 'question 2-4', 'question 2-5', 'response 2'],
# more data
]
json_data = []
for row in data:
questions = row[:-1]
response = row[-1]
for question in questions:
json_data.append({
"id": str(uuid.uuid4()), # generates a random UUID
"data": [
{
"text": question,
"userDefined": False
}
],
"isTemplate": False,
"count": 0,
"lang": "ko",
"updated": 0
})
with open('output.json', 'w', encoding='utf-8') as f:
json.dump(json_data, f, ensure_ascii=False, indent=2)
이건 완성된 코드이다.
import openpyxl
import os
import json
import uuid
os.chdir('C:\\\\text') # 파일 경로를 실제 파일 위치에 맞게 변경해야 합니다.
class Conversation:
def __init__(self, question1, question2, question3, question4, question5, answer):
self.question1 = str(question1)
self.question2 = str(question2)
self.question3 = str(question3)
self.question4 = str(question4)
self.question5 = str(question5)
self.answer = str(answer)
wb = openpyxl.load_workbook('kang4.xlsx') # 파일 이름을 실제 파일 이름에 맞게 변경해야 합니다.
ws = wb.active
conversations = []
for r in ws.rows:
if len(r) >= 6: # 여섯 개의 열을 확인
c = Conversation(r[0].value, r[1].value, r[2].value, r[3].value, r[4].value, r[5].value)
conversations.append(c)
wb.close()
def sanitize_filename(filename):
invalid_chars = ['<', '>', ':', '"', '/', '\\\\', '|', '?', '*'] # add or remove characters as needed
for char in invalid_chars:
filename = filename.replace(char, '') # replace invalid characters with ''
return filename
for i, c in enumerate(conversations):
data1 = [
{
"id": str(uuid.uuid4()),
"data": [{"text": c.question1, "userDefined": False}],
"isTemplate": False,
"count": 0,
"lang": "ko",
"updated": 0
},
{
"id": str(uuid.uuid4()),
"data": [{"text": c.question2, "userDefined": False}],
"isTemplate": False,
"count": 0,
"lang": "ko",
"updated": 0
},
{
"id": str(uuid.uuid4()),
"data": [{"text": c.question3, "userDefined": False}],
"isTemplate": False,
"count": 0,
"lang": "ko",
"updated": 0
},
{
"id": str(uuid.uuid4()),
"data": [{"text": c.question4, "userDefined": False}],
"isTemplate": False,
"count": 0,
"lang": "ko",
"updated": 0
},
{
"id": str(uuid.uuid4()),
"data": [{"text": c.question5, "userDefined": False}],
"isTemplate": False,
"count": 0,
"lang": "ko",
"updated": 0
}
]
data2 = {
"id": str(uuid.uuid4()),
"name": c.question1,
"auto": True,
"contexts": [],
"responses": [
{
"resetContexts": False,
"action": "",
"affectedContexts": [],
"parameters": [],
"messages": [
{
"type": "0",
"title": "",
"textToSpeech": "",
"lang": "ko",
"speech": [c.answer],
"condition": ""
}
],
"speech": []
}
],
"priority": 500000,
"webhookUsed": False,
"webhookForSlotFilling": False,
"fallbackIntent": False,
"events": [],
"conditionalResponses": [],
"condition": "",
"conditionalFollowupEvents": []
}
filename = sanitize_filename(c.question1)
if not filename: # if the filename is empty after sanitization
filename = f"conversation_{i}" # use a default filename
with open(f'{filename}__usersays_ko.json', 'w', encoding='utf-8') as f:
json.dump(data1, f, ensure_ascii=False, indent=2)
with open(f'{filename}_.json', 'w', encoding='utf-8') as f:
json.dump(data2, f, ensure_ascii=False, indent=2)