CODEWITHSUNDEEP
pythonpandas
diogenes
diogenes

Reputation: 2149

Pandas get the Month Ending Values from Series

I need to get the month-end balance from a series of entries.

Sample data:

           date     contrib   totalShrs
0    2009-04-23     5220.00   10000.000
1    2009-04-24    10210.00   20000.000
2    2009-04-27    16710.00   30000.000
3    2009-04-30    22610.00   40000.000
4    2009-05-05    28909.00   50000.000
5    2009-05-20    38409.00   60000.000
6    2009-05-28    46508.00   70000.000
7    2009-05-29    56308.00   80000.000
8    2009-06-01    66108.00   90000.000
9    2009-06-02    78108.00  100000.000
10   2009-06-12    86606.00  110000.000
11   2009-08-03    95606.00  120000.000

The output would look something like this:

2009-04-30   40000
2009-05-31   80000
2009-06-30  110000 
2009-07-31  110000  
2009-08-31  120000

Is there a simple Pandas method?

I don't see how I can do this with something like a groupby?

Or would I have to do something like iterrows, find all the monthly entries, order them by date and pick the last one?

Thanks.

Upvotes: 1

Views: 900

Answers (2)

rnso
rnso

Reputation: 24623

Following gives you the information you want, i.e. end of month values, though the format is not exactly what you asked: 

df['month'] = df['date'].str.split('-', expand = True)[1]   # split date column to get month column
newdf = pd.DataFrame(columns=df.columns) # create a new dataframe for output
grouped = df.groupby('month') # get grouped values
for g in grouped:  # for each group, get last row
    gdf = pd.DataFrame(data=g[1])
    newdf.loc[len(newdf),:] = gdf.iloc[-1,:]  # fill new dataframe with last row obtained
newdf = newdf.drop('date', axis=1)  # drop date column, since month column is there
print(newdf)

Output: 

  contrib totalShrs month
0   22610     40000    04
1   56308     80000    05
2   86606    110000    06
3   95606    120000    08

Upvotes: 0

jezrael
jezrael

Reputation: 863741

Use Grouper with GroupBy.last, forward filling missing values by ffill with Series.reset_index:

#if necessary
#df['date'] = pd.to_datetime(df['date'])

df = df.groupby(pd.Grouper(freq='m',key='date'))['totalShrs'].last().ffill().reset_index()
#alternative
#df = df.resample('m',on='date')['totalShrs'].last().ffill().reset_index()
print (df)
        date  totalShrs
0 2009-04-30    40000.0
1 2009-05-31    80000.0
2 2009-06-30   110000.0
3 2009-07-31   110000.0
4 2009-08-31   120000.0

Upvotes: 1

Related Questions