Using Pandas to Automate Excel Data Processing and Visualization

In many work scenarios, Excel is used for data handling, but Python can automate tasks such as parsing JSON, calculating totals, and merging multiple files.

When dealing with large datasets, using pandas to read, compute, and write Excel files greatly improves efficiency.

Example: reading an Excel file and calculating a "Total Price" column:

import pandas as pd

# Read Excel file

books = pd.read_excel('book1.xlsx', index_col='ID')

print(books)

books['总价'] = books['单价'] * books['数量']

Alternatively, a loop can assign values row by row:

for i in books.index: books['总价'].at[i] = books['单价'].at[i] * books['数量'].at[i]

To increase the price by 2 units, two methods are shown:

books['单价'] = books['单价'] + 2

books['单价'] = books['单价'].apply(lambda x: x + 2)

Sorting data with DataFrame.sort_values is demonstrated, including parameter explanations.

products.sort_values(by='价格', inplace=True, ascending=False)

Filtering rows using loc and apply functions is illustrated to select students aged 18‑20 with scores 85‑100.

def age18_20(age): return 18 <= age <= 20

def level_s(score): return 85 <= score <= 100

stu = pd.read_excel('students.xlsx', index_col='ID')

stu = stu.loc[stu['年龄'].apply(age18_20)].loc[stu['成绩'].apply(level_s)]

Finally, a simple bar chart is generated with matplotlib to visualize student scores.

import matplotlib.pyplot as plt

stu.sort_values(by='成绩', inplace=True)

stu.plot.bar(x='姓名', y='成绩', color='orange', title='学生成绩表')

plt.tight_layout()

plt.show()