Melt multiple boolean columns

user_9761749 0
Viewed 118

This question is an extension of the one already asked here: Melt multiple boolean columns in a single column in pandas

If the data looked like:

| System | Windows | Linux | Mac|
| -------- | -------------- |
| Desktop    | True | True | False |
| Laptop | True   | True | False
| Mobile | True   | True | False
| Tablet | False | True | False

where 'System' is dtype=string and other columns are dtype=boolean. How can I make the final table look like this:

| OS | System |
| --- ----- | -------------- |
| Windows| Desktop Laptop Mobile  |
| Linux | Desktop Laptop Mobile Tablet |
| Mac | |

where the values in the System column are separated by space.

pandas dataframe python-3.x
2 Answers

One idea is first convert System column to index and transpose, then use DataFrame.dot for matrix multiplication by columns names with space separator:

df1 = df.set_index('System').T
df1 = df1.dot(df1.columns + ' ').str.rstrip().rename_axis('OS').reset_index(name='System')
print (df1)
        OS                        System
0  Windows         Desktop Laptop Mobile
1    Linux  Desktop Laptop Mobile Tablet
2      Mac

Solution with DataFrame.melt is also possible, only after filtering only Trues and aggregate join is necessary also add removed categories by unique values of columns names (without first):

df1 = (df.melt('System', var_name='OS')
         .query("value == True")
         .groupby('OS')['System']
         .agg(' '.join)
         .reindex(df.columns[1:].unique(), fill_value='')
         .rename_axis('OS')
         .reset_index(name='System'))

print (df1)
        OS                        System
0  Windows         Desktop Laptop Mobile
1    Linux  Desktop Laptop Mobile Tablet
2      Mac
user_29010020

Here is another solution:

df = df.melt(id_vars=['System'],var_name='OS')
df.groupby('OS').agg(lambda x: ' '.join([e for i,e in enumerate(x) if df.loc[x.index[i],'value']]))
user_166726400
Related