import openpyxl
import time
time1 = time.time()
wb = openpyxl.load_workbook('files/某视频网站运营数据.xlsx')
sheet1 = wb.active
max_row = sheet1.max_row
max_column = sheet1.max_column
print('----------------------------1题----------------------------')
for column in range(2, max_row + 1):
if sheet1.cell(column, 6).value > 100000:
print(
f'视频ID:{sheet1.cell(column, 1).value},标题:{sheet1.cell(column, 2).value},赞数:{sheet1.cell(column, 6).value}')
print('----------------------------2题----------------------------')
if 'new_sheet' in wb.sheetnames:
sheet2 = wb['new_sheet']
else:
sheet2 = wb.create_sheet('new_sheet')
s = []
for i in range(1, max_row + 1):
if sheet1.cell(i, 1).value not in s:
for j in range(1, max_column + 1):
s.append(sheet1.cell(i, j).value)
for i in range(1, max_row + 1):
for j in range(1, max_column + 1):
if s:
sheet2.cell(i, j).value = s[0]
del s[0]
print('----------------------------3题----------------------------')
likes = []
print('点赞数排前10名的视频信息:')
max_row_two = sheet2.max_row
max_column_two = sheet2.max_column
for i in range(2, max_row_two + 1):
likes.append(sheet2.cell(i, 6).value)
likes.sort(reverse=True)
for x in range(2, max_row_two + 1):
if sheet2.cell(x, 6).value in likes[0:10]:
for j in range(1, max_column_two + 1):
print(f"{sheet2.cell(x, j).value}")
print()
print('----------------------------4题----------------------------')
channels = []
for i in range(2, max_row + 1):
s = sheet1.cell(i, 3).value
channels.append(s)
channels_set = set(channels)
for j in channels_set:
print(f'频道:{j} 视频数量:{channels.count(j)}')
print('----------------------------5题----------------------------')
views = []
for i in range(2, max_row + 1):
views.append(int(sheet1.cell(i, 5).value))
x = sorted(views, reverse=True)
for j in range(2, max_row + 1):
if sheet1.cell(j, 5).value == x[0]:
print(f'排名第一视频的ID:{sheet1.cell(j, 1).value}')
print(f'标题:{sheet1.cell(j, 2).value}')
print(f'观看数量:{sheet1.cell(j, 5).value}')
print(f'点赞数量:{sheet1.cell(j, 6).value}')
print(f'评论数量:{sheet1.cell(j, 8).value}')
print()
if sheet1.cell(j, 5).value == x[1]:
print(f'排名第二视频的ID:{sheet1.cell(j, 1).value}')
print(f'标题:{sheet1.cell(j, 2).value}')
print(f'观看数量:{sheet1.cell(j, 5).value}')
print(f'点赞数量:{sheet1.cell(j, 6).value}')
print(f'评论数量:{sheet1.cell(j, 8).value}')
print()
if sheet1.cell(j, 5).value == x[2]:
print(f'排名第三视频的ID:{sheet1.cell(j, 1).value}')
print(f'标题:{sheet1.cell(j, 2).value}')
print(f'观看数量:{sheet1.cell(j, 5).value}')
print(f'点赞数量:{sheet1.cell(j, 6).value}')
print(f'评论数量:{sheet1.cell(j, 8).value}')
print()
print('----------------------------6题----------------------------')
hot_channel = []
for i in range(2, max_row + 1):
count = int(sheet1.cell(i, 5).value) + int(sheet1.cell(i, 6).value) + int(sheet1.cell(i, 8).value)
hot_channel.append(count)
x1 = sorted(hot_channel, reverse=True)
hot_channel1 = x1[0:10]
for i in range(2, max_row + 1):
if (int(sheet1.cell(i, 5).value) + int(sheet1.cell(i, 6).value) + int(sheet1.cell(i, 8).value)) in hot_channel1:
print(f'前10名热门频道:{sheet1.cell(i, 3).value}')
print(
f'播放数+赞数+评论数:{int(sheet1.cell(i, 5).value) + int(sheet1.cell(i, 6).value) + int(sheet1.cell(i, 8).value)}')
print()
wb.save('files/某视频网站运营数据.xlsx')
print()
print('---------------------------运行时间------------------------------')
print(f'运行时间为:{time.time() - time1:.2f}秒')