python - convert datetimeindex to Qx-YY format -

i have csv file table looks like

date        open 11/1/2016   59.970001 10/3/2016   57.41 9/1/2016    57.009998 8/1/2016    56.599998 7/1/2016    51.130001 6/1/2016    52.439999 5/2/2016    50 4/1/2016    55.049999 

i need quarterly date rows (mar, jun, sep, dec) , convert date columns q1-16/ q2-16/ q3-16 etc.

code:

df_sp = pd.read_csv(shareprice,  index_col = 'date', parse_dates =[0]) df_q= df_sp.groupby(pd.timegrouper('q')).nth(-1) df_q['qx-yy'] = ???? 

you can use series.dt.to_period , dt.quarter dt.year, first need convert index.to_series:

df = df.groupby(df.date.dt.to_period('q')).open.mean() print (df) date 2016q2    52.496666 2016q3    54.913332 2016q4    58.690000 freq: q-dec, name: open, dtype: float64  df.index = 'q' + df.index.to_series().dt.quarter.astype(str) + '-'                 + df.index.to_series().dt.year.astype(str).str[2:] print (df) date q2-16    52.496666 q3-16    54.913332 q4-16    58.690000 name: open, dtype: float64 

another solution:

df = df.groupby(df.date.dt.to_period('q')).open.mean() print (df) date 2016q2    52.496666 2016q3    54.913332 2016q4    58.690000 freq: q-dec, name: open, dtype: float64  y = df.index.strftime('%y') df.index = df.index.quarter.astype(str) df.index = 'q' + df.index + '-' + y print (df) q2-16    52.496666 q3-16    54.913332 q4-16    58.690000 name: open, dtype: float64 

the best use period.period.strftime - link old documentation works well:

df = df.groupby(df.date.dt.to_period('q')).open.mean() print (df) date 2016q2    52.496666 2016q3    54.913332 2016q4    58.690000 freq: q-dec, name: open, dtype: float64  df.index = df.index.strftime('q%q-%y') print (df) q2-16    52.496666 q3-16    54.913332 q4-16    58.690000 name: open, dtype: float64