title: Transpose multiple variables in rows to columns depending on a groupby using pandas
body: <p>This is referred to a questions answered before using SAS. <a href="https://stackoverflow.com/questions/25384634/sas-transpose-multiple-variables-in-rows-to-columns">SAS - transpose multiple variables in rows to columns</a></p>
  
  <p>The new thing is that the length of the variables is not two but varies. Here is an example:</p>
  
  <pre><code>   acct     la   ln  seq1  seq2
  0  9999  20.01  100     1    10
  1  9999  19.05    1     1    10
  2  9999  30.00    1     1    10
  3  9999  26.77  100     2    11
  4  9999  24.96    1     2    11
  5  8888  38.43  218     3    20
  6  8888  37.53    1     3    20
  </code></pre>
  
  <p>My desired output is:</p>
  
  <pre><code>   acct     la   ln  seq1  seq2    la0    la1  la2  la3  ln0  ln1  ln2
  5  8888  38.43  218     3    20  38.43  37.53  NaN  NaN  218    1  NaN
  0  9999  20.01  100     1    10  20.01  19.05   30  NaN  100    1    1
  3  9999  26.77  100     2    11  26.77  24.96  NaN  NaN  100    1  NaN
  </code></pre>
  
  <p>In SAS I could use proc summary which is fairly simple however I want to get it done in Python since I can't use SAS any longer.</p>
  
  <p>I already solved the question which I can reuse for my problems but I was wondering if there is an easier option in Pandas which I didn't see. Here is my solution. Would be interesting if someone has a faster approach!</p>
  
  <pre><code># write multiple row to col based on groupby
  
  import pandas as pd
  from pandas import DataFrame
  import numpy as np 
  
  data = DataFrame({
      "acct": [9999, 9999, 9999, 9999, 9999, 8888, 8888],
      "seq1": [1, 1, 1, 2, 2, 3, 3],
      "seq2": [10, 10, 10, 11, 11, 20, 20],
      "la": [20.01, 19.05, 30, 26.77, 24.96, 38.43, 37.53],
      "ln": [100, 1, 1, 100, 1, 218, 1]
      })
  
  # group the variables by some classes
  grouped = data.groupby(["acct", "seq1", "seq2"])
  
  def rows_to_col(column, size):
      # create head and contain to iterate through the groupby values
      head = []
      contain = []
      for i,j in grouped:
          head.append(i)
          contain.append(j)
  
      # transpose the values in contain
      contain_transpose = []
      for i in range(0,len(contain)):
          contain_transpose.append(contain[i][column].tolist())
  
      # determine the longest list of a sublist
      length = len(max(contain_transpose, key = len))
      # assign missing values to sublist smaller than longest list
      for i in range(0, len(contain_transpose)):
          if len(contain_transpose[i]) != length:
              contain_transpose[i].append("NaN" * (length - len(contain_transpose[i])))
  
      # create columns for the transposed column values
      for i in range(0, len(contain)):
          for j in range(0, size):
              contain[i][column + str(j)] = np.nan
  
      # assign the transposed values to the column
      for i in range(0, len(contain)):
          for j in range(0, length):
              contain[i][column + str(j)] = contain_transpose[i][j]
  
      # now always take the first values of the grouped group
      concat_list = []
  
      for i in range(0, len(contain)):
          concat_list.append(contain[i][:1])
  
      return pd.concat(concat_list) # concate the list
  
  # fill in column name and expected size of the column
  data_la = rows_to_col("la", 4)
  data_ln = rows_to_col("ln", 3)
  
  # merge the two data frames together
  cols_use = data_ln.columns.difference(data_la.columns)
  
  data_final = pd.merge(data_la, data_ln[cols_use], left_index=True, right_index=True, how="outer")
  data_final.drop(["la", "ln"], axis = 1)
  </code></pre>