Mean of values Monthly in Dataframe with date as column

Question

Thanks for investing time to help me out :)

I have DataFrame (df_NSE_Price_) like below:

Company Name                ID      2000-01-03 00:00:00 2000-01-04 00:00:00 ....
Reliance Industries Ltd.    100325  50.810              54.
Tata Consultancy Service    123455  123                 125
..

I would want output like below :

Company Name                ID      March 00   April 00 .....
Reliance Industries Ltd    100325   52         55
Tata Consultancy Services  123455   124.3      124
..

The output data has to have the average of data month wise.

So far i have tried

df_NSE_Price_.resample('M',axis=1).mean()

But this gave me error Only valid with DatetimeIndex, TimedeltaIndex or PeriodIndex, but got an instance of 'Index'

Question is unclear, can you add a sample input and output and explain how to reach the output from input. — Animesh Mukherkjee, Jul 25 '20 at 09:49
Does this mean, on your NSE_Price df, the columns represent days? — Martin Müsli, Jul 25 '20 at 11:38

score 1 · Answer 1 · answered Jul 25 '20 at 11:52

1

Something like this should work: df.transpose().resample('M',axis=1).mean().transpose()

answered Jul 25 '20 at 11:52

Yash

21
4

Only valid with DatetimeIndex, TimedeltaIndex or PeriodIndex, but got an instance of 'RangeIndex' > gave the same error – Ritika Mehta Jul 25 '20 at 22:18

score 0 · Accepted Answer · answered Jul 26 '20 at 01:38

First, I converted the data to a Data Frame (I added a column with February info, too).

import pandas as pd

columns = ('Company Name', 
           'ID', 
           '2000-01-03 00:00:00', 
           '2000-01-04 00:00:00', 
           '2000-02-04 00:00:00')

data = [('Reliance Industries Ltd.', 100325, 50.810, 54., 66.0),
        ('Tata Consultancy Service', 123455, 123, 125, 130.0),]

df = pd.DataFrame(data=data, columns=columns)

Second, I created a two-level index (MultiIndex), using Company and ID. Now, all column labels are dates. Then, I converted the column labels to date format (using .to_datetime()

df = df.set_index(['Company Name', 'ID'])
df.columns = pd.to_datetime(df.columns)

Third, I re-sampled in monthly intervals, using the 'axis=1' to aggregate by column. This creates one month per column. Convert from month-end dates to periods with 'to_period()':

df = df.resample('M', axis=1).sum()
df.columns = df.columns.to_period('M')

                                 2000-01  2000-02
Company Name             ID                      
Reliance Industries Ltd. 100325   104.81     66.0
Tata Consultancy Service 123455   248.00    130.0

Mean of values Monthly in Dataframe with date as column

2 Answers2