数值计算

pandas的基础操作进行数值计算

import pandas as pd
import numpy as np
pd.options.display.max_columns = None
p_df = pd.read_excel('产量.xlsx')
p_ratio = pd.read_excel('养殖配比.xls')
xunibiaoge = pd.read_excel('污染系数 - 副本.xlsx')
p_df=p_df.fillna(0)
print(p_df.iloc[700:,:])
#print(p_ratio.iloc[0:30,0:5])
#print(xunibiaoge)
# 北京2006
#for year in [i for i in range(2006,2019)]:
    fenlei_list = [[0+4*i,1+4*i,2+4*i,3+4*i] for i in range(32)]
    diqu_list = [[i for i in range(0,25)],[i for i in range(25,49)],[i for i in range(49,83)],[i for i in range(83,112)],[i for i in range(812,834)],[i for i in range(112,143)],[i for i in range(834,861)],[i for i in range(861,833)],[i for i in range(143,169)],[i for i in range(169,204)],[i for i in range(204,239)],[i for i in range(239,274)],[i for i in range(274,309)],[i for i in range(309,343)],[i for i in range(343,375)],[i for i in range(375,409)],[i for i in range(409,442)],[i for i in range(442,476)],[i for i in range(476,511)],[i for i in range(511,544)],[i for i in range(544,573)],[i for i in range(573,606)],[i for i in range(606,637)],[i for i in range(637,672)],[i for i in range(672,705)],[i for i in range(705,715)],[i for i in range(715,748)],[i for i in range(748,777)],[i for i in range(777,790)],[i for i in range(790,812)],[i for i in range(883,912)],[i for i in range(912,947)]]
    year = 2018
    for i_index in range(32):
        fenlei_list = [[0+4*i,1+4*i,2+4*i,3+4*i] for i in range(32)]   
        fenlei = p_ratio.iloc[fenlei_list[i_index],year-2004]# 地区的模型
        fenlei1 = [0,0,0,0]
        fenlei2=fenlei1
        diqu = p_df.iloc[diqu_list[i_index],[1,year-2004]] # 设置地区
        outcome = pd.DataFrame([],index = p_df.iloc[diqu_list[i_index],1],columns = ['池塘','围栏','网箱','工厂化'])
        #print(outcome)
        for i in range(len(diqu)):
            zong = 0.00000000000000001
            for m in range(len(xunibiaoge)):
                index = 5
                xunimingzi = xunibiaoge.iloc[m,0]
                if xunimingzi == diqu.iloc[i,0]:
                    fangshi = xunibiaoge.iloc[m,1]
                    if fangshi == '池塘养殖':
                        index = 0
                    elif fangshi == '围栏养殖':
                        index =1
                    elif fangshi == '网箱养殖':
                        index =2
                    elif fangshi == '工厂化养殖':
                        index =3
                    else:
                        pass
                    fenlei1[index] = fenlei.iloc[index]    
                    zong += fenlei.iloc[index]
            for z in range(4):
                fenlei2[z] = fenlei1[z] / zong           
            for j in range(len(fenlei2)):
                outcome.iloc[i,j]=diqu.iloc[i,1]*fenlei2[j]
        outcome1 = pd.DataFrame(np.zeros((99,5)),columns = ['index','池塘','围栏','网箱','工厂化'])
        outcome1.iloc[:,0]=xunibiaoge.iloc[:,0]               
        outcome1 = outcome1.drop_duplicates()
        outcome1 = outcome1.reset_index(drop=True)   
        for i in range(len(outcome)):
            name_index = outcome.index[i]
            for j in range(len(outcome1)):
                p_name_index = outcome1.iloc[j,0]
                if name_index == p_name_index:
                    outcome1.iloc[j,1:5] = outcome.iloc[i,:] 
        for j in range(len(outcome1)):
            for m in range(1,5):
                if outcome1.iloc[j,m]>1000000:
                    outcome1.iloc[j,m] = 0
        outcome1.to_csv('{0}年第{1}地区分类产量.csv'.format(year,i_index),encoding ='GBK')      

    buqu = [[5,6,7],[0,1,2,3,14,26,27,28,29,30,4],[8,21,9,10,11,13,15,16,17,22],[12,18,19,20,23,24,25],[31]]#东北部 ，北部区，中部区，南部区,全国
    outcome_h = pd.DataFrame([],columns = ['种类','方式','地区','数量'])
    for i in range(0,5):
        buqu_list = buqu[i]
        outcome_z = pd.DataFrame(np.zeros((36,5)),columns = ['index','池塘','围栏','网箱','工厂化'])
        outcome_z.iloc[:,0] = outcome1.iloc[:,0]
        for j in buqu_list:
            goal1 = pd.read_csv('2006年第{0}地区分类产量.csv'.format(j),encoding = 'GBK')
            outcome_z.iloc[:,1:5]=outcome_z.iloc[:,1:5]+goal1.iloc[:,2:6]
        outcome_z.to_csv('{0}年第{1}区汇总.csv'.format(year,i),encoding='GBK')
        for k in range(4):
            for p in range(36):
                if i == 0:
                    a = '东北区'
                elif i == 1:
                    a = '北部区'
                elif i == 2:
                    a = '中部区'
                elif i == 3:
                    a = '南部区'
                else:
                    a = '全国'

                if k == 0:
                    b = '池塘养殖'
                elif k==1:
                    b = '围栏养殖'
                elif k==2:
                    b = '网箱养殖'
                else:
                    b = '工厂化养殖'
                c = outcome_z.iloc[p,0]
                d = outcome_z.iloc[p,k+1]
                jieguo = pd.DataFrame([[c,b,a,d]],columns = ['种类','方式','地区','数量'])
                outcome_h = outcome_h.append(jieguo)
    print(outcome_h)

    outcome_h.to_csv('{}年产量汇总.csv'.format(year),encoding='GBK')

    wurandata = pd.read_excel('污染系数数据.xlsx')
    wurandata = wurandata.iloc[:,1:]
    print(wurandata)
    zuizhong = pd.DataFrame([],columns=wurandata.columns)

    for i in range(len(outcome_h)):
        a = outcome_h.iloc[i,0:3].to_list()
        p = outcome_h.iloc[i,3]
        xinde = []
        for j in range(len(wurandata)):
            b = wurandata.iloc[j,0:3].to_list()
            if a == b:
                xinde = a + (p * wurandata.iloc[j,3:8]).to_list()
                jieguo = pd.DataFrame([xinde],columns=wurandata.columns)
                zuizhong = zuizhong.append(jieguo)
    zuizhong.to_csv('{}年污染.csv'.format(year),encoding = 'GBK')