Using Python pandas for Excel Data Processing and Mathematical Operations

In data processing, Excel is popular, but Python with pandas offers more efficient batch and automated operations.

1. Read Excel file Use pandas.read_excel to load the workbook.

import pandas as pd
# Read Excel file
df = pd.read_excel('example.xlsx')
print(df.head())

2. Basic arithmetic Perform addition, subtraction, multiplication, division on DataFrame columns.

# Add two columns
df['Total'] = df['Column1'] + df['Column2']
# Subtract
df['Difference'] = df['Column1'] - df['Column2']
# Multiply
df['Product'] = df['Column1'] * df['Column2']
# Divide
df['Quotient'] = df['Column1'] / df['Column2']

3. Mathematical functions Apply numpy functions such as sin and log.

# Apply math functions
df['SinColumn1'] = df['Column1'].apply(np.sin)
df['LogColumn2'] = df['Column2'].apply(np.log)

4. Statistical functions Compute mean, standard deviation, and other statistics.

# Compute average
average = df['Column1'].mean()
# Compute standard deviation
std_dev = df['Column1'].std()

5. Custom function Define and apply a custom function.

def custom_function(x):
    return x ** 2 if x > 0 else 0

df['SquaredColumn1'] = df['Column1'].apply(custom_function)

6. Handle missing values Fill NaNs before calculations.

df.fillna(0, inplace=True)

7. Save results Write the modified DataFrame back to an Excel file.

df.to_excel('modified_example.xlsx', index=False)

The full example also shows how to work with Chinese column headers, perform the same arithmetic and mathematical operations, and produce a new Excel file containing the original data plus the computed columns.