python学习-使用pandas库分析excel表，并导出所需的表

核心代码

# 导入pandas库
import pandas as pd
# 导入正则表达式包
import re

# 指定Excel文件的路径，这个data.xlsx表为原始表，表内有40个sheet子表
file_path = 'data.xlsx'
# 读取各个子表
allDf = pd.read_excel(file_path, sheet_name=None)
# 获取各个子工作表表名
workSheet = allDf.keys()
# 定义workSheetList ，存放所有子工作表表名
workSheetList = list(workSheet)
# 直接打印
# print(workSheetList)

# 创建一个空list，组织
organizationList = []
# 总人数
countList = []
# 未活跃人数
inactiveCountList = []
# 平均周活跃度
userActivityList = []
# 总采纳行数
acceptanceLineNumberList = []
# 总推荐行数
recommendationsLineNumberList = []
# 平均采纳率
acceptanceRateList = []
# 总采纳次数
numberOfAcceptancesList = []
# 总推荐次数
numberOfRecommendationsList = []
# 循环workSheetList 列表，去掉表名以sheet开头的子表名，即无用的sheet表
for j in workSheetList:
    if j.startswith('Sheet'):
        workSheetList.remove(j)
    else:
    	# 使用正则表达式，去掉空string
        j = re.sub(r"\s+", "", j)
        organizationList.append(j)
		# 读取Excel文件中的子工作表
        sheetDateFrom = pd.read_excel(file_path, j)

        # 获取每个工作表的行数
        rows = len(sheetDateFrom.index)
        countList.append(rows)

        # 获取特定列的内容
        column_8 = sheetDateFrom.iloc[:, 7]

        # 统计是否获取列，包含“否”的个数
        count_no = (column_8 == '否').sum()
        inactiveCountList.append(count_no)
        activityNum = rows - count_no
        userActivity = activityNum / rows
        # 去掉小数点后面的数，例如50%
        userActivity = '{:.0%}'.format(userActivity)
        userActivityList.append(userActivity)

        # 获取采纳行数的内容,子表第5列的数据
        acceptanceLineSum = sheetDateFrom.iloc[:, 4].sum()
        acceptanceLineNumberList.append(acceptanceLineSum)
        # 获取推荐行数的内容,子表第6列的数据
        recommendationsLineSum = sheetDateFrom.iloc[:, 5].sum()
        recommendationsLineNumberList.append(recommendationsLineSum)
 		# 获取采纳次数的内容,子表第11列的数据
        numberOfAcceptances = sheetDateFrom.iloc[:, 10].sum()
        numberOfAcceptancesList.append(numberOfAcceptances)
        # 获取推荐次数的内容,子表第12列的数据
        numberOfRecommend = sheetDateFrom.iloc[:, 11].sum()
        numberOfRecommendationsList.append(numberOfRecommend)
        # 获取采纳率/行的内容，解决除数为0的异常情况
        if numberOfRecommend == 0:
            acceptanceRateSum = 0
        else:
            acceptanceRateSum = numberOfAcceptances/numberOfRecommend
        acceptanceRateSum = '{:.0%}'.format(acceptanceRateSum)
        acceptanceRateList.append(acceptanceRateSum)

# 创建一个DataFrame对象，并将组织名称、人数和未活跃人数分别赋值给每一列
data = {'组织': organizationList,
        '人数': countList,
        '未活跃人数': inactiveCountList,
        '周活跃度': userActivityList,
        '采纳率/行': acceptanceRateList,
        '总采纳行数': acceptanceLineNumberList,
        '总推荐行数': recommendationsLineNumberList,
        '总采纳次数': numberOfAcceptancesList,
        '总推荐次数': numberOfRecommendationsList
        }
resultDf = pd.DataFrame(data)
# 显示DataFrame
pd.set_option('display.unicode.ambiguous_as_wide', True)
pd.set_option('display.unicode.east_asian_width', True)
pd.set_option('display.max_colwidth', 1000)
# 解决在控制台输出的列不完整，有...
pd.set_option('expand_frame_repr', False)
print(resultDf)
# 输出表
resultDf.to_excel('output.xlsx', index=False)
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
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102

