how do I use pd.melt() across multiple columns?

Question

I'm currently having an issue while creating a dimension table named payment_types_Owned that lists the number Products that a customer has, plus their balances, and their limits on each payment. Currently, I have a table that looks like this:

    cust_id  Payment Type X owned  Payment Type Y owned  Payment Type Z owned  Credit Used_X  Limit_X  Credit Used_Y  Limit_Y  Credit Used_Z  Limit_Z
0  Person_A                     1                     3                     4            300      700            700      800            400      900
1  Person_B                     2                     1                     3            400      600            100      150            400      500
2  Person_C                     2                     4                     4            500      600            700      800            100      500

My desired output:

        cust_id        variable  value  Credit Used  Limit
0  Person_A_key  Payment Type X      1          300    700
1  Person_A_key  Payment Type Y      3          700    800
2  Person_A_key  Payment Type Z      4          400    900
3  Person_B_key  Payment Type X      2          400    600
4  Person_B_key  Payment Type Y      1          100    150
5  Person_B_key  Payment Type Z      3          400    500

Assuming that I already have 2 other Dimension tables that capture the following information:

1. Customer Dimension Table - Contains cust_id Primary Keys
2. Product Dimension Table - Contains the unique Product Primary Keys

Using pd.melt(), I get the below, but its only partly solving my problem:

(pd.melt(df, id_vars=['cust_id'], value_vars=['Payment Type X owned','Payment Type Y owned', 'Payment Type Z owned'])).sort_values(by=['cust_id'])

    cust_id        variable  value
0  Person_A  Payment Type X      1
3  Person_A  Payment Type Y      3
6  Person_A  Payment Type Z      4
1  Person_B  Payment Type X      2
4  Person_B  Payment Type Y      1
7  Person_B  Payment Type Z      3
2  Person_C  Payment Type X      2
5  Person_C  Payment Type Y      4
8  Person_C  Payment Type Z      4

Any suggestions?

Answer 1

If you can organize your columns as a multiindex with first level 'Payment Type X'... there is a relatively easy solution (at the end of this posting you find code that brings your dataframe in that form).

With a multiindex on the columns as described above, the following code produces your output:

result= None
for col_group in set(df.columns.get_level_values(0)):
    df_group= df[col_group].assign(variable=col_group).set_index('variable', append=True)
    if result is None:
        result= df_group
    else:
        result= pd.concat([result, df_group], axis='index')
result.sort_index(inplace=True)

After execution variable result contains a dataframe, which looks like:

                         owned  Credit Used  Limit
cust_id  variable                                 
Person_A Payment Type X      1          300    700
         Payment Type Y      3          700    800
         Payment Type Z      4          400    900
Person_B Payment Type X      2          400    600
         Payment Type Y      1          100    150
         Payment Type Z      3          400    500
Person_C Payment Type X      2          500    600
         Payment Type Y      4          700    800
         Payment Type Z      4          100    500

The following code creates the test data and reorganizes the columns as used above:

import pandas as pd
import io
raw=\
"""   cust_id  Payment Type X owned  Payment Type Y owned  Payment Type Z owned  Credit Used_X  Limit_X  Credit Used_Y  Limit_Y  Credit Used_Z  Limit_Z
0  Person_A                     1                     3                     4            300      700            700      800            400      900
1  Person_B                     2                     1                     3            400      600            100      150            400      500
2  Person_C                     2                     4                     4            500      600            700      800            100      500"""

df= pd.read_csv(io.StringIO(raw), sep='  +', engine='python')
df.set_index(['cust_id'], inplace=True)

new_cols= list()

for col in df.columns:
    if 'X' in col:
        lv1= 'Payment Type X'
    elif 'Y' in col:
        lv1= 'Payment Type Y'
    elif 'Z' in col:
        lv1= 'Payment Type Z'
    else:
        lv1= col
    if col[-2:-1] == '_':
        lv2= col[:-2]
    elif col.endswith(' owned'):
        lv2= 'owned'
    else:
        lv2= col
    new_cols.append((lv1, lv2))

df.columns= pd.MultiIndex.from_tuples(new_cols)

A more radical approach in just one step looks like this:

flat= df_orig.melt(id_vars=['cust_id'], var_name='column')
flat['variable']= ''
flat.loc[flat['column'].str.match('.*[_ ]X.*'), 'variable']= 'Payment Type X'
flat.loc[flat['column'].str.match('.*[_ ]Y.*'), 'variable']= 'Payment Type Y'
flat.loc[flat['column'].str.match('.*[_ ]Z.*'), 'variable']= 'Payment Type Z'

flat['column']= flat['column'].str.replace('[_ ][XYZ]', '').str.replace('Payment Type owned', 'Owned')

flat.set_index(['cust_id', 'variable', 'column'], inplace=True)
result= flat.unstack().droplevel(0, axis='columns')

It's more radical, because it decomposes the original dataframe completely in order to rebuild it. It is probably less efficient than the first approach.

Answer 2

Use wide_to_long, but first is necessary use Series.str.replace with first group Payment Type columns:

df.columns = df.columns.str.replace(' owned', '').str.replace('Payment Type ', 'Payment Type_')
print (df)
    cust_id  Payment Type_X  Payment Type_Y  Payment Type_Z  Credit Used_X  \
0  Person_A               1               3               4            300   
1  Person_B               2               1               3            400   
2  Person_C               2               4               4            500   

   Limit_X  Credit Used_Y  Limit_Y  Credit Used_Z  Limit_Z  
0      700            700      800            400      900  
1      600            100      150            400      500  
2      600            700      800            100      500  

df1 = pd.wide_to_long(df, stubnames=['Payment Type','Credit Used', 'Limit'], 
                      i='cust_id', 
                      j='variable', 
                      sep='_',
                      suffix='\w+').sort_index(level=0).reset_index()

Last add string to variable column and rename column by dict:

df1 = (df1.assign(variable='Payment Type ' + df1['variable'])
          .rename(columns={'Payment Type':'value'}))
print(df1)
    cust_id        variable  value  Credit Used  Limit
0  Person_A  Payment Type X      1          300    700
1  Person_A  Payment Type Y      3          700    800
2  Person_A  Payment Type Z      4          400    900
3  Person_B  Payment Type X      2          400    600
4  Person_B  Payment Type Y      1          100    150
5  Person_B  Payment Type Z      3          400    500
6  Person_C  Payment Type X      2          500    600
7  Person_C  Payment Type Y      4          700    800
8  Person_C  Payment Type Z      4          100    500