1.背景
移动端上线httpdns后，搜集了一部分数据，每行有20多个字段，其中有
-
• 'ms',
-
• 'isHttpDns',
-
• 'dnsStart',
-
• 'dnsEnd',
-
• 'country_code'字段
-
-
如何统计各国家dns效果对比呢？输出每个国家未使用
httpdns和已使用httpdns的耗时的中位数，平均数，最大值，最小值，P70，P80，P90，P95，P99，样本总数.
2.筛选样本集
从src_data.xlsx的源文件中读取特定的列，并根据预定义的条件筛选数据，然后将其写入不同的Excel文件中
def filter_data_to_excels():
use_cols =['ms','isHttpDns','dnsStart','dnsEnd','country_code']
df = pd.read_excel('src_data.xlsx',usecols=use_cols)
data_items =[
{'country_code':'CN','isHttpDns':0},
{'country_code':'CN','isHttpDns':1},
{'country_code':'ID','isHttpDns':0},
{'country_code':'ID','isHttpDns':1},
{'country_code':'IN','isHttpDns':0},
{'country_code':'IN','isHttpDns':1},
{'country_code':'IR','isHttpDns':0},
{'country_code':'IR','isHttpDns':1},
{'country_code':'JP','isHttpDns':0},
{'country_code':'JP','isHttpDns':1},
{'country_code':'KR','isHttpDns':0},
{'country_code':'KR','isHttpDns':1},
{'country_code':'PK','isHttpDns':0},
{'country_code':'PK','isHttpDns':1},
{'country_code':'US','isHttpDns':0},
{'country_code':'US','isHttpDns':1},
]
for item in data_items:
print(f"{item}开始筛选数据集合")
filtered_data = df[(df["country_code"]== item['country_code'])&(df["isHttpDns"]== item['isHttpDns'])]
output_filename =f"filter_excel_files/{item['country_code']}_{item['isHttpDns']}.xlsx"
filtered_data.to_excel(output_filename, index=False)
print(f'筛选结果已写入 {output_filename}')
3.对样本集输出统计数据
def csv_result(source, dest):
file_name = os.path.basename(source)# 获取文件名
file_name_ext = os.path.splitext(file_name)[0]# 去掉文件扩展名
parts = file_name_ext.split('_')# 使用下划线分割文件名
country = parts[0]# 获取路径前缀
httpdns_flag = parts[1]# 获取路径后缀
df = pd.read_excel(source)
total_rows = df.shape[0]# 获取总行数
# 确保dnsStart和dnsEnd列是整数类型，以便进行计算
df['dnsStart']= pd.to_numeric(df['dnsStart'], errors='coerce')
df['dnsEnd']= pd.to_numeric(df['dnsEnd'], errors='coerce')
# 计算每一行的dnsEnd - dnsStart的值
df['dns_duration']= df['dnsEnd']- df['dnsStart']
# 将计算结果逐行添加到每一行末尾
df = df.assign(**{'dns_duration': df['dns_duration']})
# 保存更新后的DataFrame到新的Excel文件
df.to_excel(dest, index=False)
if "dns_duration"in df.columns:
# 计算中位数
median = df["dns_duration"].median()
# 计算平均数
average = df["dns_duration"].mean()
# 计算最大值和最小值
max_value = df["dns_duration"].max()
min_value = df["dns_duration"].min()
# 计算各分位数
q70 = df["dns_duration"].quantile(0.70)
q80 = df["dns_duration"].quantile(0.80)
q90 = df["dns_duration"].quantile(0.90)
q95 = df["dns_duration"].quantile(0.95)
q99 = df["dns_duration"].quantile(0.99)
# 中位数 平均数 最大值 最小值 70分位数 80分位数 90分位数 95分位数 99分位数
print(f"{country} {httpdns_flag} {median:.2f} {average:.2f} {max_value:.2f} {min_value:.2f} {q70:.2f} {q80:.2f} {q90:.2f} {q95:.2f} {q99:.2f} ")
status ="未使用"
if httpdns_flag =='1':
status ="已使用"
return {
'国家': country,
'HTTPDNS使用情况': status,
'中位数(单位：ms)':round(median,2),
'平均数(单位：ms)':round(average,2),
'最大值(单位：ms)':round(max_value,2),
'最小值(单位：ms)':round(min_value,2),
'P70(单位：ms)':round(q70,2),
'P80(单位：ms)':round(q80,2),
'P90(单位：ms)':round(q90,2),
'P95(单位：ms)':round(q95,2),
'P99(单位：ms)':round(q99,2),
'样本总数(单位:条)': total_rows
}
else:
print("最后一列不是 'dnsEnd - dnsStart'，请检查文件或列名。")
return None
4.输出各样本集统计数据到最终表格
def main():
filter_data_to_excels()
file_list =["CN_0.xlsx","CN_1.xlsx",
"ID_0.xlsx","ID_1.xlsx",
"IN_0.xlsx","IN_1.xlsx",
"IR_0.xlsx","IR_1.xlsx",
"JP_0.xlsx","JP_1.xlsx",
"KR_0.xlsx","KR_1.xlsx",
"PK_0.xlsx","PK_1.xlsx",
"US_0.xlsx","US_1.xlsx"
]
statistics_result =[]
for file in file_list:
source_dir =f"filter_excel_files/{file}"
dest_dir =f"result_excels/{file}"
print(f"-----------{source_dir}----{dest_dir}-------------")
result = csv_result(source_dir, dest_dir)
if result:
statistics_result.append(result)
print(f"statistics_result:{statistics_result}")
# 创建DataFrame
df = pd.DataFrame(statistics_result)
# 将数据写入Excel文件
output_filename ='httpdns_country_statistics.xlsx'
df.to_excel(output_filename, index=False)
print(f'统计数据已输出 {output_filename}')
5.效果
{'country_code':'CN','isHttpDns':0}开始筛选数据集合
筛选结果已写入 filter_excel_files/CN_0.xlsx
{'country_code':'CN','isHttpDns':1}开始筛选数据集合
筛选结果已写入 filter_excel_files/CN_1.xlsx
{'country_code':'ID','isHttpDns':0}开始筛选数据集合
筛选结果已写入 filter_excel_files/ID_0.xlsx
{'country_code':'ID','isHttpDns':1}开始筛选数据集合
筛选结果已写入 filter_excel_files/ID_1.xlsx
{'country_code':'IN','isHttpDns':0}开始筛选数据集合
筛选结果已写入 filter_excel_files/IN_0.xlsx
{'country_code':'IN','isHttpDns':1}开始筛选数据集合
筛选结果已写入 filter_excel_files/IN_1.xlsx
{'country_code':'IR','isHttpDns':0}开始筛选数据集合
筛选结果已写入 filter_excel_files/IR_0.xlsx
{'country_code':'IR','isHttpDns':1}开始筛选数据集合
筛选结果已写入 filter_excel_files/IR_1.xlsx
{'country_code':'JP','isHttpDns':0}开始筛选数据集合
筛选结果已写入 filter_excel_files/JP_0.xlsx
{'country_code':'JP','isHttpDns':1}开始筛选数据集合
筛选结果已写入 filter_excel_files/JP_1.xlsx
{'country_code':'KR','isHttpDns':0}开始筛选数据集合
筛选结果已写入 filter_excel_files/KR_0.xlsx
{'country_code':'KR','isHttpDns':1}开始筛选数据集合
筛选结果已写入 filter_excel_files/KR_1.xlsx
{'country_code':'PK','isHttpDns':0}开始筛选数据集合
筛选结果已写入 filter_excel_files/PK_0.xlsx
{'country_code':'PK','isHttpDns':1}开始筛选数据集合
筛选结果已写入 filter_excel_files/PK_1.xlsx
{'country_code':'US','isHttpDns':0}开始筛选数据集合
筛选结果已写入 filter_excel_files/US_0.xlsx
{'country_code':'US','isHttpDns':1}开始筛选数据集合
筛选结果已写入 filter_excel_files/US_1.xlsx
-----------filter_excel_files/CN_0.xlsx----result_excels/CN_0.xlsx-------------
CN 0 x
-----------filter_excel_files/CN_1.xlsx----result_excels/CN_1.xlsx-------------
CN 1 x
-----------filter_excel_files/ID_0.xlsx----result_excels/ID_0.xlsx-------------
ID 0 x
-----------filter_excel_files/ID_1.xlsx----result_excels/ID_1.xlsx-------------
ID 1 x
-----------filter_excel_files/IN_0.xlsx----result_excels/IN_0.xlsx-------------
IN 0 x
-----------filter_excel_files/IN_1.xlsx----result_excels/IN_1.xlsx-------------
IN 1 x
-----------filter_excel_files/IR_0.xlsx----result_excels/IR_0.xlsx-------------
IR 0 x
-----------filter_excel_files/IR_1.xlsx----result_excels/IR_1.xlsx-------------
IR 1 x
-----------filter_excel_files/JP_0.xlsx----result_excels/JP_0.xlsx-------------
JP 0 x
-----------filter_excel_files/JP_1.xlsx----result_excels/JP_1.xlsx-------------
JP 1 x
-----------filter_excel_files/KR_0.xlsx----result_excels/KR_0.xlsx-------------
KR 0 x
-----------filter_excel_files/KR_1.xlsx----result_excels/KR_1.xlsx-------------
KR 1 x
-----------filter_excel_files/PK_0.xlsx----result_excels/PK_0.xlsx-------------
PK 0 x
-----------filter_excel_files/PK_1.xlsx----result_excels/PK_1.xlsx-------------
PK 1 x
-----------filter_excel_files/US_0.xlsx----result_excels/US_0.xlsx-------------
US 0 x
-----------filter_excel_files/US_1.xlsx----result_excels/US_1.xlsx-------------
US 1 x
statistics_result:[{'国家':'CN','HTTPDNS使用情况':'未使用','中位数(单位：ms)': np.float64(1.0),'平均数(单位：ms)': np.float64(71.27),'最大值(单位：ms)': np.int64(5288),'最小值(单位：ms)': np.int64(0),'P70(单位：ms)': np.float64(12.0),'P80(单位：ms)': np.float64(31.0),'P90(单位：ms)': np.float64(60.0),'P95(单位：ms)': np.float64(213.5),'P99(单位：ms)': np.float64(2244.32),'样本总数(单位:条)':1299},{'国家':'CN','HTTPDNS使用情况':'已使用','中位数(单位：ms)': np.float64(0.0),'平均数(单位：ms)': np.float64(50.25),'最大值(单位：ms)': np.int64(4714),'最小值(单位：ms)': np.int64(0),'P70(单位：ms)': np.float64(1.0),'P80(单位：ms)': np.float64(2.0),'P90(单位：ms)': np.float64(41.0),'P95(单位：ms)': np.float64(123.0),'P99(单位：ms)': np.float64(1380.0),'样本总数(单位:条)':4727},{'国家':'ID','HTTPDNS使用情况':'未使用','中位数(单位：ms)': np.float64(1.0),'平均数(单位：ms)': np.float64(69.05),'最大值(单位：ms)': np.int64(5030),'最小值(单位：ms)': np.int64(0),'P70(单位：ms)': np.float64(7.0),'P80(单位：ms)': np.float64(27.0),'P90(单位：ms)': np.float64(79.4),'P95(单位：ms)': np.float64(238.8),'P99(单位：ms)': np.float64(1813.26),'样本总数(单位:条)':623},{'国家':'ID','HTTPDNS使用情况':'已使用','中位数(单位：ms)': np.float64(1.0),'平均数(单位：ms)': np.float64(397.51),'最大值(单位：ms)': np.int64(7259),'最小值(单位：ms)': np.int64(0),'P70(单位：ms)': np.float64(3.0),'P80(单位：ms)': np.float64(58.0),'P90(单位：ms)': np.float64(228.2),'P95(单位：ms)': np.float64(5019.8),'P99(单位：ms)': np.float64(5066.04),'样本总数(单位:条)':1645},{'国家':'IN','HTTPDNS使用情况':'未使用','中位数(单位：ms)': np.float64(1.0),'平均数(单位：ms)': np.float64(251.13),'最大值(单位：ms)': np.int64(7472),'最小值(单位：ms)': np.int64(0),'P70(单位：ms)': np.float64(28.0),'P80(单位：ms)': np.float64(95.0),'P90(单位：ms)': np.float64(563.6),'P95(单位：ms)': np.float64(2115.9),'P99(单位：ms)': np.float64(3993.84),'样本总数(单位:条)':9159},{'国家':'IN','HTTPDNS使用情况':'已使用','中位数(单位：ms)': np.float64(1.0),'平均数(单位：ms)': np.float64(112.55),'最大值(单位：ms)': np.int64(7753),'最小值(单位：ms)': np.int64(0),'P70(单位：ms)': np.float64(1.0),'P80(单位：ms)': np.float64(2.0),'P90(单位：ms)': np.float64(324.0),'P95(单位：ms)': np.float64(596.0),'P99(单位：ms)': np.float64(2247.0),'样本总数(单位:条)':24055},{'国家':'IR','HTTPDNS使用情况':'未使用','中位数(单位：ms)': np.float64(7.0),'平均数(单位：ms)': np.float64(189.21),'最大值(单位：ms)': np.int64(7036),'最小值(单位：ms)': np.int64(0),'P70(单位：ms)': np.float64(50.0),'P80(单位：ms)': np.float64(80.0),'P90(单位：ms)': np.float64(227.0),'P95(单位：ms)': np.float64(1102.35),'P99(单位：ms)': np.float64(4139.84),'样本总数(单位:条)':5244},{'国家':'IR','HTTPDNS使用情况':'已使用','中位数(单位：ms)': np.float64(408.0),'平均数(单位：ms)': np.float64(826.59),'最大值(单位：ms)': np.int64(7611),'最小值(单位：ms)': np.int64(0),'P70(单位：ms)': np.float64(653.0),'P80(单位：ms)': np.float64(889.6),'P90(单位：ms)': np.float64(1794.6),'P95(单位：ms)': np.float64(3538.8),'P99(单位：ms)': np.float64(5649.4),'样本总数(单位:条)':17693},{'国家':'JP','HTTPDNS使用情况':'未使用','中位数(单位：ms)': np.float64(1.0),'平均数(单位：ms)': np.float64(57.9),'最大值(单位：ms)': np.int64(2196),'最小值(单位：ms)': np.int64(0),'P70(单位：ms)': np.float64(9.6),'P80(单位：ms)': np.float64(29.0),'P90(单位：ms)': np.float64(82.0),'P95(单位：ms)': np.float64(216.6),'P99(单位：ms)': np.float64(1182.06),'样本总数(单位:条)':699},{'国家':'JP','HTTPDNS使用情况':'已使用','中位数(单位：ms)': np.float64(1.0),'平均数(单位：ms)': np.float64(62.94),'最大值(单位：ms)': np.int64(5719),'最小值(单位：ms)': np.int64(0),'P70(单位：ms)': np.float64(1.0),'P80(单位：ms)': np.float64(2.0),'P90(单位：ms)': np.float64(12.0),'P95(单位：ms)': np.float64(156.5),'P99(单位：ms)': np.float64(2163.35),'样本总数(单位:条)':1706},{'国家':'KR','HTTPDNS使用情况':'未使用','中位数(单位：ms)': np.float64(0.0),'平均数(单位：ms)': np.float64(33.84),'最大值(单位：ms)': np.int64(5044),'最小值(单位：ms)': np.int64(0),'P70(单位：ms)': np.float64(14.0),'P80(单位：ms)': np.float64(30.0),'P90(单位：ms)': np.float64(52.0),'P95(单位：ms)': np.float64(83.0),'P99(单位：ms)': np.float64(659.2),'样本总数(单位:条)':6216},{'国家':'KR','HTTPDNS使用情况':'已使用','中位数(单位：ms)': np.float64(0.0),'平均数(单位：ms)': np.float64(7.0),'最大值(单位：ms)': np.int64(3154),'最小值(单位：ms)': np.int64(0),'P70(单位：ms)': np.float64(1.0),'P80(单位：ms)': np.float64(1.0),'P90(单位：ms)': np.float64(3.0),'P95(单位：ms)': np.float64(33.0),'P99(单位：ms)': np.float64(100.0),'样本总数(单位:条)':13846},{'国家':'PK','HTTPDNS使用情况':'未使用','中位数(单位：ms)': np.float64(1.0),'平均数(单位：ms)': np.float64(224.79),'最大值(单位：ms)': np.int64(7119),'最小值(单位：ms)': np.int64(0),'P70(单位：ms)': np.float64(36.7),'P80(单位：ms)': np.float64(92.8),'P90(单位：ms)': np.float64(337.4),'P95(单位：ms)': np.float64(1481.85),'P99(单位：ms)': np.float64(3811.3),'样本总数(单位:条)':2252},{'国家':'PK','HTTPDNS使用情况':'已使用','中位数(单位：ms)': np.float64(1.0),'平均数(单位：ms)': np.float64(228.72),'最大值(单位：ms)': np.int64(7687),'最小值(单位：ms)': np.int64(0),'P70(单位：ms)': np.float64(1.0),'P80(单位：ms)': np.float64(2.0),'P90(单位：ms)': np.float64(283.6),'P95(单位：ms)': np.float64(1156.5),'P99(单位：ms)': np.float64(5847.9),'样本总数(单位:条)':5635},{'国家':'US','HTTPDNS使用情况':'未使用','中位数(单位：ms)': np.float64(1.0),'平均数(单位：ms)': np.float64(160.85),'最大值(单位：ms)': np.int64(7264),'最小值(单位：ms)': np.int64(0),'P70(单位：ms)': np.float64(37.0),'P80(单位：ms)': np.float64(83.6),'P90(单位：ms)': np.float64(229.0),'P95(单位：ms)': np.float64(811.4),'P99(单位：ms)': np.float64(3473.25),'样本总数(单位:条)':3058},{'国家':'US','HTTPDNS使用情况':'已使用','中位数(单位：ms)': np.float64(1.0),'平均数(单位：ms)': np.float64(276.56),'最大值(单位：ms)': np.int64(7335),'最小值(单位：ms)': np.int64(0),'P70(单位：ms)': np.float64(2.0),'P80(单位：ms)': np.float64(121.2),'P90(单位：ms)': np.float64(952.6),'P95(单位：ms)': np.float64(1605.65),'P99(单位：ms)': np.float64(4365.14),'样本总数(单位:条)':7528}]
统计数据已输出 httpdns_country_statistics.xlsx
6.其它
处理100M左右的xlsx文件，几分钟后即输出；如果是10G数据呢？可以思考下。
原文始发于微信公众号（客户端全栈技术养成记）：Python处理表格中httpdns数据
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/296758.html
