day20周末作业

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


# 1. 找出点赞数在100000次以上的视频ID、标题和赞数
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}')


# 2. 按照视频ID唯一的原则对原数据去重，创建一个新的表保存已经去重后的所有数据
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]


# 3. 从已经去重的数据中找出点赞数排前10名的视频信息
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()


# 4. 打印每个频道对应的发布的视频数量
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)}')


# 5. 打印热度最高前三个视频的ID、标题、观看数量、点赞数量和评论数量
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()


# 6. 根据上面的结果找出热门频道（“播放数+赞数+评论数”总和最高的前10名）。
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}秒')