输出的结果

       组织        人数  未活跃人数 周活跃度 采纳率/行  总采纳行数  总推荐行数  总采纳次数  总推荐次数
0       A部           1           1       0%        0%           0           0           0           0
1       B部          17           9      47%       44%         660        1385         341         777
2       C部          8           4      50%       38%         218         551         137         361
3       D部           8           2      75%       26%         114         444          70         266E
4       E部           8           5      38%       21%          69         468          58         282
5       F部           2           2       0%        0%           0           0           0           0
6       G部          51          27      47%       23%        1362        5820         676        2955
7       H部          13           4      69%       38%         460        1425         261         685
8       I部          25           9      64%       20%         297        1844         192         963
9       J部           2           2       0%        0%           0           0           0           0
10      K部           3           3       0%        0%           0           0           0           0
11      L部           2           2       0%        0%           0           0           0           0
12      M部          63          36      43%       19%         534        3447         219        1150
13      N部          63          26      59%       34%        1659        5354         913        2705
14      总计         267         133      50%       28%        5373       20738        2867       10144

进程已结束，退出代码为 0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

推荐的插件

Chinese(Simplified)Language·Pack / 中文语言包

特别说明

参考了pycharm 专业版激活码(2024.5亲测可用)最新https://blog.csdn.net/qq_41717536/article/details/136171678

