Pandas Resample On Date Columns

I have a dataframe with dates as columns. I'd like to average the values from daily to a monthly level. I've tried with Time Grouper and Resample, but it doesn't like the columns n

Solution 1:

You can using resample

df.columns = pd.to_datetime(df.columns)
df.T.resample('M').mean().T
Out[409]: 
   2013-01-312013-02-28
A         1.53.5
B         5.57.5

Or groupby one

axis=1 
df.groupby(pd.to_datetime(df.columns).to_period('M'),1).mean()
Out[412]: 
   2013-01  2013-02
A      1.5      3.5
B      5.5      7.5

Solution 2:

you can make use of pd.PeriodIndex:

In [145]: df.groupby(pd.PeriodIndex(df.columns, freq='M'), axis=1).mean()
Out[145]:
   2013-012013-02
A      1.53.5
B      5.57.5

Solution 3:

First, convert column index to datetime with pd.to_datetime, then use T and groupby with pd.Grouper (note pd.TimeGerouper is deprecated use pd.Grouper):

df.columns = pd.to_datetime(df.columns)
df.T.groupby(by=pd.Grouper(freq='MS')).mean().T

Output:

2013-01-012013-02-01A1.53.5B5.57.5

Solution 4:

Try converting the column names to date first:

df = pd.DataFrame(data=[[1,2,3,4],[5,6,7,8]], columns=pd.to_datetime(['2013-01-01', '2013-01-02', '2013-02-03', '2013-02-04']), index=['A', 'B'])

Hope it helps!

Solution 5:

import pandas as pd

list=df.columns
df_new = pd.DataFrame()

for i in range(int(0.5*len(list))):
    df_new[list[2*i]] = (df[[list[2*i], list[2*i+1]]].mean(axis=1))

Output

2013-01-012013-02-03A1.53.5B5.57.5

I dont understand your Desired Output: