原生的pd.to_excel会覆写，我们需要用到pd.ExcelWriter。

like：

1
2
3
4
with pd.ExcelWriter(p,mode="a",engine="openpyxl") as writer:
    df=[pd.DataFrame(i) for i in Record_List]
    for i,j in enumerate(years):
        df[i].to_excel(writer,sheet_name=str(j))

当然，在之前必须保证这个文件的存在。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
import pandas as pd
import numpy as np
import os
from pandas.core.frame import DataFrame


# urban_pop,rural_pop,NF,CF为m*1矩阵,
# urban_prot,rural_pop,ncf,Redi为数值,
# AN,WP,CP,CA为m*n矩阵,
# ANI,ANI,ANO,PW,NF,PC为n*1矩阵。
def caculate_NANI(urban_pop,rural_pop,urban_prot,rural_prot,AN,ANI,ANO,Redi,CP,PC,NF,CF,rN,CA,Ndep):
    def Map(x):
        return [float(i) for i in x]
    Nhc = urban_pop.T*urban_prot+rural_pop.T*rural_prot
    Nlc = np.dot(AN*(10e5), ANI)
    Nlp = np.dot(AN*(10e5), (ANI-ANO))*Redi
    Ncp = np.dot(CP*(10e5), PC)
    Nfer = N*(10e5) + CF*(10e5)*rN
    Ncro = np.dot(CA*10, NF)
    Nim = Nhc + Nlc - Nlp - Ncp
    NANI = Nim+Nfer+Ncro+Ndep
    return NANI,{"Nhc":list(map(lambda x:float(x),Nhc)),"Nlc":list(map(lambda x:float(x),Nlc)),
                 "Nlp":Map(Nlp),"Ncp":Map(Ncp),"Nfer":Map(Nfer),"Ncro":Map(Ncro),"Nim":Map(Nim),"NANI":Map(NANI)}



if __name__ == '__main__':
    years = [1990, 2000, 2010, 2020]
    # years = [1990,2000]
    root = r'C:\Users\lenovo\Documents\Tencent Files\651421775\FileRecv\新建文件夹 (2)\\'  # 数据存放目录路径
    Record_path=r"G:\\" # 中间变量输出位置
    file_paths = [os.path.join(root,str(i))+".xlsx" for i in years]
    all_list = []
    urban_prot = 4.77
    rural_prot = 4.31
    Redi = 0.9
    rN = 0.1285
    ANI = np.mat([66.75, 44.80, 18.00, 18.00, 9.5, 14.5, 14.4]).T
    ANO = np.mat([58.51, 40.00, 15.15, 9.59, 7.21, 5.69, 5.69]).T
    PC = np.mat([12.6, 21.3, 13.0, 66.2, 2.0, 39.7, 12.2, 2.8, 1.5, 1.8]).T
    NF = np.mat([3700, 9600, 8000, 1500, 1500]).T
    if os.path.exists(p:=(Record_path+"process.xlsx")):
        os.remove(p)

    pd.DataFrame([]).to_excel(p)
    Record_List=[]

    for i in range(len(file_paths)):

        data = pd.read_excel(file_paths[i])
        urban_pop, rural_pop = np.mat(data['城镇人口']), np.mat(data['农村人口'])
        AN, CP, CA = np.mat(data.values[:, 4:11]), np.mat(data.values[:, 11:21]), np.mat(data.values[:, 23:28])
        N = np.mat(data['氮肥']).T
        CF = np.mat(data['复合肥']).T
        Ndep = np.mat(data['大气氮沉降']).T
        NANI,Record = caculate_NANI(urban_pop,rural_pop,urban_prot,rural_prot,AN,ANI,ANO,Redi,CP,PC,NF,CF,rN,CA,Ndep)
        NANI_list=[float(i) for i in NANI]
        Record_List.append(Record)
        all_list.append(NANI_list)

    with pd.ExcelWriter(p,mode="a",engine="openpyxl") as writer:
        df=[pd.DataFrame(i) for i in Record_List]
        for i,j in enumerate(years):
            df[i].to_excel(writer,sheet_name=str(j))

    df_data = {"省域": list(data['省域']), "1990": all_list[0], "2000": all_list[1], "2010": all_list[2], "2020": all_list[3]}
    df = DataFrame(df_data)
    df.to_excel(r"G:\new.xlsx")  # 保存结果路径

Numpy的矩阵乘法用于处理数据时，一般是这样操作：

  • 读取列A=np.mat(data.values[:,5:15])
  • 写入权重或者对应矩阵w=[i for i in range(10)]
  • 做矩阵乘法np.dot(A,w.T)，也就是Aw=b
  • 这样每一列都被乘上了权重。