python - 将 python 列表导出到 excel 列中
问题描述
from collections import Counter
import pandas as pd
import string
import xlwt
from xlwt import Workbook
wb=Workbook()
sheet2=wb.add_sheet('Sheet2')
sheet2 = wb.add_sheet("Sheet 2", cell_overwrite_ok=True)
sheet2.title="FINAL RESULTS"
df=pd.read_excel("Book2.xlsx", sheet_name=0)
df=df.astype('object')
df.info()
df_c1=df['Signal']
df_c2=df['DCS number']
list1_with_letters=list(df_c1)
list2_with_letters=list(df_c2)
new_list1=[]
new_list2=[]
def duplicates(lst, item):
return [i for i, x in enumerate(lst) if x == item]
#stripping the characters for COMOS list
for x in list1_with_letters:
x=str(x)
new_x=''.join(filter(str.isdigit, x))
new_list1.append(new_x)
#stripping the characters for DCS list
for y in list2_with_letters:
y=str(y)
new_y=''.join(filter(str.isdigit, y))
new_list2.append(new_y)
new_list1 = list(filter(None, new_list1))
seen = set()
#we take out the duplicates of the COMOS list
new_list1_in_order= []
for item in new_list1:
if item not in seen:
seen.add(item)
new_list1_in_order.append(item)
for elem1 in new_list1_in_order: #loop through COMOS list
index_duplicates_DCS=duplicates(new_list2,elem1)
matched= [list2_with_letters[i] for i in index_duplicates_DCS]
matched=str(matched)
elem1_str=str(elem1) #convert the found element from new_list 2 into a string type
print(elem1_str+ "-->"+ matched)
#CODE WORKS UP TO HERE
size_matched=len(matched)
size_new_list1_in_order=len(new_list1_in_order)
for x in range(size_new_list1_in_order):
for y in range(size_matched):
sheet2.write(x,y,matched[y])
wb.save('sample_book.xls')
- 如果您一直运行代码直到#CODE WORKS up to here,您将获得以下示例输出:
690205-->['AAH690205', 'AHH690205', 'LI690205', 'TDX690205']
690206-->['AAH690206', 'AHH690206', 'LI690206', 'TAHH690206', 'THH690206', 'TI690206', 'TNHH690206']
我现在要做的是将此数据打印到这样的 excel 表中:
Column1 Column 2
690205 AAH690205
AHH690205
LI690205
TDX690205
690206 AAH690206
LI690206
TAHH690206
THH690206
TI690206
and so on and so forth
我意识到代码写得不好(第一次编码),但是有人可以在#CODE WORKS UP TO HERE 之后帮助我实现部分
解决方案
为此,我使用字典来组织信息。
690205-->['AAH690205', 'AHH690205', 'LI690205', 'TDX690205']
690206-->['AAH690206', 'AHH690206', 'LI690206', 'TAHH690206', 'THH690206', 'TI690206', 'TNHH690206']
key
箭头前的数字在字典的每个成员中用作:
69020
690206
value
每个列表中的数字作为每个列表中的数字存储key
在字典中。为了解释这一点,这就是我的字典的样子:
columns = {
690205 : ['AAH690205', 'AHH690205', 'LI690205', 'TDX690205'],
690206 : ['AAH690206', 'AHH690206', 'LI690206', 'TAHH690206', 'THH690206', 'TI690206', 'TNHH690206'],
}
为了将每一个都写key
在适当的位置,我使用了一个变量,该变量将设置为value
前一个的长度key
。values
编写起来要容易得多,因为我只需要将它们存储在 a 中并对其list
进行迭代list
。
import xlwt
from xlwt import Workbook
wb = Workbook()
sheet = wb.add_sheet('Sheet 1', cell_overwrite_ok=True)
# write columns that will always be there
sheet.write(0, 0, 'Column 1')
sheet.write(0, 1, 'Column 2')
columns = {
690205 : ['AAH690205', 'AHH690205', 'LI690205', 'TDX690205'],
690206 : ['AAH690206', 'AHH690206', 'LI690206', 'TAHH690206', 'THH690206', 'TI690206', 'TNHH690206'],
}
# key_list is used to store each key in order
key_list = []
for key in columns:
key_list.append(key)
# key_index needs to start at one to prevent overriding of the column names (Column 1 and Column 2)
# key_index will be used to place each key in their correct spot
key_index = 1
for key in key_list:
# writes the key at the correct key_index
sheet.write(key_index, 0, key)
# gets the length of the value for the key
key_value_length = len(columns[key])
# adds key_value_length to key_index to put the next key at the correct place
key_index += key_value_length
# values_list is used to store all of the values of each key in order
value_list = []
for values in columns.values():
for value in values:
value_list.append(value)
# getting index number of the value in the value_list
index = value_list.index(value)
# have to add one to the index because the indexes for the values will start at 1, not 0. This prevents overriding of the cell 'Column 2'
sheet.write(index+1 , 1 ,value)
wb.save('examplesheet.xls')
推荐阅读
- rstudio - 无法从 RStudio 用西班牙语编织 PDF 文件(在 Centos 7 上)
- django - Django - 序列化程序返回更新数据但不修改数据库
- selenium - 如何使用 selenium 在 Java 中的 firefox 驱动程序中添加扩展
- google-sheets - vlookup(importrange) 函数在另一个电子表格的查询结果表中找不到数据
- azure - 在部署 arm 模板之前检查 Azure VM 名称是否存在
- java - 使用正则表达式从文件中读取文本
- python-3.x - 如何在 Gekko 中实现缺失数据的动态参数估计?
- flutter - url 启动器插件不起作用的原因可能是什么?
- javascript - 即使表中有数据,制表器 table.getRow() 也会返回 false
- android - 在Android设备中测试统一游戏会一直停止它(Unity3D)?