-1

I have successfully created new columns based on arithmetic for other columns but now I have a more challenging need to first select elements based on matches of multiple columns then perform math and having a problem with syntax.

ind_df_qtrly[10:16]
ticker  comp_name   per_fisc_year   per_type    per_fisc_qtr    per_end_date_x  sales   tot_revnu   zacks_x_sector_desc zacks_m_ind_desc    per_end_date_y  mkt_val
562170  AVX AVX CORP    2007    Q   1   2006-06-30  366.408 366.408 Computer and Technology ELECTRONICS 2008-12-31  1353.95
562215  AVX AVX CORP    2007    Q   2   2006-09-30  374.648 374.648 Computer and Technology ELECTRONICS 2008-12-31  1353.95
562260  AVX AVX CORP    2007    Q   3   2006-12-31  378.088 378.088 Computer and Technology ELECTRONICS 2008-12-31  1353.95
562305  AVX AVX CORP    2007    Q   4   2007-03-31  379.351 379.351 Computer and Technology ELECTRONICS 2008-12-31  1353.95
562350  AVX AVX CORP    2008    Q   1   2007-06-30  383.158 383.158 Computer and Technology ELECTRONICS 2008-12-31  1353.95
562395  AVX AVX CORP    2008    Q   2   2007-09-30  400.706 400.706 Computer and Technology ELECTRONICS 2008-12-31  1353.95

I am trying to calculate Trailing 12 Month Value for "tot_revnu" Column starting for Q2 of 2008, which means for every "ticker" I need to create a new Column in the DF which would sum the results of 4 Quarters (2008-Q2, 2008-Q1, 2007-Q4, & 2007-Q3). I have done the same thing to calculate growth using Annual Data but this exercise is proving to be very challenging.

I was able to do this with Annual Data like this 

        # Annual Create the Pivot Table
        ind_table=pd.pivot_table(ind_df_annual, values='tot_revnu',index=['ticker','comp_name','zacks_x_sector_desc','zacks_m_ind_desc','mkt_val'],columns=['per_fisc_year'],aggfunc=np.sum)

        # Annual calculate Simple and FD Growth and add columns to PivotTable
        ind_table[str(prev_year)+'-Ann Simple']= (ind_table[(prev_year)]  - ind_table[(prev2_year)])/ ind_table[(prev2_year)]
        ind_table[str(current_year)+'-Ann Simple']= (ind_table[current_year]  - ind_table[prev_year])/ ind_table[prev_year]
        ind_table[str(current_year)+'-Ann FD']= (ind_table[(str(current_year))+'-Ann Simple']  - ind_table[(str(prev_year))+'-Ann Simple'])/ ind_table[(str(prev_year))+'-Ann Simple']
TEKOrchestrator
  • 105
  • 8
  • 1
    *I have done the same thing to calculate growth using Annual Data* ... where is this attempt? – Parfait Jun 02 '17 at 14:02
  • Have you looked at `df.groupby`? – EFT Jun 02 '17 at 14:04
  • Thanks I have looked at df.groupby and used df.pivot_table as well. I have done this successfully with another dataset using Annual Data. I will update the question with this info, – TEKOrchestrator Jun 02 '17 at 15:37

1 Answers1

0

You could maybe use a rolling window. Something like this:

ind_df_qtrly['cumulative_tot_revnu'] = ind_df_qtrly['tot_revnu'].rolling(4).sum()

Documentation: pandas.DataFrame.rolling

André C. Andersen
  • 8,955
  • 3
  • 53
  • 79