Python Pandas - Pd.melt A Dataframe With Datetime Index Results In Nan

I have the following dataframe (sim_2005): Date ELEM1 ELEM2 ... ELEM1133 2005-01-01 0.021 2.455 ... 345.2 2005-01-02 0.321 2.331 ... 355.1 ... ... ... ...

Solution 1:

Assuming Date is the index to your DataFrame, you can get a date column in your melted DataFrame as follows:

sim_2005_melted['Date'] = pd.concat([sim_2005.reset_index().Datefor _ inrange(sim_2005.shape[1])], 
                                    ignore_index=True).values

Solution 2:

Here is one way to use .stack() to solve your question.

import pandas as pd
import numpy as np

# try to simulate your data
columns = ['ELEM' + str(x) for x in np.arange(1, 1134, 1)]
sim_2005 = pd.DataFrame(np.random.randn(365, 1133), index=pd.date_range('2005-01-01', periods=365, freq='D'), columns=columns)

processed_sim_2005 = sim_2005.stack().reset_index()
processed_sim_2005.columns = ['Date', 'ELEM', 'Q_sim']

Out[82]: 
             Date      ELEM   Q_sim
02005-01-01     ELEM1  0.622112005-01-01     ELEM2  0.186222005-01-01     ELEM3 -1.073632005-01-01     ELEM4 -0.975642005-01-01     ELEM5  0.8397...           ...       ...     ...
4135402005-12-31  ELEM1129  0.03454135412005-12-31  ELEM1130  0.55224135422005-12-31  ELEM1131 -0.69004135432005-12-31  ELEM1132 -0.22694135442005-12-31  ELEM1133  0.1243

[413545 rows x 3 columns]

Solution 3:

A possibly simpler solution still using .melt() is to pull your date index out into a column with .reset_index() first:

sim_2005_melted = pd.melt(sim_2005.reset_index(), id_vars=sim_2005.index.name, value_vars=list(sim_2005.columns.values), var_name='ELEM', value_name='Q_sim')

You get the same result with .stack() but this way is a bit more flexible if you want all the extra melty goodness.