Pandas Replicate Date Columns

Question

I have dataframewith dates in the form of columns. I would like to average the values ​​from daily to monthly. I tried Time Grouper and Resample, but I don't like the column names, and I can figure out how to make the columns something like DatetimeIndex.

My initial data frame:

import pandas as pd

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

Output Required:

   2013-01-01  2013-02-01
A         1.5         3.5
B         5.6         7.5

Answer 1

you can use resample

df.columns = pd.to_datetime(df.columns)
df.T.resample('M').mean().T
Out[409]: 
   2013-01-31  2013-02-28
A         1.5         3.5
B         5.5         7.5

Or groupbyone

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

Answer 2

First convert to a datetime column index using pd.to_datetime, then use Tand groupbyto pd.Grouper(note Note pd.TimeGerouper outdated pd.Grouper.):

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

Conclusion:

   2013-01-01  2013-02-01
A         1.5         3.5
B         5.5         7.5

Answer 3

pd.PeriodIndex:

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

Answer 4

:

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 this helps!

Answer 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-01  2013-02-03
A         1.5         3.5
B         5.5         7.5

I do not understand your desired result: