title: pandas applying multicolumnindex to dataframe
body: <p>The situation is that I have a few files with time_series data for various stocks with several fields. each file contains</p>
  
  <pre><code>time, open, high, low, close, volume
  </code></pre>
  
  <p>the goal is to get that all into one dataframe of the form</p>
  
  <pre><code>field      open                              high                            ...
  security    hk_1      hk_2      hk_3 ...      hk_1      hk_2      hk_3 ...  ...
  time
  t_1      open_1_1  open_2_1  open_3_1 ...  high_1_1  high_2_1  high_3_1 ...  ...            
  t_2      open_1_2  open_2_2  open_3_2 ...  high_1_2  high_2_2  high_3_2 ...  ...
  ...        ...        ...       ... ...       ...       ...       ... ...  ...
  </code></pre>
  
  <p>I created a multiindex</p>
  
  <pre><code>fields = ['time','open','high','low','close','volume','numEvents','value']
  midx = pd.MultiIndex.from_product([security_name'], fields], names=['security', 'field'])
  </code></pre>
  
  <p>and for a start, tried to apply that multiindex to the dataframe I get from reading the data from csv (by creating a new dataframe and adding the index)</p>
  
  <pre><code>for c in eqty_names_list:
  
      midx = pd.MultiIndex.from_product([[c], fields], names=['security', 'field'])
  
      df_temp = pd.read_csv('{}{}.csv'.format(path, c))
      df_temp = pd.DataFrame(df_temp, columns=midx, index=df_temp['time'])
      df_temp.df_name = c
      all_dfs.append(df_temp)
  </code></pre>
  
  <p>However, the new dataframe only contains nan</p>
  
  <pre><code>security    1_HK
  field       time    open    high    low     close   volume
  time                                
   NaN         NaN     NaN     NaN    NaN       NaN      NaN
  </code></pre>
  
  <p>Also, it still contains a column for time, although I tried to make that the index (so that I can later join all the other dataframes for other stocks by index to get the aggregated dataframe).</p>
  
  <p>How can I apply the multiindex to the dataframe without losing my data and then later join the dataframes looking like this</p>
  
  <pre><code>security    1_HK
  field       time    open    high    low     close   volume
  time
  </code></pre>
  
  <p>to create something like (note hierarchy field and security are switched)</p>
  
  <pre><code>field       time                open    high        ...
  security    1_HK    2_HK ...    1_HK    2_HK ...    ...
  time
  </code></pre>