微模板网站建设,东莞优化网站建设,紫色风格网站,网站的域名在哪里看1. 背景 针对下面的文件data.csv#xff0c;首先根据fid进行排序#xff0c;然后分组#xff0c;使相同fid的记录放到同一个excel文件中#xff0c;并对每列重复的数据元素染上红色。
fid,user_id
-1000078398032092029,230410010036537520
-1000078398032092029,23042301…1. 背景 针对下面的文件data.csv首先根据fid进行排序然后分组使相同fid的记录放到同一个excel文件中并对每列重复的数据元素染上红色。
fid,user_id
-1000078398032092029,230410010036537520
-1000078398032092029,230423010026993942
-1000078398032092029,230505010027684603
-101241766345369238,210911010005526495
-101241766345369238,211017010017923011
-101241766345369238,230113010029633164
-101241766345369238,230514010028256452
-101241766345369238,230518010036813773
-1045165137456710,220401010038956742
-1045165137456710,220401010038956742
-1050918014514687463,210805010001898014
-1050918014514687463,210805010001898014
-111手动添加一个结束标志
2. 分组切割文件
import pandas as pd
pd.set_option(display.max_rows, None)# 根据fid对文件进行分割每个fid一个文件
def split_df_by_fid():df pd.read_csv(data.csv, dtypestr)row_split_list [] # 记录分割点索引current_fid -1000078398032092029 # 第一个fidstart 0end startcnt 1 # 记录当前是第几个分割子文件for fid in df[fid]:if fid ! current_fid:row_split_list.append((start, end))# 当前fid组写入一个新文件df[start:end].to_csv(data_split/ str(cnt) .csv, index0)cnt 1current_fid fidstart endend 1print(总文件数: , len(row_split_list))print(row_split_list)# [(0, 3), (3, 8), (8, 10), (10, 12)]输出 3. 染色-写入excel
1. 找到同列重复元素
def group_by_find_duplicate_values(group_df: pd.DataFrame, col: str) - list:value_counts group_df[col].value_counts().reset_index()return value_counts[value_counts[col] 1][index].to_list()
2. 插入图片
def inset_a_img(row_index, col_index, img_name):image_path os.path.join(data_img/, img_name.replace(/, _))h, w, *_ cv2.imread(image_path).shapescale CEIL_HEIGHT * 1.3 / hSHEET.insert_image(row_index, col_index, image_path, # x_offset可调整x轴图片偏移{x_offset: 0, y_offset: 0, x_scale: scale, y_scale: scale, positioning: 1})
3. 同列相同元素染色
# -*- coding: utf-8 -*-
import os
import cv2
import tqdm
import pandas as pd
import xlsxwriter
CEIL_HEIGHT 156def write_color():df pd.read_csv(input_file, dtypestr)for i, col in enumerate(df.columns):SHEET.write(0, i, col) # 第0行第i列插入表头字段try:for i, line in tqdm.tqdm(enumerate(df.itertuples())): # tqdm: 显示进度条temp_df df[df[fid] line.fid]for j, col in enumerate(df.columns):duplicate_values group_by_find_duplicate_values(temp_df, col)content str(df.iloc[i, j])# 染色cell_format BOOK.add_format({font_color: red if (content in duplicate_values and j 0) else black})if col not in (face_path, ocr_path):# SHEET.write(*(i 1, j), content, cell_format)SHEET.write(i1, j, content, cell_format)else:if col face_path and not pd.isna(line.face_path):inset_a_img(i1, j, line.face_path) # 为Nan的置空不写入图片if col ocr_path and not pd.isna(line.ocr_path):inset_a_img(i1, j1-1, line.ocr_path)BOOK.close()except Exception as e:print(e)if __name__ __main__:for file in os.listdir(data_split):input_file data_split/ fileoutput_file data_split_xlsx/ file.replace(csv, xlsx)BOOK xlsxwriter.Workbook(output_file)SHEET BOOK.add_worksheet(sheet1)SHEET.set_default_row(CEIL_HEIGHT)SHEET.set_column(0, 60, 25) # 分别为要修改的起始列终止列设置的列宽write_color()输出
