如何在Excel中将提取的列从Excel中分离到不同的列表或组中 [英] How to separate extracted column from excel into different lists or groups in Python
问题描述
我已经编写了代码来获取我需要的数据列到excel中,但是数据之间是用分隔的空单元格。
我想把单元格值不是None的那些数据分组到19个国家,这样我就可以用它来计算平均值和标准差对于19个国家。
我不想使用列表切片进行硬编码。相反,我想要将这些整数保存到一个列表或清单使用循环,但我不知道如何,因为这是我的第一个项目与Python。
这是我的代码:
#Read PCT排名项目批准结果
#Beta
导入openpyxl
wb = openpyxl.load_workbook('PCT PCT排名项目已批准results.xlsx',data_only = True)
sheet = wb.get_sheet_by_name('IP by IP companies')
row_counter = sheet.max_row
column_counter = sheet.max_column
print(row_counter)
print(column_counter)
#在专利申请专栏并尝试使用空单元格来标记循环,以便在到达下一个非空单元格之前附加/存储数字列表,并在每次发生时重复此操作(期待19次)
list = []
为行范围(4,sheet.max_row +1):
patent = sheet ['I'+ str(row)]。
print(patent)
if patent = =无:
list.append (专利)
print(list)
这是Python的输出给你一个可视化
第一栏:
412
14
493
488
339
273
238
226
200
194
153
164
151
126
无
120
无
无
133
77
62
79
24
0
30
20
16
0
6
9
11
无
无
无
无
608
529
435
320
266
264
200
272
134
113
73
23
12
52
21
你应该使用csv库!
I am reading a column from an excel file using openpyxl.
I have written code to get the column of data I need into excel but the data is separated by empty cells.
I want to group these data wherever the cell value is not None into 19 sets of countries so that I can use it later to calculate the mean and standard deviation for the 19 countries.
I don't want to hard code it using list slices. Instead I want to save these integers to a list or list of lists using a loop but im not sure how to because this is my first project with Python.
Here's my code:
#Read PCT rankings project ratified results
#Beta
import openpyxl
wb=openpyxl.load_workbook('PCT rankings project ratified results.xlsx', data_only=True)
sheet=wb.get_sheet_by_name('PCT by IP firms')
row_counter=sheet.max_row
column_counter=sheet.max_column
print(row_counter)
print(column_counter)
#iterating over column of patent filings and trying to use empty cells to flag loop for it to append/store list of numbers before reaching the next non empty cell and repeat this everytime it happens(expecting 19 times)
list=[]
for row in range(4,sheet.max_row +1):
patent=sheet['I'+str(row)].value
print(patent)
if patent == None:
list.append(patent)
print(list)
This is the output from Python giving you a visualisation of what I am trying to do.
Column I: 412 14 493 488 339 273 238 226 200 194 153 164 151 126 None 120 None None 133 77 62 79 24 0 30 20 16 0 6 9 11 None None None None 608 529 435 320 266 264 200 272 134 113 73 23 12 52 21
You should use csv library !
这篇关于如何在Excel中将提取的列从Excel中分离到不同的列表或组中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!