2024.8.17（hadoop学习第六周）

最近依旧在学习python，学习到了黑马程序员课程的第十章部分，是关于文件和数据操作的部分，正好最近一次数学建模模拟测试是一道关于数据分析的题，总共5张excel表，有四张数据量较大的文件，差不多一个在77万条左右，我依据所学和一些网上的资料对这些数据进行了分析操作，积累了很多实战经验。

数学建模模拟测试使用的一些相关python代码：

import pandas as pd

# #读取各层级用水表
file_path='E:\student\数学建模\8月培训模拟2\附件_水表层级.xlsx'
file_path1="E:\student\数学建模\8月培训模拟2\附件_一季度.xlsx"
file_path2="E:\student\数学建模\8月培训模拟2\附件_二季度.xlsx"
file_path3="E:\student\数学建模\8月培训模拟2\附件_三季度.xlsx"
file_path4="E:\student\数学建模\8月培训模拟2\附件_四季度.xlsx"

data=pd.read_excel(file_path,sheet_name='水表层级关系')

data1=pd.read_excel(file_path1,sheet_name='一季度')
data2=pd.read_excel(file_path2,sheet_name='二季度')
data3=pd.read_excel(file_path3,sheet_name='三季度')
data4=pd.read_excel(file_path4,sheet_name='四季度')

#去掉层级中一级和四级的水表
data_clean=data[(data['一级表计编码'].isna())&(data['四级表计编码'].isna())]
data_clean.to_excel('data_clean.xlsx',index=False)

#第一季度中二、三级水表
merged_data1=pd.merge(data_clean,data1,on='水表号',how='inner')
group_result1=pd.merge(data[['二级表计编码','三级表计编码','水表号']],merged_data1.groupby(['水表号'])['用量'].sum().reset_index(),on='水表号',how='inner')
#第二季度中二、三级水表
merged_data2=pd.merge(data_clean,data2,on='水表号',how='inner')
group_result2=pd.merge(data[['二级表计编码','三级表计编码','水表号']],merged_data2.groupby(['水表号'])['用量'].sum().reset_index(),on='水表号',how='inner')
#第三季度中二、三级水表
merged_data3=pd.merge(data_clean,data3,on='水表号',how='inner')
group_result3=pd.merge(data[['二级表计编码','三级表计编码','水表号']],merged_data3.groupby(['水表号'])['用量'].sum().reset_index(),on='水表号',how='inner')
#第四季度中二、三级水表
merged_data4=pd.merge(data_clean,data4,on='水表号',how='inner')
group_result4=pd.merge(data[['二级表计编码','三级表计编码','水表号']],merged_data4.groupby(['水表号'])['用量'].sum().reset_index(),on='水表号',how='inner')

# all_group_result=pd.concat([group_result1,group_result2,group_result3,group_result4])
# all_group_result=pd.merge(data[['二级表计编码','三级表计编码','水表号']],all_group_result.groupby('水表号',as_index=False).agg({'用量':'sum'}),on='水表号',how='inner')
# all_group_result.to_excel('all_group_result.xlsx',index=False)

file_name='two_three_data.xlsx'
with pd.ExcelWriter(file_name) as writer:
    group_result1.to_excel(writer,sheet_name='第一季度',index=False)
    group_result2.to_excel(writer, sheet_name='第二季度', index=False)
    group_result3.to_excel(writer, sheet_name='第三季度', index=False)
    group_result4.to_excel(writer, sheet_name='第四季度', index=False)

#
# file_path='merged_data.xlsx'
# merged_data=pd.read_excel(file_path,sheet_name='Sheet1')
# print(merged_data)

# merged_data.to_excel('merged_data.xlsx',index=False)

# #将采集时间按月进行分组
# merged_data['new_group1']=pd.to_datetime(merged_data['采集时间']).dt.month
# group_result=merged_data.groupby(['水表号','new_group1'])['用量'].sum().reset_index()
#
# print(group_result)
# group_result.to_excel('group_result.xlsx',index=False)

