CODEWITHSUNDEEP
pythonpandas
user8415577
user8415577

Reputation:

Creating week wise dataframe from date wise columns data frame in pandas

I have a data like this 

                                          Average    Std         Rank
Index           
('East', 'Mid', 'Equities', '2017/09/01')   7.1      2.3            5
('East', 'Mid', 'Equities', '2017/09/04')   6.4      4.2           14
('West', 'Mid', 'Equities', '2017/09/05')   6.3      4.3           16
('East', 'Mid', 'Equities', '2017/09/06')   4        1.8           18

I need to group it by week to make it look like this

Week-1                            Average  Std     Rank

East Mid Equities 2017/09/04       6.4      4.2     14  
West Mid Equities 2017/09/05       6.3      4.3     16     

Week-2
East Mid Equities 2017/09/12       8.1      1.7    25

and so on.

The columns average , std and rank are derived from some other dataframes. I just need to group the dates according to the week number (1-4) as it is a monthly data. So I need to add week-1, week-2 etc. as index here Which functions can help me in generating such dataframe? Thanks in advance

Upvotes: 1

Views: 755

Answers (1)

jezrael
jezrael

Reputation: 862541

Use resample by weekly frequency starting in Monday with aggregating:

df.index = pd.to_datetime(df.index)
df = df.resample('W-MON').agg({'Average':'mean', 'Std':'std'})
print (df)
            Average       Std
Date                         
2017-09-04     6.75  1.343503
2017-09-11     5.15  1.767767

EDIT:

print (df.index)
#MultiIndex(levels=[['East', 'West'], ['Mid'], ['Equities'], 
#                   ['2017/09/01', '2017/09/04', '2017/09/05', '2017/09/06']],
#           labels=[[0, 0, 1, 0], [0, 0, 0, 0], [0, 0, 0, 0], [0, 1, 2, 3]])

#set MultiIndex level names for later groupby
df.index.names = ('a','b','c','date')
#create DatetimeIndex
df = df.reset_index(level=[0,1,2])
df.index = pd.to_datetime(df.index)

#aggreagte, rank has to be aggregate some method like mean, sum, 
#because rank of ranks has no sense
d = {'Average':'mean', 'Std':'std', 'Rank': 'mean'}
df = df.groupby(['a','b','c']).resample('W-MON').agg(d)
print (df)
                              Average       Std  Rank
a    b   c        date                               
East Mid Equities 2017-09-04     6.75  1.343503    19
                  2017-09-11     4.00       NaN    18
West Mid Equities 2017-09-11     6.30       NaN    16

Upvotes: 1

Related Questions