Using Python pandas to Replicate Common Excel Functions and Visualizations

In the past, business analysis relied on Excel, but growing data volumes have driven a shift toward Python and its pandas library, which follows the DRY principle and offers a complete ecosystem for data manipulation.

Key pandas I/O functions (read_csv, read_excel, to_csv, to_excel) are introduced, followed by a simple setup:

import numpy as np
import pandas as pd
pd.set_option('max_columns', 10)
pd.set_option('max_rows', 20)
pd.set_option('display.float_format', lambda x: '%.2f' % x)  # disable scientific notation

Reading a specific range from an Excel sheet can be done with:

df = pd.read_excel("test.xlsx", header=1, nrows=17, usecols=3)

To emulate Excel's VLOOKUP, a Python function maps scores to grades and applies it to a DataFrame:

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)

Cross‑sheet lookups are performed with pandas merge:

df1 = pd.read_excel("test.xlsx", sheet_name='折旧明细表')
df2 = pd.read_excel("test.xlsx", sheet_name=1)  # Sheet1
result = df2.merge(df1[['编号', '月折旧额']], how='left', on='编号')

Pivot tables and groupby aggregations replace Excel's data‑pivot functionality. Example using groupby:

df = pd.read_excel('test.xlsx', sheet_name='销售统计表')
df['订购月份'] = df['订购日期'].apply(lambda x: x.month)
summary = df.groupby(['订购月份', '所属区域'])[['销售额', '成本']].sum()
summary['利润'] = summary['销售额'] - summary['成本']
print(summary)

The same result can be achieved with pandas pivot_table:

pivot = pd.pivot_table(df, values=['销售额', '成本'], index=['订购月份', '所属区域'], aggfunc='sum')
pivot['利润'] = pivot['销售额'] - pivot['成本']
print(pivot)

For visualizations, Plotly is used to recreate Excel charts. A grouped bar chart example:

import plotly.offline as off
import plotly.graph_objs as go

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')
fig = go.Figure(data=[trace1, trace2, trace3], layout=layout)
off.plot(fig)

A radar chart (scatterpolar) is also shown, illustrating the extra steps required compared to Excel:

df = pd.read_excel('plot.xlsx', sheet_name='政治治理')
theta = df.columns.tolist(); theta.append(theta[0])
names = df.index
values = np.array(df)
traces = []
for i in range(4):
    traces.append(go.Scatterpolar(r=values[i], theta=theta, name=names[i]))
layout = go.Layout(polar=dict(radialaxis=dict(visible=True, range=[0,1])), showlegend=True)
fig = go.Figure(data=traces, layout=layout)
off.plot(fig)

Overall, for simple charts Excel remains the quickest tool, but for more advanced, customizable visualizations and large‑scale data processing, Python with pandas and Plotly offers greater flexibility and performance.