-3
$\begingroup$

I have a data set recording daily values of several metrics say R1, R2, for example:

Date     Metric cur_val     Mgmt_Lmt
1/1/2019    R1  38.94927536 100
1/2/2019    R1  38.83188406 100
1/3/2019    R1  38.71449275 100
1/4/2019    R1  38.59710145 100
1/5/2019    R1  38.47971014 100
1/6/2019    R1  38.36231884 100

I am trying to get the data in below format: basically agg by month year

    MGMT_LmtAgg Jan-19  Feb-19  Mar-19  
    100 min 80  80  80  
R1      max 90  90  90  
        avg 85  85  85  
    75  min 80  80  80  
R2      max 90  90  90  
        avg 85  85  85

I am trying df.groupby([pd.Grouper(freq='M'), 'Metric']).agg({'cur_val': ['sum', 'mean', 'min', 'last']}).T

this gives me R1 and R2 as columns, I want to see as rows. Could you help? I am beginner in python. Thanks!

$\endgroup$
2
  • $\begingroup$ Hello, welcome to DS Exchange. Have you tried to remove the transpose method? (T) Try using df.groupby([pd.Grouper(freq='M'), 'Metric']).agg({'cur_val': ['sum', 'mean', 'min', 'last']}) only, if I understood correctly, you will have metric as rows. $\endgroup$ – Victor Oliveira Sep 11 '19 at 19:59
  • $\begingroup$ Thanks for the reply. I want to see Month-Year as columns. $\endgroup$ – user82041 Sep 11 '19 at 20:06
0
$\begingroup$

I think the way you want to see your data is best achieved by using pivot tables.

Try the following code:

pd.pivot_table(df, index=['Metric', 'Mgmt_Lmt'], 

columns=pd.Grouper(freq='M'), values='cur_val', aggfunc=['sum', 'mean', 'min', 'last'])

This will index your table by metric and mgmt and give Month-Year as columns:

                               sum     mean           min     last
                 Date   2019-01-31  2019-01-31  2019-01-31  2019-01-31
Metric  Mgmt_Lmt                
R1       100            155.092754  38.773188   38.597101   38.597101
R2       100             76.842029  38.421014   38.362319   38.362319

By doing df_pivoted.stack(level=0) you will have:

                    Date    2019-01-31
Metric  Mgmt_Lmt        
R1           100     sum    155.092754
                    mean    38.773188
                     min    38.597101
                    last    38.597101
R2           100     sum    76.842029
                    mean    38.421014
                     min    38.362319
                    last    38.362319
$\endgroup$
1
  • $\begingroup$ Yup. Its working. Can't thankyou enough. Really appreciate your time trying to help others! $\endgroup$ – user82041 Sep 11 '19 at 20:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.