Ordering data in python or excel

Question

I've got a sizable csv file with unorganized data. It is made up of music tags. In an effort to make analysis easier, I'm attempting to combine all of the comparable tags.

An example of what I have:

Band1, hiphop, pop, rap    
Band2, rock, rap, pop    
band3, hiphop, rap

The output I am looking for would be like this:

Band1, hiphop, pop, rap    
Band2, NaN,    pop, rap, rock    
Band3 hiphop,   NaN, rap

What is the best way to sort the data like this?

Answer 1

Basically, the data is converted from wide to long and then into a single hot encoded dataframe that you may use however you like.

import pandas as pd

df = pd.read_csv('./band_csv.csv',header=None)

new_df = pd.DataFrame(columns=['band','genre'])
for col in list(df.columns[1:]):
    temp_df = pd.DataFrame(columns=['band','genre'])
    temp_df.loc[:,'band'] = df.loc[:,df.columns[0]]
    temp_df.loc[:,'genre'] = df.loc[:,col]
    new_df = pd.concat([new_df,temp_df])


grouped_df = pd.get_dummies(new_df, columns=['genre']).groupby(['band'], as_index=False).sum()

Your grouped_df should look like

   band  genre_hiphop  genre_pop  genre_rap  genre_rock
0  Band1             1          1          1           0
1  Band2             0          1          1           1
2  band3             1          0          1           0