python - Best Way To Unpivot a Pandas Dataframe -

i have data missing values on weekends, public holidays etc.

 datadate  | id | value  ----------------------- 1999-12-31 | 01 |  1.0  1999-12-31 | 02 |  0.5 1999-12-31 | 03 |  3.2 2000-01-04 | 01 |  1.0 2000-01-04 | 02 |  0.7 2000-01-04 | 03 |  3.2 

and want copy values down on dates data missing. so, i've pivoted frame, re-indexed, , copied values down.

 datadate  | 01  | 02  | 03  ---------------------------- 1999-12-31 | 1.0 | 0.5 | 3.2 2000-01-01 | 1.0 | 0.5 | 3.2 2000-01-02 | 1.0 | 0.5 | 3.2 2000-01-03 | 1.0 | 0.5 | 3.2 2000-01-04 | 1.0 | 0.7 | 3.2 

now want return data original form. i've tried using pd.melt(), , df.unstack(), i'm ending more columns want, , constructing new data frame result taking long time.

is there better way unpivot data ?

there pandas.pivot_table function , if define datadate , id indices, can unstack dataframe.

that'd be:

from io import stringio import pandas  datatable = stringio("""\ datadate  | id | value  1999-12-31 | 01 |  1.0  1999-12-31 | 02 |  0.5 1999-12-31 | 03 |  3.2 2000-01-04 | 01 |  1.0 2000-01-04 | 02 |  0.7 2000-01-04 | 03 |  3.2""")  fullindex = pandas.datetimeindex(freq='1d', start='1999-12-31', end='2000-01-06') df = (     pandas.read_table(datatable, sep='\s+\|\s+', parse_dates=['datadate'])           .set_index(['datadate', 'id'])           .unstack(level='id')           .reindex(fullindex)           .fillna(method='ffill')           .stack()           .reset_index()           .rename(columns={'level_0': 'date'})  )  print(df) 

which gives me:

         date  id  value 0  1999-12-31   1    1.0 1  1999-12-31   2    0.5 2  1999-12-31   3    3.2 3  2000-01-01   1    1.0 4  2000-01-01   2    0.5 5  2000-01-01   3    3.2 6  2000-01-02   1    1.0 7  2000-01-02   2    0.5 8  2000-01-02   3    3.2 9  2000-01-03   1    1.0 10 2000-01-03   2    0.5 11 2000-01-03   3    3.2 12 2000-01-04   1    1.0 13 2000-01-04   2    0.7 14 2000-01-04   3    3.2 15 2000-01-05   1    1.0 16 2000-01-05   2    0.7 17 2000-01-05   3    3.2 18 2000-01-06   1    1.0 19 2000-01-06   2    0.7 20 2000-01-06   3    3.2 

(i chaining)