Skip to content
  • AI Chat
  • Code
  • Report
  • Spinner

    Analysing expenses spreadsheet

    Main questions:

    • Avg earning/spending
    • Max earned/max spent
    • Pivot table with spending per month in each category
    • Pivot table of percent of whole salary spent each month in each category
    import pandas as pd
    df = pd.read_csv('')
    expenses = pd.DataFrame(df)

    Preparing the data

    Changing the data types accordingly

    expenses['date'] = pd.to_datetime(expenses['date'], format='%d/%m/%Y')
    expenses.type = expenses.type.astype('category')
    expenses.category = expenses.category.astype('category')
    expenses.who = expenses.who.astype('category')
    expenses.card_account = expenses.card_account.astype('category')
    expenses.income_expense = expenses.income_expense.astype('category')

    Merging same categories

    expenses['category'] = expenses['category'].replace('Beauty/Health/medical', 'Beauty/Health/Medical')
    Hidden output
    import matplotlib.pyplot as plt
    expenses.plot(kind = 'scatter', x = 'date', y = 'amount')

    Average income x average expenses

    expenses['mm/yyyy'] = expenses['date'].dt.strftime('%m/%Y')
    expenses = expenses.drop(['month_year'], axis=1)
    expenses = expenses.rename(columns={'mm/yyyy': 'month_year'})