How can I convert an index column and add it back to the dataframe?

user_16042386 0
Viewed 79

My df looks like this:

Index1 Index2 cat art
id 1 1293874 Vintage
name 1 Sam old
id 2 2030039 Retired
name 2 Ben lacklust

My goal is try to pivot index1 and add them as columns. Currently, my data is not structured and correctly formatted. The category column has a bucket of information of both name and id, but it is not a category. Not only do I want to group by the index, but I also want to add the columns to the dataset without removing any of the original columns.

My desired output looks like this

Index2 cat name id_Art name_Art
1 1293874 Sam Vintage old
2 2030039 Ben Retired lacklust
pandas python
3 Answers

You can use melt:

out = (df.melt(['Index1', 'Index2'], var_name='Category', value_name='Value')
         .assign(Category=lambda x: x['Index1'] + '_' + x['Category'])
         .pivot('Index2', 'Category', 'Value')
         .rename_axis(columns=None).reset_index())
print(out)

# Output
   Index2   id_art   id_cat  name_art name_cat
0       1  Vintage  1293874       old      Sam
1       2  Retired  2030039  lacklust      Ben
user_152399510

You can use unstack on level=0 (in your case Index1):

df.set_index(['Index1', 'Index2'], inplace=True) #make sure your Index1 and Index2 are set are index
print(df.unstack(level=0))

Output:

            cat           art          
Index1       id name       id      name
Index2                                 
1       1293874  Sam  Vintage       old
2       2030039  Ben  Retired  lacklust
user_135255120

Another way to get to the same result as Tranbi's answer, since you mentioned pivoting:

df.pivot(index='Index2', columns='Index1')

gives

            cat           art          
Index1       id name       id      name
Index2                                 
1       1293874  Sam  Vintage       old
2       2030039  Ben  Retired  lacklust
user_97699530
Related