Convert a list of values to a time series in python

Question

I want to convert the foll. data:

jan_1   jan_15  feb_1   feb_15  mar_1   mar_15  apr_1   apr_15  may_1   may_15  jun_1   jun_15  jul_1   jul_15  aug_1   aug_15  sep_1   sep_15  oct_1   oct_15  nov_1   nov_15  dec_1   dec_15
0       0       0       0       0       1       1       2       2       2       2       2       2        3      3       3       3       3       0       0       0       0       0       0

into a array of length 365, where each element is repeated till the next date days e.g. 0 is repeated from january 1 to january 15...

I could do something like numpy.repeat, but that is not date aware, so would not take into account that less than 15 days happen between feb_15 and mar_1.

Any pythonic solution for this?

Answer 1

You can use resample:

#add last value - 31 dec by value of last column of df 
df['dec_31'] = df.iloc[:,-1]

#convert to datetime - see http://strftime.org/
df.columns = pd.to_datetime(df.columns, format='%b_%d')

#transpose and resample by days
df1 = df.T.resample('d').ffill()
df1.columns = ['col']
print (df1)

          col  
1900-01-01  0
1900-01-02  0
1900-01-03  0
1900-01-04  0
1900-01-05  0
1900-01-06  0
1900-01-07  0
1900-01-08  0
1900-01-09  0
1900-01-10  0
1900-01-11  0
1900-01-12  0
1900-01-13  0
1900-01-14  0
1900-01-15  0
1900-01-16  0
1900-01-17  0
1900-01-18  0
1900-01-19  0
1900-01-20  0
1900-01-21  0
1900-01-22  0
1900-01-23  0
1900-01-24  0
1900-01-25  0
1900-01-26  0
1900-01-27  0
1900-01-28  0
1900-01-29  0
1900-01-30  0
       ..
1900-12-02  0
1900-12-03  0
1900-12-04  0
1900-12-05  0
1900-12-06  0
1900-12-07  0
1900-12-08  0
1900-12-09  0
1900-12-10  0
1900-12-11  0
1900-12-12  0
1900-12-13  0
1900-12-14  0
1900-12-15  0
1900-12-16  0
1900-12-17  0
1900-12-18  0
1900-12-19  0
1900-12-20  0
1900-12-21  0
1900-12-22  0
1900-12-23  0
1900-12-24  0
1900-12-25  0
1900-12-26  0
1900-12-27  0
1900-12-28  0
1900-12-29  0
1900-12-30  0
1900-12-31  0

[365 rows x 1 columns]

#if need serie
print (df1.col)
1900-01-01    0
1900-01-02    0
1900-01-03    0
1900-01-04    0
1900-01-05    0
1900-01-06    0
1900-01-07    0
1900-01-08    0
1900-01-09    0
1900-01-10    0
1900-01-11    0
1900-01-12    0
1900-01-13    0
1900-01-14    0
1900-01-15    0
1900-01-16    0
1900-01-17    0
1900-01-18    0
1900-01-19    0
1900-01-20    0
1900-01-21    0
1900-01-22    0
1900-01-23    0
1900-01-24    0
1900-01-25    0
1900-01-26    0
1900-01-27    0
1900-01-28    0
1900-01-29    0
1900-01-30    0
             ..
1900-12-02    0
1900-12-03    0
1900-12-04    0
1900-12-05    0
1900-12-06    0
1900-12-07    0
1900-12-08    0
1900-12-09    0
1900-12-10    0
1900-12-11    0
1900-12-12    0
1900-12-13    0
1900-12-14    0
1900-12-15    0
1900-12-16    0
1900-12-17    0
1900-12-18    0
1900-12-19    0
1900-12-20    0
1900-12-21    0
1900-12-22    0
1900-12-23    0
1900-12-24    0
1900-12-25    0
1900-12-26    0
1900-12-27    0
1900-12-28    0
1900-12-29    0
1900-12-30    0
1900-12-31    0
Freq: D, Name: col, dtype: int64

#transpose and convert to numpy array
print (df1.T.values)
[[0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
  0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1
  1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 2 2 2 2 2 2 2
  2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2
  2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2
  2 2 2 2 2 2 2 2 2 2 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3
  3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3
  3 3 3 3 3 3 3 3 3 3 3 3 3 3 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
  0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
  0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0]]