import numpy as np
import pandas as pd
import math
df2=pd.read_excel(r'C:\Users\Desktop\数学建模美赛\第一次小组赛11.27E\各地流量使用数据.xlsx')
df1=pd.read_excel(r'C:\Users\Desktop\数学建模美赛\第一次小组赛11.27E\编码水表号查找.xlsx')
df3=pd.read_excel(r'C:\Users\Desktop\数学建模美赛\第一次小组赛11.27E\各级水表关系图.xlsx')
df2['date']=pd.to_datetime(df2['time'])
df=df2
def lost(j):
    xx1=df1['水表名'][list(df1['编码']).index(df3.iloc[j,0])]
    xx2=df1['水表名'][list(df1['编码']).index(df3.iloc[j,1])]
    d1=df[[xx1,xx2]]
    d1.dropna(axis=0, how='any', inplace=True)
    d2=d1[(d1[xx1]>d1[xx2])&(d1[xx2]>0)]
    d2['lost_rate']=(d2[xx1]-d2[xx2])/d2[xx1]
    d12=sorted(list(d2['lost_rate']))
    lost_r=d12[0]
    result=[xx2,lost_r]
    return result

lost_rate=[]
for i in range(len(df3)):
    lost_rate.append(lost(i))
    print('已计算到第',i,'个')
print('计算结束')
df_lost=pd.DataFrame(lost_rate)

import matplotlib.pyplot as plt
df_test=df_lost[df_lost[1]>0.3]
# 这两行代码解决 plt 中文显示的问题
plt.rcParams['font.sans-serif'] = ['SimHei']
plt.rcParams['axes.unicode_minus'] = False

plt.figure(figsize=(20, 5), dpi=500)
plt.bar(df_test[0],df_test[1])
plt.figure(figsize=(10, 5), dpi=300)
d2['lost_rate'].plot()
plt.figure(figsize=(10, 5), dpi=500)
a=pd.DataFrame(d12)
a[0].plot()