Using Python pandas and Plotly to Replicate Common Excel Functions

The article begins by explaining the shift from Excel to Python for large‑scale business analytics, highlighting pandas as the core library for data manipulation and plotly for interactive visualisation.

Key pandas I/O functions ( read_csv , read_excel , to_csv , to_excel ) are introduced, followed by a sample import configuration:

<code>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</code>

Several Excel‑like tasks are reproduced in Python:

VLOOKUP equivalent : reading an Excel sheet and mapping grades with a custom function, then applying it to a DataFrame column.

Cross‑sheet lookup : using merge to join tables on a key column.

Approximate lookup : iterating over rows and matching substrings with pandas.

Batch lookup : filtering rows where a name matches a target value.

Examples of the code used for these tasks are shown, for instance:

<code>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)
</code>

Pivot tables are recreated with both groupby and pivot_table methods, demonstrating aggregation of sales, cost, and profit by month and region.

<code># groupby version
df['订购月份'] = df['订购日期'].apply(lambda x: x.month)
summary = df.groupby(['订购月份', '所属区域'])[['销售额', '成本']].sum()
summary['利润'] = summary['销售额'] - summary['成本']

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

For visualisation, plotly is used to create grouped bar charts and radar charts. The script loads the data, defines traces, sets a layout, and renders the figure:

<code>import plotly.offline as off
import plotly.graph_objs as go
off.init_notebook_mode()
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)
</code>

The article concludes that while Excel is convenient for quick, simple charts, Python offers greater flexibility and automation for complex or large‑scale data analysis tasks.