title: How to transform repeating numbered columns in Pandas to singular, non-numbered columns?
body: <p>Apologies for the (potentially) poor title, but I have a hard time understanding how to describe this myself. I have a dataset that, when read into a pandas dataframe, has roughly the following shape: </p>
  
  <pre><code>import pandas as pd 
  import numpy as np
  df_all = pd.DataFrame(np.random.randn(2, 6), columns=["0_X", "0_Y", "1_X", "1_Y", "2_X", "2_Y"])
    0_X       0_Y       1_X       1_Y       2_X       2_Y
  0 1.470289  0.588573  1.303684  1.374806  1.025082  0.316623
  1 0.426527  2.036558  0.100993  2.485025  0.350100  0.603069
  </code></pre>
  
  <p>Each pair of columns with the same number represent the (X, Y) positions for an object at a given timestamp. Each row represents a new timestamp. What I would like to do is transform this dataframe into something like this:</p>
  
  <pre><code>   Time   ObjectId   X         Y 
  0  0      0          1.470289  0.588573  
  1  0      1          1.303684  1.374806  
  2  0      2          1.025082  0.316623
  3  1      0          0.426527  2.036558  
  4  1      1          0.100993  2.485025  
  5  1      2          0.350100  0.603069
  </code></pre>
  
  <p>Now, I know I can extract the relevant information from the column <strong>names</strong> and repeat it like so: </p>
  
  <pre><code>obj_ids = []
  for each_column in list(df_all.columns):
    obj_id = each_column.split("_")[0]
    if obj_id not in obj_ids:
      obj_ids.append(obj_id)
  
  df_all_rotated = pd.DataFrame()
  df_all_rotated["ObjectID"] = obj_ids 
  df_all_rotated = pd.concat([df_all_rotated ] * len(df_all.index), ignore_index=True)
  </code></pre>
  
  <p>This produces something close to the first part of what I want: </p>
  
  <pre><code>      ObjectId   
  0     0
  1     1
  2     2
  3     0
  4     1
  5     2
  </code></pre>
  
  <p>But unfortunately, I get stuck when thinking how to move the (X,Y) points to their proper positions in the dataframe. I know there are ways of doing this by looping over the entirety of the dataframe and setting each dataframe cell to its respective value, but those seem inefficient especially considering that the datasets will be in the MBs of information, and I have bad (runtime) experiences with using "C-like" looping methods in Pandas. </p>
  
  <p>The easy answer is "change the dataset" but sadly I do not control how that is generated :(</p>
  
  <p>Any help is appreciated! My apologies if this is a repost.</p>