Pandas create month end holding from activity

Question

I am trying to find the month-end balance amounts based on the "issues" held from the df_td list of activity.

Actually I am just looking to find the count of "issues" with a positive balance at the end of each month.

To do this I need to create the month-end balance based on each "issues" "action" and "shares" on activity less than the month-end date. So the total count of issues with a balance > 0 for each period.

The "action" is used to know if it is a buy or a sell, "+" or "-". So the balance is "+ shares" less "- shares" per each "issue."

Previously I used sql to do this but that seems like a serious waste.

What is best way to do this with Pandas?

df_td

   action code     comm    credit        date  \
0       +    P     0.00      0.00  2013-03-27   
1       +    P     0.00      0.00  2013-03-27   
2       -    S    19.00  86751.01  2013-04-08   
3       +    Z  2000.00      0.00  2013-04-09   
4       -    S    18.71    730.49  2013-04-10   

                                       issue  \
   FIDELITY REAL ESTATE INVESTMENT PORTFOLIO FUND   
                FIDELITY NJ MUNICIPAL INCOME FUND   
   FIDELITY REAL ESTATE INVESTMENT PORTFOLIO FUND   
              AMERICAN RLTY CAP HEALTHCARE TR INC   
                FIDELITY NJ MUNICIPAL INCOME FUND   

     price    shares 
0  34.4800  2462.958    
1   0.2003    60.963      
2  35.2300  2462.958     
3  10.0000  2000.000     
4  12.2900    60.960

sample of the month ends df_month

        month
0  2013-03-31
1  2013-04-30
2  2013-05-31
3  2013-06-30
4  2013-07-31

so as I loop through the months, how would I get the "balance" of each issue in the df_td?

I hope this makes sense?

Thanks.

Might be a repeat of this but look here: https://stackoverflow.com/questions/37354105/find-the-end-of-the-month-pandas-dataframe-series . Follow this and add a groupby of rows where credit > 0 — W Stokvis, Apr 28 '18 at 03:02
Expected output is the number of issues with a balance > 0 for each month period. 2013-03-31 = 2 or 2013-04-30 = 4 — diogenes, Apr 28 '18 at 03:39

score 1 · Accepted Answer · answered Apr 28 '18 at 04:21

Check if the following code works for your needs:

def get_balance(x):
    return x.comm + x.credit + x.price*x.shares*(1 if x.action == '+' else -1)

df['balance'] = df.apply(get_balance, axis=1)
df.query('balance>0').set_index('date').resample('M').agg({'issue': 'nunique', 'balance': np.sum})

* Note *

make sure your date field is in the right format (i.e. datetime64[ns])

df['date'] = pd.to_datetime(df['date'], format="%Y-%m-%d")
you might move .query('balance>0') to the end of the chain if the total balance of the issues is the concern.

Tested: Python 3.6.4 + Pandas 0.22.0

score 0 · Answer 2 · answered Apr 28 '18 at 05:36

I got this to work though I am sure the code might not be perfect or sexy.

first I created a "bal" to show if it was a debit or credit amount.

df_td [ 'bal' ] = np.where ( df_td [ 'action' ] == "+", df_td.shares, df_td.shares * -1 )

I then looped through the months using a groupby.

    cnt = [ ]

    for i, item in enumerate ( df.month ):

        // get the trades <= month
        df_mo = df_trd [ (df_trd.date <= item) ]

        // groupby the issue and sum the bal about
        i = df_mo.groupby ( 'issue' ) [ 'bal' ].sum ()

        // get the count where greater than 0
        c = i [ i > 0 ].count ()

        // add to list
        cnt.append ( c )

any comments on how to approve are very welcome!

thanks.

Pandas create month end holding from activity

2 Answers2