CODEWITHSUNDEEP
pythonpandas
Matias Salimbene
Matias Salimbene

Reputation: 605

Calculating monthly aggregate of expenses with pandas

I've got a file with a list of expenses:

Sample: 

date        item                        out     in
12/01/2017  PAGO FIBERTEL               668.5   0.0
12/01/2017  PAGO GAS NATURAL            2.32    0.0
10/02/2017  EXTRACCION TARJETA          1200.0  0.0
10/02/2017  CPA. STARBUCKS R. PE9A      105.0   0.0
10/02/2017  CPA. STARBUCKS R. PE9A      125.0   0.0
11/03/2017  EXTRACCION TARJETA          1200.0  0.0
11/03/2017  SALES                       0.0     10000.0

I want to produce a plot where I can see how some items evolved during a year, each month. For example, i would filter the "item" column using "startbucks" as keyword, i would calculate monthly aggregates and would present that info like this:

             Dec   Jan Mar
Starbucks    0     0   230

There's a list of keywords that i take from json file, and that i would use to produce each row. However, I'm not able to do it with only one. I've tried several forms of groupby (with grouper and without), but I think I'm not getting it. This is the code I've got at the moment:

import pandas as pd
import matplotlib.pyplot as plt
import sys
import json

class Banca():
def __init__(self, name, csv_path, json_path):
    self.name= name
    self.df = pd.read_csv(csv_path)
    with open(json_path) as j:
        self.json = json.load(j)

def prepare(self):
    #Add header
    headers = ['fecha','concepto','in','out',"x"]
    self.df.columns = headers

    #fix data
    self.df.fecha = pd.to_datetime(self.df.fecha)


    #Here i'm testing, this doesnt work
    g1=self.df.groupby(pd.Grouper(key='fecha', freq='M')['in'].sum())
    print(g1.describe().to_string())
    print(g1.head())

    #g1.plot(y='out', style='.-', figsize=(15,4))
    #plt.show()
    #filter data
    # some filter

def grafica(self):
    #plot data
    self.df.plot(x='fecha', y='out',style='.-', figsize=(15,4))
    plt.show()

def test_df(self):
    print(self.df.describe(include='all'))

def test_json(self):
    for x,y in self.json.items():
        print(x,y)



icbc = Banca("ICBC", sys.argv[1], sys.argv[2])
icbc.test_df()
icbc.prepare()
#icbc.grafica()
#icbc.test_json()

I'm writing this code as an exercise to learn data manipulation with pandas. I've learn quite a few articules but I've been stuck here for a while now. I'm thinking perhaps i'm not supposed to use groupby for this, but something else. Anyway, I appreciate any help.

Upvotes: 1

Views: 1253

Answers (1)

jezrael
jezrael

Reputation: 863166

Use:

#convert column to datetimes if necessary
df['fecha'] = pd.to_datetime(df['fecha'], format='%d/%m/%Y')
print(df)
       fecha                concepto       in      out
0 2017-01-12           PAGO FIBERTEL   668.50      0.0
1 2017-01-12        PAGO GAS NATURAL     2.32      0.0
2 2017-02-10      EXTRACCION TARJETA  1200.00      0.0
3 2017-02-10  CPA. STARBUCKS R. PE9A   105.00      0.0
4 2017-02-10  CPA. STARBUCKS R. PE9A   125.00      0.0
5 2017-03-11      EXTRACCION TARJETA  1200.00      0.0
6 2017-03-11                   SALES     0.00  10000.0

import re

#create DatetimeIndex
df = df.set_index('fecha')

#list of values
L = ['starbuck','pago']
all_s = []
for x in L:
    #filter by substrings, select column in
    s = df.loc[df['concepto'].str.contains(x, flags=re.I), 'in']
    #aggregate by months and sum
    s = s.groupby(pd.Grouper(freq='M')).sum()
    #change format of index by `MM-YYYY`
    s.index = s.index.strftime('%b-%Y')
    all_s.append(s.rename(x))

#join all Series together and transpose 
df = pd.concat(all_s, axis=1).T
print (df)
          Feb-2017  Jan-2017
starbuck     230.0       NaN
pago           NaN    670.82

EDIT:

For ploting should be better plot DatetimeIndex and columns by keywords, also group by MS for start of months and add asfreq if want add missing months filled by 0:

df['fecha'] = pd.to_datetime(df['fecha'], format='%d/%m/%Y')
print(df)
       fecha                concepto       in      out
0 2017-01-12           PAGO FIBERTEL   668.50      0.0
1 2017-01-12        PAGO GAS NATURAL     2.32      0.0
2 2017-02-10      EXTRACCION TARJETA  1200.00      0.0
3 2017-02-10  CPA. STARBUCKS R. PE9A   105.00      0.0
4 2017-02-10  CPA. STARBUCKS R. PE9A   125.00      0.0
5 2017-03-11      EXTRACCION TARJETA  1200.00      0.0
6 2017-05-11                   SALES    20.00  10000.0 <-changed last month

import re

df = df.set_index('fecha')

L = ['starbuck','pago', 'sales']
all_s = []
for x in L:
    s = df.loc[df['concepto'].str.contains(x, flags=re.I), 'in']
    s = s.groupby(pd.Grouper(freq='MS')).sum()
    all_s.append(s.rename(x))

df = pd.concat(all_s, axis=1).fillna(0).asfreq('MS', fill_value=0)
print (df)
            starbuck    pago  sales
fecha                              
2017-01-01       0.0  670.82    0.0
2017-02-01     230.0    0.00    0.0
2017-03-01       0.0    0.00    0.0
2017-04-01       0.0    0.00    0.0
2017-05-01       0.0    0.00   20.0

df.plot(style='.-', figsize=(15,4))

Upvotes: 1

Related Questions