python - 嵌套到 excel 的字典
问题描述
我有一个具有这些值的字典：
{'Page': ['5', '3', '2'], 'PageSize': [1, 1, 1], 'SolvePage': [0, 0, 0], 'Subject': ['None', 'None', 'None'], 'QuestionsNumbers': ['0', '0', '0'], 'Comment': ['None', 'None', 'None'], 'Date': ['15:51:07', '15:51:04', '15:51:03'], 'TotalPages': [0, 0, 0, 3], 'TotalSolvesPages': [0, 0, 0, 0], 'TotalLeftPages': [0, 0, 0, 3]}]
我想通过键的每个名称将这个dict添加到excel中，得到一个表名，它的值得到列的值等等。所以最后它应该在excel中看起来像这样：
请帮我在 python 中获取代码以从这个字典创建 excel 文件
我在代码中使用的内容：
import datetime
import os
import pandas as pd
df = {
'Page': [],
'PageSize': [],
'SolvePage': [],
'Subject': [],
'QuestionsNumbers': [],
'Comment': [],
'Date': [],
'TotalPages': [],
'TotalSolvesPages': [],
'TotalLeftPages': []
}
temp_da = {
'Page': [],
'PageSize': [],
'SolvePage': [],
'Subject': [],
'QuestionsNumbers': [],
'Comment': [],
'Date': [],
'TotalPages': [],
'TotalSolvesPages': [],
'TotalLeftPages': []
}
temp_index_in_page = []
df_temp = []
tempTotalPage = {0}
tempTotalSolvesPage = {0}
tempTotalLeftPage = {0}
answer_1 = True
answer_2 = True
Error_Mas = ' ERROR! You cannot enter Dot/Space/Empty value between page or in the page syntax:\n'\
' 1.If you want to enter more then one page:\n'\
' Try to enter - between each page - for example 15-16.\n'\
' 2.If you want to enter single page:\n'\
' Try to enter just the page number - for example 15'\
' Reopen the program...'
def get_index_item(key, a_list):
a_list = list(a_list)
index = a_list.index(key)
if not index:
return 0
else:
return index
def get_last_item(a_list):
if not a_list:
return 0
else:
tempVar = len(a_list) + 1
return tempVar
def get_last_item_v2(a_list):
if not a_list:
return 0
else:
tempVar = len(a_list)-1
return tempVar
def get_highest_max_substrac_min(var):
for each in var:
if '-' in each:
page1, page2 = each.split('-')
page1 = int(page1)
page2 = int(page2)
if page1 > page2:
Max_int = int(page1)
Min_int = int(page2)
calc = Max_int - Min_int
if calc == 0:
calc = 1
return calc
elif calc != 0:
return calc
else:
return 1
elif page1 < page2:
Max_int = int(page2)
Min_int = int(page1)
calc = Max_int - Min_int
if calc == 0:
calc = 1
return calc
else:
return calc
elif page1 == page2:
return 1
else:
return 1
def get_sorted_numbers(key):
if '-' in key:
page1, page2 = key.split('-')
Max = max(page1, page2)
Min = min(page1, page2)
Max_int = int(Max)
Min_int = int(Min)
to_min_and_max_value_sorted = f'{Min_int}-{Max_int}'
return to_min_and_max_value_sorted
else:
return key
def total_TotalLeftPages():
global tempTotalLeftPage
tempCalc = []
for T_P, T_SP in zip(df['TotalPages'], df['TotalSolvesPages']):
T_P = int(T_P)
T_SP = int(T_SP)
calc = T_P - T_SP
tempCalc.append(calc)
last_item = int(get_last_item_v2(tempCalc))
calc = tempCalc[last_item]
if not df['TotalLeftPages']:
df['TotalLeftPages'].insert(0,0)
df['TotalLeftPages'].append(calc)
else:
df['TotalLeftPages'].clear()
for num,each in enumerate(df['Page']):
num = 0
df['TotalLeftPages'].append(num)
df['TotalLeftPages'].append(calc)
def total_TotalSolvesPages():
global tempTotalSolvesPage
tempSum = []
for page in df['SolvePage']:
page = int(page)
if page == 0:
page_int = 0
tempTotalSolvesPage.add(page_int)
else:
page_int = int(page)
tempTotalSolvesPage.add(page_int)
Sum = sum(tempTotalSolvesPage)
tempSum.append(Sum)
Sum = sum(tempSum)
if not df['TotalSolvesPages']:
return df['TotalSolvesPages'].append(0),df['TotalSolvesPages'].append(Sum)
elif df['TotalSolvesPages']:
# df['TotalSolvesPages'].clear()
return df['TotalSolvesPages'].append(Sum)
tempTotalSolvesPage.clear()
tempSum.clear()
def total_TotalPages():
tempList = []
for page in df['PageSize']:
page = int(page)
tempList.append(page)
Sum = sum(tempList)
if not df['TotalPages']:
return df['TotalPages'].append(0),df['TotalPages'].append(Sum)
elif df['TotalPages']:
df['TotalPages'].clear()
for num,each in enumerate(df['Page']):
num = 0
df['TotalPages'].append(num)
return df['TotalPages'].append(Sum)
def chack_page_v1(inp_page):
global df
global df_temp
if '-' in inp_page:
page1, page2 = inp_page.split('-')
Max = max(page1, page2)
Min = min(page1, page2)
Max_int = int(Max)
Min_int = int(Min)
to_min_and_max_value_sorted = f'{Min_int}-{Max_int}'
if to_min_and_max_value_sorted in df['Page']:
print(' The page you enter is already in the database, ignoring...')
else:
print(f' Adding page number : {inp_page} to database...')
df['Page'].insert(get_last_item(df['Page']), to_min_and_max_value_sorted)
df_temp.append(inp_page)
df['Page'] = sorted(df['Page'], reverse=True)
elif inp_page in df['Page']:
print(' The page you enter is already in the database, ignoring...')
else:
print(f' Adding page number : {inp_page} to database...')
df['Page'].append(inp_page)
df['Page'] = sorted(df['Page'], reverse=True)
df_temp.append(inp_page)
def page_size():
global df
global df_temp
global temp_index_in_page
var_1 = df_temp[0]
try:
if df_temp[0] in df['Page']:
var = get_highest_max_substrac_min(df_temp)
i = df['Page'].index(df_temp[0])
df['PageSize'].insert(i, var)
df['SolvePage'].insert(i, 0)
else:
if '-' in var_1:
page_enter_sorted = get_sorted_numbers(var_1)
for each in df['Page']:
if page_enter_sorted == each:
temp_list.append(df['Page'].index(each))
index_page_enter = int(temp_list[0])
var = get_highest_max_substrac_min(df_temp)
df['PageSize'].insert(index_page_enter, var)
df['SolvePage'].insert(index_page_enter, 0)
else:
last = get_last_item(df['PageSize'])
var = get_highest_max_substrac_min(df_temp)
df['PageSize'].insert(last, var)
df['SolvePage'].append(0)
except ValueError:
print(Error_Mas)
def page_solve():
for_while = True
temp_list_num = []
temp_list_page = []
get_range_pages = []
get_page_from_user = []
get_range_pages_get_solve_page = []
checkSumSolvePage = sum(df['SolvePage'])
if not df['SolvePage']:
print('Error! Cant solve page with non value , try to enter any page then enter the solve pages menu.')
else:
print('\nList of all the page number : ')
for num_print, page_print in enumerate(df['Page']):
print(f' For picking page number {page_print} enter the key {num_print}')
temp_list_num.append(num_print)
temp_list_page.append(page_print)
while for_while:
answer = input('Enter here you choosing : ')
try:
if answer not in str(temp_list_num):
print(' ERROR! You enter not exist page pick , pick again')
elif answer in str(temp_list_num):
if checkSumSolvePage == 0:
get_page_from_user.append(temp_list_page[int(answer)])
get_range_pages.append(df['PageSize'][int(answer)])
else:
get_page_from_user.append(temp_list_page[int(answer)])
get_range_pages.append(df['PageSize'][int(answer)])
get_range_pages_get_solve_page.append(df['SolvePage'][int(answer)])
value = int(get_range_pages[0]) - int(get_range_pages_get_solve_page[0])
get_range_pages.clear()
get_range_pages.append(value)
get_range_pages = get_range_pages[0]
get_page_from_user = get_page_from_user[0]
print('The pages', get_page_from_user)
print('The calc pages is', get_range_pages)
get_pages_substrac = input(f'How many pages did you finish in page {get_page_from_user}? ')
get_pages_substrac = int(get_pages_substrac)
if get_range_pages < get_pages_substrac:
print(
f'your input for finish in pages in more then the calc of the pages : the calc between the '
f'pages is : {get_range_pages}')
elif get_range_pages == get_pages_substrac:
if '-' in get_page_from_user:
var = df['SolvePage'][int(answer)]
df['SolvePage'].pop(int(answer))
new_var = var + get_pages_substrac
print(new_var)
df['SolvePage'].insert(int(answer), new_var)
else:
df['SolvePage'].pop(int(answer))
df['SolvePage'].insert(int(answer), 1)
else:
var = df['SolvePage'][int(answer)]
df['SolvePage'].pop(int(answer))
new_var = var + get_pages_substrac
df['SolvePage'].insert(int(answer), new_var)
for_while = False
except ValueError:
print('ERROR ! You enter the wrong key , try again')
a = 0
def save_new_excel_file(path):
try:
if os.path.isdir(path):
global a
global df
global temp_da
path_for_stand_along_excel = os.path.join(path, 'HomeWorkPage.xlsx')
da = pd.DataFrame.from_dict(df,orient='index')
Try = True
while Try:
try:
if os.path.exists(path_for_stand_along_excel):
os.remove(path_for_stand_along_excel)
writeToExcel = pd.ExcelWriter(path_for_stand_along_excel)
da.to_excel(writeToExcel, index=False)
writeToExcel.save()
print('create new excel file')
Try = False
else:
writeToExcel = pd.ExcelWriter(path_for_stand_along_excel)
da.to_excel(writeToExcel, index=False)
writeToExcel.save()
print('create new excel file')
Try = False
except PermissionError:
print('Data not attached to open excel , try reopen the program and run ')
answer = input('Re-try again ? (y/n)')
if answer == 'y':
pass
elif answer == 'n':
Try = False
except FileNotFoundError:
print('seems like the path is not exsist in your PC , try to run again with the correct path')
while True:
print('The syntax to insert pages is :\n'
' 1.If you want to enter more then one page:\n'
' Try to enter - between each page - for example 15-16.\n'
' 2.If you want to enter single page:\n'
' Try to enter just the page number - for example 15\n')
print('---\n'
'To Insert New HomeWork pages enter (1)\n'
'To insert solution pages enter (2)\n'
'---')
temp_list = []
while answer_1:
main_answer = input('\nPlease Enter your answer here (1/2) : ')
if main_answer == '1':
try:
page_enter = input(' Enter the page numbers please : ')
if page_enter in df['Page'] or '.' in page_enter or page_enter == '':
print(Error_Mas)
else:
chack_page_v1(str(page_enter))
page_size()
df_temp.clear()
temp_list.clear()
Now = datetime.datetime.now()
date_correct = Now.strftime("%H:%M:%S")
page_enter_sorted = get_sorted_numbers(page_enter)
for each in df['Page']:
if page_enter_sorted == each:
temp_list.append(df['Page'].index(each))
index_page_enter = int(temp_list[0])
df['Date'].insert(index_page_enter,date_correct)
subject_enter = input(' Enter your subject enter please : Default is == (None) ')
if subject_enter == '':
df['Subject'].insert(index_page_enter,'None')
else:
df['Subject'].append(subject_enter)
question = input(f' Any specific question ? Default is == (None) ')
if question == '':
df['QuestionsNumbers'].insert(index_page_enter,'0')
else:
df['QuestionsNumbers'].insert(index_page_enter,question)
comment = input(' Any comment to this page ? Default is == (None)')
if comment == '':
df['Comment'].insert(index_page_enter,'None')
else:
df['Comment'].insert(index_page_enter,comment)
print(f' **Page number {page_enter} successfully added to excel file**')
total_TotalPages()
total_TotalSolvesPages()
total_TotalLeftPages()
save_new_excel_file(r'D:\Try\New folder(3)')
print(df)
except IndexError:
print(Error_Mas)
if main_answer == '2':
page_solve()
save_new_excel_file(r'D:\Try\New folder(3)')
解决方案
对我来说，使用构造函数进行字典理解Series，因为某些列表具有不同的长度：
d = {'Page': ['5', '3', '2'], 'PageSize': [1, 1, 1], 'SolvePage': [0, 0, 0], 'Subject': ['None', 'None', 'None'], 'QuestionsNumbers': ['0', '0', '0'], 'Comment': ['None', 'None', 'None'], 'Date': ['15:51:07', '15:51:04', '15:51:03'], 'TotalPages': [0, 0, 0, 3], 'TotalSolvesPages': [0, 0, 0, 0], 'TotalLeftPages': [0, 0, 0, 3]}
da = pd.DataFrame({k: pd.Series(v) for k, v in d.items()})
print (da)
Page PageSize SolvePage Subject QuestionsNumbers Comment Date \
0 5 1.0 0.0 None 0 None 15:51:07
1 3 1.0 0.0 None 0 None 15:51:04
2 2 1.0 0.0 None 0 None 15:51:03
3 NaN NaN NaN NaN NaN NaN NaN
TotalPages TotalSolvesPages TotalLeftPages
0 0 0 0
1 0 0 0
2 0 0 0
3 3 0 3
df.to_excel(file, index=False)
推荐阅读
- android - 以编程方式设置值时停止观察对象通知
- reactjs - 容器滚动上的关闭菜单
- iis - 如何使用动态源 URL 重定向到此 URL？
- tsql - 是否可以使用 2 个执行 SQL 任务在 SSIS 中使用事务包装容器？
- c# - 将数据集（在 iframe 页面中）绑定到中继器（在父页面中）
- mysql - 如何使用 Diesel 生成和解析原始 SQL 查询？
- asp.net-core - 我不使用 Kestrel 作为 Web 服务器并在代码中启用 IIS 集成，但显示服务器是 Kestrel 的响应标头
- c# - 粘贴超过 32767 的 DataGridViewTextBoxColumn 文本
- javascript - 过滤香草 javascript 待办事项列表中的待办事项
- amazon-web-services - 如何使我的 AWS Lambda 函数能够写入 Firehose？