Python和Excel的完美结合：常用操作汇总(案例详析)

import numpy as npimport pandas as pdpd.set_option('max_columns', 10)pd.set_option('max_rows', 20)pd.set_option('display.float_format', lambda x: '%.2f' % x) # 禁用科学计数法

dfOut[]:工号 姓名 性别部门0 A0001 张伟男工程1 A0002王秀英女人事2 A0003 王芳女行政3 A0004 郑勇男市场4 A0005 张丽女研发5 A0006 王艳女后勤6 A0007 李勇男市场7 A0008 李娟女工程8 A0009 张静女人事9 A0010 王磊男行政10A0011 李娜女市场11A0012刘诗雯女研发12A0013 王刚男后勤13A0014 叶倩女后勤14A0015金雯雯女市场15A0016王超杰男工程16A0017 李军男人事

df = pd.read_excel("test.xlsx", sheet_name=0)def grade_to_point(x): if x >= 90: return 'A' elif x >= 80: return 'B' elif x >= 70: return 'C' elif x >= 60: return 'D' else: return 'E'df['等级'] = df['语文'].apply(grade_to_point)dfOut[]:学号 姓名 性别 语文 等级0 101王小丽女 69D1 102王宝勤男 85B2 103杨玉萍女 49E3 104田东会女 90A4 105陈雪蛟女 73C5 106杨建丰男 42E6 107黎梅佳女 79C7 108 张兴 男 91A8 109马进春女 48E9 110魏改娟女100A10111王冰研女 64D

df1 = pd.read_excel("test.xlsx", sheet_name='折旧明细表')df2 = pd.read_excel("test.xlsx", sheet_name=1) #题目里的sheet1df2.merge(df1[['编号', '月折旧额']], how='left', on='编号')Out[]: 编号 资产名称月折旧额0YT001电动门 13991YT005桑塔纳轿车11472YT008打印机51

df1 = pd.read_excel("test.xlsx", sheet_name='折旧明细表')df3 = pd.read_excel("test.xlsx", sheet_name=3) #含有资产名称简写的表df3['月折旧额'] = 0for i in range(len(df3['资产名称'])): df3['月折旧额'][i] = df1[df1['资产名称'].map(lambda x:df3['资产名称'][i] in x)]['月折旧额']df3Out[]: 资产名称 月折旧额0 电动 13991 货车 24382 惠普1323 交联101334桑塔纳 11475 春兰230

df4 = pd.read_excel("test.xlsx", sheet_name='员工基本信息表')df5 = pd.read_excel("test.xlsx", sheet_name='请假统计表')df5.merge(df4[['工号', '姓名', '部门', '职务', '入职日期']], on='工号')Out[]: 工号 姓名部门 职务 入职日期0A0004龚梦娟后勤 主管 2006-11-201A0003 赵敏行政 文员 2007-02-162A0005 黄凌研发工程师 2009-01-143A0007 王维人事 经理 2006-07-244A0016张君宝市场工程师 2007-08-145A0017 秦羽人事副经理 2008-03-06

df6 = pd.read_excel("test.xlsx", sheet_name='消费额')df6[df6['姓名'] == '张一'][['姓名', '消费额']]Out[]:姓名 消费额0张一 1002张一 3004张一1000

df = pd.read_excel('test.xlsx', sheet_name='销售统计表')df['订购月份'] = df['订购日期'].apply(lambda x:x.month)df2 = df.groupby(['订购月份', '所属区域'])[['销售额', '成本']].agg('sum')df2['利润'] = df2['销售额'] - df2['成本']df2Out[]:销售额 成本利润订购月份 所属区域1南京134313.61 94967.8439345.77常熟177531.47163220.0714311.40无锡316418.09231822.2884595.81昆山159183.35145403.3213780.03苏州287253.99238812.0348441.962南京187129.13138530.4248598.71常熟154442.74126834.3727608.37无锡464012.20376134.9887877.22昆山102324.46 86244.5216079.94苏州105940.34 91419.5414520.80...... ...11 南京286329.88221687.1164642.77常熟 2118503.54 1840868.53 277635.01无锡633915.41536866.7797048.64昆山351023.24342420.18 8603.06苏州 1269351.39 1144809.83 124541.5612 南京894522.06808959.3285562.74常熟324454.49262918.8161535.68无锡 1040127.19856816.72 183310.48昆山 1096212.75951652.87 144559.87苏州347939.30302154.2545785.05[60 rows x 3 columns]

df3 = pd.pivot_table(df, values=['销售额', '成本'], index=['订购月份', '所属区域'] , aggfunc='sum')df3['利润'] = df3['销售额'] - df3['成本']df3Out[]: 成本销售额利润订购月份 所属区域1南京 94967.84134313.6139345.77常熟163220.07177531.4714311.40无锡231822.28316418.0984595.81昆山145403.32159183.3513780.03苏州238812.03287253.9948441.962南京138530.42187129.1348598.71常熟126834.37154442.7427608.37无锡376134.98464012.2087877.22昆山 86244.52102324.4616079.94苏州 91419.54105940.3414520.80...... ...11 南京221687.11286329.8864642.77常熟 1840868.53 2118503.54 277635.01无锡536866.77633915.4197048.64昆山342420.18351023.24 8603.06苏州 1144809.83 1269351.39 124541.5612 南京808959.32894522.0685562.74常熟262918.81324454.4961535.68无锡856816.72 1040127.19 183310.48昆山951652.87 1096212.75 144559.87苏州302154.25347939.3045785.05[60 rows x 3 columns]

import plotly.offline as offimport plotly.graph_objs as gooff.init_notebook_mode()

df = pd.read_excel("plot.xlsx", sheet_name='高等教育入学率')trace1 = go.Bar( x=df['国家'], y=df[1995], name='1995', opacity=0.6, marker=dict( color='powderblue' ) )trace2 = go.Bar( x=df['国家'], y=df[2005], name='2005', opacity=0.6, marker=dict( color='aliceblue', ) )trace3 = go.Bar( x=df['国家'], y=df[2014], name='2014', opacity=0.6, marker=dict( color='royalblue' ) )layout = go.Layout(barmode='group')data = [trace1, trace2, trace3]fig = go.Figure(data, layout)off.plot(fig)

df = pd.read_excel('plot.xlsx', sheet_name='政治治理')theta = df.columns.tolist()theta.append(theta[0])names = df.indexdf[''] = df.iloc[:,0]df = np.array(df)trace1 = go.Scatterpolar( r=df[0], theta=theta, name=names[0] )trace2 = go.Scatterpolar( r=df[1], theta=theta, name=names[1] )trace3 = go.Scatterpolar( r=df[2], theta=theta, name=names[2] )trace4 = go.Scatterpolar( r=df[3], theta=theta, name=names[3] )data = [trace1, trace2, trace3, trace4]layout = go.Layout( polar=dict( radialaxis=dict( visible=True, range=[0,1] ) ), showlegend=True )fig = go.Figure(data, layout)off.plot(fig)