Melting pandas data frame with multiple variable names and multiple value names

user_7246047 0
Viewed 3934

How can I melt a pandas data frame using multiple variable names and values? I have the following data frame that changes its shape in a for loop. In one of the for loop iterations, it looks like this:

ID  Cat    Class_A   Class_B     Prob_A     Prob_B
1   Veg      1        2          0.9         0.1
2   Veg      1        2          0.8         0.2
3   Meat     1        2          0.6         0.4
4   Meat     1        2          0.3         0.7
5   Veg      1        2          0.2         0.8

I need to melt it in such a way that it looks like this:

ID  Cat    Class     Prob    
1   Veg      1       0.9       
1   Veg      2       0.1
2   Veg      1       0.8        
2   Veg      2       0.2
3   Meat     1       0.6         
3   Meat     2       0.4
4   Meat     1       0.3         
4   Meat     2       0.7
5   Veg      1       0.2         
5   Veg      2       0.8

During the for loop the data frame will contain different number of classes with their probabilities. That is why I am looking for a general approach that is applicable in all my for loop iterations. I saw this question and this but they were not helpful!

melt pandas python
5 Answers

You could also use pd.melt.

# Make DataFrame
df = pd.DataFrame({'ID' :  [i for i in range(1,6)],
                   'Cat' : ['Veg']*2 + ['Meat']*2 + ['Veg'],
                   'Class_A' : [1]*5,
                   'Class_B' : [2]*5,
                   'Prob_A' : [0.9, 0.8, 0.6, 0.3, 0.2],
                   'Prob_B' : [0.1, 0.2, 0.4, 0.7, 0.8]})

# Make class dataframe and prob dataframe
df_class = df.loc[:, ['ID', 'Cat', 'Class_A', 'Class_B']]
df_prob = df.loc[:, ['ID', 'Cat', 'Prob_A', 'Prob_B']]

# Melt class dataframe and prob dataframe
df_class = df_class.melt(id_vars = ['ID',
                                    'Cat'],
                         value_vars = ['Class_A',
                                       'Class_B'],
                         value_name = 'Class')
df_prob = df_prob.melt(id_vars = ['ID',
                                  'Cat'],
                       value_vars = ['Prob_A',
                                     'Prob_B'],
                       value_name = 'Prob')

# Clean variable column so only 'A','B' is left in both dataframes
df_class.loc[:, 'variable'] = df_class.loc[:, 'variable'].str.partition('_')[2]
df_prob.loc[:, 'variable'] = df_prob.loc[:, 'variable'].str.partition('_')[2]

# Merge class dataframe with prob dataframe on 'ID', 'Cat', and 'variable';
# drop 'variable'; sort values by 'ID', 'Cat'
final = df_class.merge(df_prob,
                       how = 'inner',
                       on = ['ID',
                             'Cat',
                             'variable']).drop('variable', axis = 1).sort_values(by = ['ID',
                                                                                       'Cat'])
user_65095190

One option is pivot_longer from pyjanitor, which abstracts the process, and is efficient:

# pip install janitor
import janitor

df.pivot_longer(
    index = ['ID', 'Cat'], 
    names_to = '.value', 
    names_pattern = '([a-zA-Z]+)_*')

   ID   Cat  Class  Prob
0   1   Veg      1   0.9
1   2   Veg      1   0.8
2   3  Meat      1   0.6
3   4  Meat      1   0.3
4   5   Veg      1   0.2
5   1   Veg      2   0.1
6   2   Veg      2   0.2
7   3  Meat      2   0.4
8   4  Meat      2   0.7
9   5   Veg      2   0.8

The idea for this particular reshape is that whatever group in the regular expression is paired with the .value stays as the column header.

user_71757130
Related