import pandas as pd

data=pd.read_excel('E:\student\数学建模\8月培训模拟2\附件_水表层级.xlsx',sheet_name='水表层级关系')

file_path1="E:\student\数学建模\8月培训模拟2\附件_一季度.xlsx"
file_path2="E:\student\数学建模\8月培训模拟2\附件_二季度.xlsx"
file_path3="E:\student\数学建模\8月培训模拟2\附件_三季度.xlsx"
file_path4="E:\student\数学建模\8月培训模拟2\附件_四季度.xlsx"

data1=pd.read_excel(file_path1,sheet_name='一季度')
data2=pd.read_excel(file_path2,sheet_name='二季度')
data3=pd.read_excel(file_path3,sheet_name='三季度')
data4=pd.read_excel(file_path4,sheet_name='四季度')

merged_data1=pd.merge(data,data1,on='水表号',how='inner')
merged_data2=pd.merge(data,data2,on='水表号',how='inner')
merged_data3=pd.merge(data,data3,on='水表号',how='inner')
merged_data4=pd.merge(data,data4,on='水表号',how='inner')

#去掉重复列
merged_data1=merged_data1.loc[:,~merged_data1.columns.duplicated()]
merged_data2=merged_data2.loc[:,~merged_data2.columns.duplicated()]
merged_data3=merged_data3.loc[:,~merged_data3.columns.duplicated()]
merged_data4=merged_data4.loc[:,~merged_data4.columns.duplicated()]

#去掉无用列
merged_data1.drop(['水表名_x','用户号','用户名','口径','水表名_y'],axis=1,inplace=True)
merged_data2.drop(['水表名_x','用户号','用户名','口径','水表名_y'],axis=1,inplace=True)
merged_data3.drop(['水表名_x','用户号','用户名','口径','水表名_y'],axis=1,inplace=True)
merged_data4.drop(['水表名_x','用户号','用户名','口径','水表名_y'],axis=1,inplace=True)

#依据层级表的四个等级的水表出现顺序创建一个排序方式
cate_data=data[['一级表计编码','二级表计编码','三级表计编码','四级表计编码']].copy()
merged_data1['排序']=pd.Categorical(
    merged_data1[['一级表计编码','二级表计编码','三级表计编码','四级表计编码']].apply(tuple,axis=1),
    categories=list(cate_data.itertuples(index=False,name=None)),
    ordered=True
)
merged_data2['排序']=pd.Categorical(
    merged_data2[['一级表计编码','二级表计编码','三级表计编码','四级表计编码']].apply(tuple,axis=1),
    categories=list(cate_data.itertuples(index=False,name=None)),
    ordered=True
)
merged_data3['排序']=pd.Categorical(
    merged_data3[['一级表计编码','二级表计编码','三级表计编码','四级表计编码']].apply(tuple,axis=1),
    categories=list(cate_data.itertuples(index=False,name=None)),
    ordered=True
)
merged_data4['排序']=pd.Categorical(
    merged_data4[['一级表计编码','二级表计编码','三级表计编码','四级表计编码']].apply(tuple,axis=1),
    categories=list(cate_data.itertuples(index=False,name=None)),
    ordered=True
)

group_result1=merged_data1.sort_values(by=['采集时间','排序']).drop(columns='排序').reset_index(drop=True)
group_result2=merged_data2.sort_values(by=['采集时间','排序']).drop(columns='排序').reset_index(drop=True)
group_result3=merged_data3.sort_values(by=['采集时间','排序']).drop(columns='排序').reset_index(drop=True)
group_result4=merged_data4.sort_values(by=['采集时间','排序']).drop(columns='排序').reset_index(drop=True)

# group_result1.to_excel('now_result_1.xlsx',index=False)
# group_result2.to_excel('now_result_1.xlsx',index=False)
# group_result3.to_excel('now_result_1.xlsx',index=False)
# group_result4.to_excel('now_result_1.xlsx',index=False)

