Ordering data in python or excel

0 votes

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?

Feb 11, 2023 in Others by narikkadan
• 63,720 points
310 views

1 answer to this question.

0 votes

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
answered Feb 11, 2023 by Kithuzzz
• 38,020 points

Related Questions In Others

0 votes
1 answer
0 votes
1 answer

Divide data and copy other cells in google sheets or excel

Try this : const Sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(`YourSheetName`) function dataHandler() ...READ MORE

answered Mar 20, 2023 in Others by narikkadan
• 63,720 points
351 views
0 votes
1 answer

How do I stop python from appending data to the same row in excel?

There is no indication in your code ...READ MORE

answered Mar 25, 2023 in Others by narikkadan
• 63,720 points
489 views
0 votes
2 answers
+1 vote
2 answers

how can i count the items in a list?

Syntax :            list. count(value) Code: colors = ['red', 'green', ...READ MORE

answered Jul 7, 2019 in Python by Neha
• 330 points

edited Jul 8, 2019 by Kalgi 4,361 views
0 votes
1 answer
0 votes
1 answer
0 votes
1 answer

How to read an Excel CSV file in Python?

The csv module or the pandas library ...READ MORE

answered Mar 19, 2023 in Others by Kithuzzz
• 38,020 points
794 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP