pandas dataframe: convert 2 columns (value, value) into 2 columns (value, type)

Question

Let's say I have the following dataframe "A"

         utilization  utilization_billable
service                                   
1               10.0                   5.0
2               30.0                  20.0
3               40.0                  30.0
4               40.0                  32.0

I need to convert it into the following dataframe "B"

         utilization      type
service                       
1               10.0     total
2               30.0     total
3               40.0     total
4               40.0     total
1                5.0  billable
2               20.0  billable
3               30.0  billable
4               32.0  billable

so the values from the first are categorized into type column with values of total or billable.

data = {
    'utilization': [10.0, 30.0, 40.0, 40.0],
    'utilization_billable': [5.0, 20.0, 30.0, 32.0],
    'service': [1, 2, 3, 4]
}
df = pd.DataFrame.from_dict(data).set_index('service')
print(df)

data = {
    'utilization': [10.0, 30.0, 40.0, 40.0, 5.0, 20.0, 30.0, 32.0],
    'service': [1, 2, 3, 4, 1, 2, 3, 4],
    'type': [
        'total',
        'total',
        'total',
        'total',
        'billable',
        'billable',
        'billable',
        'billable',
    ]
}
df = pd.DataFrame.from_dict(data).set_index('service')
print(df)

Is there a way to transform the data frame and perform such categorization?

Answer 1

You could use pd.melt:

import pandas as pd
data = {
    'utilization': [10.0, 30.0, 40.0, 40.0],
    'utilization_billable': [5.0, 20.0, 30.0, 32.0],
    'service': [1, 2, 3, 4]}

df = pd.DataFrame(data)
result =  pd.melt(df, var_name='type', value_name='utilization', id_vars='service')
print(result)

yields

   service                  type  utilization
0        1           utilization         10.0
1        2           utilization         30.0
2        3           utilization         40.0
3        4           utilization         40.0
4        1  utilization_billable          5.0
5        2  utilization_billable         20.0
6        3  utilization_billable         30.0
7        4  utilization_billable         32.0

Then result.set_index('service') would make service the index, but I would recommend avoiding that since service values are not unique.