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

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

CodePudding user response：

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

CodePudding user response：

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

CodePudding user response：

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