Difficult Dataframe Reshape in Python Pandas
Question: If I have a dataframe that looks like this:

    
    
             DATE1    DATE2   DATE3   AMOUNT1   AMOUNT2   AMOUNT3
    
    1        1/1/15   5/22/14 7/12/13    5        6         3
    ..         ..       ..       ..      ..       ..        ..
    

and I want to get it in the form:

    
    
        DATE  AMOUNT
    1  1/1/15   5
    2  5/22/14  6
    3  7/12/13  3
    ..   ..     ..
    

What is the most efficient code to do this? From what I can tel melting or
grouping wont work because of the difference in column names (DATE1, DATE2,
etc). Is the best thing to subset the "1" columns, "2" columns, and "3"
columns into smaller dataframes, rename the columns, and concat? Or is there a
better way to do it that I'm missing?

Thanks.


Answer: You could use `pd.lreshape`:

    
    
    import pandas as pd
    
    df = pd.DataFrame([['1/1/15', '5/22/14', '7/12/13', 5, 6, 3]], 
                      columns=['DATE1', 'DATE2', 'DATE3', 'AMOUNT1', 'AMOUNT2', 'AMOUNT3'])
    
    result = pd.lreshape(df, {'AMOUNT': ['AMOUNT1', 'AMOUNT2', 'AMOUNT3'],
                              'DATE': ['DATE1', 'DATE2', 'DATE3']})
    print(result)
    

yields

    
    
          DATE  AMOUNT
    0   1/1/15       5
    1  5/22/14       6
    2  7/12/13       3
    

The second argument to `pd.lreshape` is a dict of key/value pairs. Each key is
the name of a desired column, and each value is a list of columns from `df`
which you wish to coalesce into one column.

See the docstring, `help(pd.lreshape)`, for a little more on `pd.lreshape`.

* * *

Alternatively, you could use `pd.melt` to coalesce all the columns into one
column, and use `str.extract` to separate the text-part from the numeric-part
of the column names. Then use `pivot` to obtain the desired result:

    
    
    result = pd.melt(df)
    result[['variable', 'num']] = result['variable'].str.extract('(\D+)(\d+)', expand=True)
    result = result.pivot(index='num', columns='variable', values='value')
    print(result)
    

yields

    
    
    variable AMOUNT     DATE
    num                     
    1             5   1/1/15
    2             6  5/22/14
    3             3  7/12/13
    