UX394X3HLT-eyJsaWNlbnNlSWQiOiJVWDM5NFgzSExUIiwibGljZW5zZWVOYW1lIjoiSG9uZ2lrIFVuaXZlcnNpdHntmY3snbXrjIDtlZnqtZAiLCJsaWNlbnNlZVR5cGUiOiJDTEFTU1JPT00iLCJhc3NpZ25lZU5hbWUiOiLkvJfliJvkupEg5bel5L2c5a6kIiwiYXNzaWduZWVFbWFpbCI6ImhhbmF6YXdhbWl0b0BnbWFpbC5jb20iLCJsaWNlbnNlUmVzdHJpY3Rpb24iOiJGb3IgZWR1Y2F0aW9uYWwgdXNlIG9ubHkiLCJjaGVja0NvbmN1cnJlbnRVc2UiOmZhbHNlLCJwcm9kdWN0cyI6W3siY29kZSI6IkdPIiwicGFpZFVwVG8iOiIyMDI0LTEyLTEzIiwiZXh0ZW5kZWQiOmZhbHNlfSx7ImNvZGUiOiJSUzAiLCJwYWlkVXBUbyI6IjIwMjQtMTItMTMiLCJleHRlbmRlZCI6ZmFsc2V9LHsiY29kZSI6IkRNIiwicGFpZFVwVG8iOiIyMDI0LTEyLTEzIiwiZXh0ZW5kZWQiOmZhbHNlfSx7ImNvZGUiOiJDTCIsInBhaWRVcFRvIjoiMjAyNC0xMi0xMyIsImV4dGVuZGVkIjpmYWxzZX0seyJjb2RlIjoiUlNVIiwicGFpZFVwVG8iOiIyMDI0LTEyLTEzIiwiZXh0ZW5kZWQiOmZhbHNlfSx7ImNvZGUiOiJSU0MiLCJwYWlkVXBUbyI6IjIwMjQtMTItMTMiLCJleHRlbmRlZCI6dHJ1ZX0seyJjb2RlIjoiUEMiLCJwYWlkVXBUbyI6IjIwMjQtMTItMTMiLCJleHRlbmRlZCI6ZmFsc2V9LHsiY29kZSI6IkRTIiwicGFpZFVwVG8iOiIyMDI0LTEyLTEzIiwiZXh0ZW5kZWQiOmZhbHNlfSx7ImNvZGUiOiJSRCIsInBhaWRVcFRvIjoiMjAyNC0xMi0xMyIsImV4dGVuZGVkIjpmYWxzZX0seyJjb2RlIjoiUkMiLCJwYWlkVXBUbyI6IjIwMjQtMTItMTMiLCJleHRlbmRlZCI6ZmFsc2V9LHsiY29kZSI6IlJTRiIsInBhaWRVcFRvIjoiMjAyNC0xMi0xMyIsImV4dGVuZGVkIjp0cnVlfSx7ImNvZGUiOiJSTSIsInBhaWRVcFRvIjoiMjAyNC0xMi0xMyIsImV4dGVuZGVkIjpmYWxzZX0seyJjb2RlIjoiSUkiLCJwYWlkVXBUbyI6IjIwMjQtMTItMTMiLCJleHRlbmRlZCI6ZmFsc2V9LHsiY29kZSI6IkRQTiIsInBhaWRVcFRvIjoiMjAyNC0xMi0xMyIsImV4dGVuZGVkIjpmYWxzZX0seyJjb2RlIjoiREIiLCJwYWlkVXBUbyI6IjIwMjQtMTItMTMiLCJleHRlbmRlZCI6ZmFsc2V9LHsiY29kZSI6IkRDIiwicGFpZFVwVG8iOiIyMDI0LTEyLTEzIiwiZXh0ZW5kZWQiOmZhbHNlfSx7ImNvZGUiOiJQUyIsInBhaWRVcFRvIjoiMjAyNC0xMi0xMyIsImV4dGVuZGVkIjpmYWxzZX0seyJjb2RlIjoiUlNWIiwicGFpZFVwVG8iOiIyMDI0LTEyLTEzIiwiZXh0ZW5kZWQiOnRydWV9LHsiY29kZSI6IldTIiwicGFpZFVwVG8iOiIyMDI0LTEyLTEzIiwiZXh0ZW5kZWQiOmZhbHNlfSx7ImNvZGUiOiJQU0kiLCJwYWlkVXBUbyI6IjIwMjQtMTItMTMiLCJleHRlbmRlZCI6dHJ1ZX0seyJjb2RlIjoiUENXTVAiLCJwYWlkVXBUbyI6IjIwMjQtMTItMTMiLCJleHRlbmRlZCI6dHJ1ZX0seyJjb2RlIjoiUlMiLCJwYWlkVXBUbyI6IjIwMjQtMTItMTMiLCJleHRlbmRlZCI6dHJ1ZX0seyJjb2RlIjoiRFAiLCJwYWlkVXBUbyI6IjIwMjQtMTItMTMiLCJleHRlbmRlZCI6dHJ1ZX0seyJjb2RlIjoiUERCIiwicGFpZFVwVG8iOiIyMDI0LTEyLTEzIiwiZXh0ZW5kZWQiOnRydWV9XSwibWV0YWRhdGEiOiIwMTIwMjMxMjI4TFBBQTAwNDAwOCIsImhhc2giOiI1MjY4NzM4Ny8yNTMxODE0MTotMTUwNDg0MzkwMSIsImdyYWNlUGVyaW9kRGF5cyI6NywiYXV0b1Byb2xvbmdhdGVkIjpmYWxzZSwiaXNBdXRvUHJvbG9uZ2F0ZWQiOmZhbHNlLCJ0cmlhbCI6ZmFsc2UsImFpQWxsb3dlZCI6dHJ1ZX0=-YqDHrEIEaf/x1JqIdTI64AYA1IpRoYiqoZ/1YDnfpEqSFNJIC4er7K1hjUm9tFslnY2XoNRs04JSUG8CgNkTgIKA4xLyxGBufJYyHv26UKQmyf1nb1pM9XATb3pWSQ3h6o8/4x3jacVk3zbAuXt3uV6eEj2bCZvhGpATFmK1JVsSor+XgPr5aYePCtyymiyPOq33ghW5onzSI5LsQR5motHvLgWmjf0Mkutys3SmWt13YVcIe5yCzhlTNCZw++CAuRh2Fx/JXZhRt+kUqW2yLbkIo3kNEg2I31H8qya+RDJ09Qz7DsDkrIgODqX4Wbd2fy1C7Q1CcjjksvjhswnpNA==-MIIETDCCAjSgAwIBAgIBDzANBgkqhkiG9w0BAQsFADAYMRYwFAYDVQQDDA1KZXRQcm9maWxlIENBMB4XDTIyMTAxMDE2MDU0NFoXDTI0MTAxMTE2MDU0NFowHzEdMBsGA1UEAwwUcHJvZDJ5LWZyb20tMjAyMjEwMTAwggEiMA0GCSqGSIb3DQEBAQUAA4IBDwAwggEKAoIBAQC/W3uCpU5M2y48rUR/3fFR6y4xj1nOm3rIuGp2brELVGzdgK2BezjnDXpAxVDw5657hBkAUMoyByiDs2MgmVi9IcqdAwpk988/Daaajq9xuU1of59jH9eQ9c3BmsEtdA4boN3VpenYKATwmpKYkJKVc07ZKoXL6kSyZuF7Jq7HoQZcclChbF75QJPGbri3cw9vDk/e46kuzfwpGftvl6+vKibpInO6Dv0ocwImDbOutyZC7E+BwpEm1TJZW4XovMBegHhWC04cJvpH1u98xoR94ichw0jKhdppywARe43rGU96163RckIuFmFDQKZV9SMUrwpQFu4Z2D5yTNqnlLRfAgMBAAGjgZkwgZYwCQYDVR0TBAIwADAdBgNVHQ4EFgQU5FZqQ4gnVc+inIeZF+o3ID+VhcEwSAYDVR0jBEEwP4AUo562SGdCEjZBvW3gubSgUouX8bOhHKQaMBgxFjAUBgNVBAMMDUpldFByb2ZpbGUgQ0GCCQDSbLGDsoN54TATBgNVHSUEDDAKBggrBgEFBQcDATALBgNVHQ8EBAMCBaAwDQYJKoZIhvcNAQELBQADggIBANLG1anEKid4W87vQkqWaQTkRtFKJ2GFtBeMhvLhIyM6Cg3FdQnMZr0qr9mlV0w289pf/+M14J7S7SgsfwxMJvFbw9gZlwHvhBl24N349GuthshGO9P9eKmNPgyTJzTtw6FedXrrHV99nC7spaY84e+DqfHGYOzMJDrg8xHDYLLHk5Q2z5TlrztXMbtLhjPKrc2+ZajFFshgE5eowfkutSYxeX8uA5czFNT1ZxmDwX1KIelbqhh6XkMQFJui8v8Eo396/sN3RAQSfvBd7Syhch2vlaMP4FAB11AlMKO2x/1hoKiHBU3oU3OKRTfoUTfy1uH3T+t03k1Qkr0dqgHLxiv6QU5WrarR9tx/dapqbsSmrYapmJ7S5+ghc4FTWxXJB1cjJRh3X+gwJIHjOVW+5ZVqXTG2s2Jwi2daDt6XYeigxgL2SlQpeL5kvXNCcuSJurJVcRZFYUkzVv85XfDauqGxYqaehPcK2TzmcXOUWPfxQxLJd2TrqSiO+mseqqkNTb3ZDiYS/ZqdQoGYIUwJqXo+EDgqlmuWUhkWwCkyo4rtTZeAj+nP00v3n8JmXtO30Fip+lxpfsVR3tO1hk4Vi2kmVjXyRkW2G7D7WAVt+91ahFoSeRWlKyb4KcvGvwUaa43fWLem2hyI4di2pZdr3fcYJ3xvL5ejL3m14bKsfoOv
1

如果提示报错，可以配置一下代理，比如
（注意：这样会影响插件、库的下载导入；那么可以在安装插件和导入库之后再配置）

手动代理配置
http
主机名：localhost
端口号：80
1
2
3
4