file_name='now_result.xlsx'
with pd.ExcelWriter(file_name) as writer:
    group_result1.to_excel(writer,sheet_name='第一季度',index=False)
    group_result2.to_excel(writer, sheet_name='第二季度', index=False)
    group_result3.to_excel(writer, sheet_name='第三季度', index=False)
    group_result4.to_excel(writer, sheet_name='第四季度', index=False)

import pandas as pd
file_path1="now_result.xlsx"
sheet=['第一季度','第二季度','第三季度','第四季度']

#定义一个新的数据框
data=pd.DataFrame(columns=['一级表计编码','二级表计编码','三级表计编码','四级表计编码','水表号','采集时间','上次读数','当前读数','用量'])

file_name='暗漏情况.xlsx'

num=0

with (pd.ExcelWriter(file_name) as writer):
    for x in sheet:
        excel_data=pd.read_excel(file_path1,sheet_name=x)

        data=pd.DataFrame(columns=excel_data.columns)
        #遍历每一行数据，将每一次的当前读数减去上次读数大于用量的时候报存到新的数据框中
        for index,row in excel_data.iterrows():
            if row['当前读数']-row['上次读数'] > row['用量'] :
                num=num+1
                print(num)
                data=pd.concat([data,pd.DataFrame([row])],ignore_index=True)
        # 将数据存入到excel表格里
        data.to_excel(writer,sheet_name=x,index=False)
import pandas as pd
import matplotlib.pyplot as plt

plt.rcParams['font.sans-serif']=['SimHei']
plt.rcParams['axes.unicode_minus'] = False

file_path='now_result.xlsx'
file_path2='暗漏情况.xlsx'

sheet=['第一季度','第二季度','第三季度','第四季度']

file_name="水表分布.xlsx"
with pd.ExcelWriter(file_name) as writer:
    for x in sheet:
        data1=pd.read_excel(file_path,sheet_name=x)
        grouped1=data1.groupby('水表号').size().reset_index(name='全部数')
        print(grouped1)

        data2=pd.read_excel(file_path2,sheet_name=x)
        grouped2 = data2.groupby('水表号').size().reset_index(name='暗漏数')
        print(grouped2)

        merged_data=pd.merge(grouped1,grouped2,on='水表号',how='inner')
        print(merged_data)

        plt.figure(figsize=(10,6))
        plt.plot(merged_data['水表号'],merged_data['全部数'],marker='o',label='全部数',color='blue')
        plt.plot(merged_data['水表号'], merged_data['暗漏数'], marker='o', label='暗漏数', color='red')
        # 设置标题和标签
        name="各水表号漏损情况统计图"+x
        plt.title(name)
        plt.xlabel('水表号')
        plt.ylabel('数量')
        plt.xticks(rotation=45)  # 如果水表号较长，可以旋转坐标标签
        plt.legend()
        plt.grid()

        merged_data.to_excel(writer,sheet_name=x,index=False)

# 显示图形
plt.tight_layout()  # 调整布局以避免标签重叠
plt.show()
import pandas as pd

sheet=['第一季度','第二季度','第三季度','第四季度']

date=pd.DataFrame(columns=['时间','漏水总量'])

for x in sheet:
    excel_data=pd.read_excel('now_result.xlsx',sheet_name=x)
    excel_data['读数差值']=excel_data['当前读数']-excel_data['上次读数']
    excel_data['漏水量']=excel_data.apply(lambda row:row['读数差值']-row['用量'] if row['读数差值']> row['用量'] else 0.0, axis=1)
    excel_data['时间']=pd.to_datetime(excel_data['采集时间']).dt.date
    now_date=excel_data.groupby(['时间'])['漏水量'].sum().reset_index(name='漏水总量')
    now_date=now_date.dropna(axis=1,how='all')
    date=pd.concat([date,now_date],ignore_index=True)
date.to_excel('每日漏水总量.xlsx',index=False)

 

在学习完python后将会进入到spark的相关学习中。