Master Statistical Analysis in Excel & Python: Formulas and Pandas Code

Introduction

A fan asked how to implement statistical calculations on Excel data using Python. The author shares two practical solutions: one using Excel formulas and another using Python with Pandas.

Method 1: Excel Formulas

Enter the following formulas directly in Excel cells and fill down to calculate the required statistics.

=SUM(B2:GG2)/COUNTIF(B2:GG2,"<>0")<br/>=STDEVA(IF(B2:GG2=0,"",B2:GG2))

Method 2: Python with Pandas

Using Pandas simplifies the process. The script reads the Excel file, computes total, mean (excluding zeros), and standard deviation for each row, and prints the resulting DataFrame.

import pandas as pd
df = pd.read_excel('产品周需求.xlsx', usecols='A:GG', index_col=0)
df['total'] = df.iloc[:, :188].sum(axis=1)
df['mean'] = df.iloc[:, :188].apply(lambda x: x[x != 0].mean(), axis=1)
df['std'] = df.iloc[:, :188].apply(lambda x: x[x != 0].std(), axis=1)
print(df)

Conclusion

Both the Excel formulas and the Python Pandas script produce identical results, providing flexible options for performing statistical analysis on Excel datasets.