2. pandas: Cleaning Data

In Python in Excel, a range becomes a pandas table, a DataFrame. Typical cleanup of an export takes a few lines:

df = xl("A1:B9", headers=True)
df["City"] = df["City"].str.strip().str.title()   # trim spaces, capitalize "Austin"
df["Amount"] = pd.to_numeric(df["Amount"])        # text "1200" -> number 1200
df = df.dropna(subset=["Amount"])                 # drop rows with no amount
len(df), df["Amount"].sum(), df["Amount"].mean()

• str.strip() is like TRIM; str.title() is like PROPER;
• pd.to_numeric or astype(float) is like VALUE: a number stored as text becomes a number;
• dropna() removes empty values (NaN). The functions sum() and mean() skip empties anyway, just like SUM and AVERAGE.

A good habit is to check Python's result against ordinary formulas at least once. In this lesson you'll repeat the cleanup with formulas and get the same three numbers that the last line of code returns.