Set column names to column value in pandas forloop, add prefix to column names

Question

I have a dataframe with repeat column names, and I am trying to add a prefix label to column names with an appropriate identifier that comes from a column within the dataframe. I have this data frame:

and I would like to add the first '_length' value in each duplicate set of columns as a prefix to that set. So the end result would look like this:

I also am trying to make this code work across a different number of duplicate sets of columns so there is a bit of chunkiness, but the number of columns within a duplicate set will always stay the same. So far, I have this:

from multiprocessing.resource_sharer import stop
number_of_repeats = len(df.loc[0,"_Length"])-1
start = 0
a = 4

for column in df:
        if start >= number_of_repeats:
                stop
        else:
                b = df.iloc[0,(2+(a*start))]
                df.columns =  str(b) + column
                start=start+1
df
        

The code runs but doesn't do anything to the dataframe. Additionally, I do not know if it is doing the intended task.

Any help would be appreciated. Below is code to create the dataframes:

import pandas as pd
import numpy as np
boxes = {'Color': ['Red','Orange','Yellow','Green','Red','Orange','Yellow','Green','Red','Orange','Yellow','Green'],
         'Shape': ['Square','Square','Square','Rectangle','Rectangle','Rectangle','Square','Rectangle','Circle','Circle','Circle','Circle'],
        'Length': [15,25,25,15,15,15,20,25,26,26,23,29],
         'Width': [8,5,5,4,8,8,5,4,2,2,3,5,],
        'Height': [30,35,35,40,30,35,40,40,36,35,39,46]
        }

df = pd.DataFrame(boxes, columns = ['Color','Shape','Length','Width','Height'])


#transpose dataframe#
df=df.groupby(['Color']).apply(lambda x: x[['Shape','Length','Width','Height']].values.flatten()).apply(pd.Series).rename_axis(mapper='Color',index=list).reset_index()

#rename columns#
s = '_Length'
list = ['_Shape',(s),'_Width','_Height']
a=len(df.axes[1])-1
b=a/4
b
list4 = list*int(b)
c = ['Colors']
names = c+list4
df=df.set_axis(names, axis=1)

df

#create desired df#
df1=df.copy()
names_list = ['Color', '15_Shape', '15_Length', '15_Width', '15_Height', '25_Shape', '25_Length', '25_Width', '25_Height', '29_Shape', '29_Length', '29_Width', '29_Height']
df1.columns = names_list

Answer 1

I got this to work, but I had to back up a few steps in order to get a code working. Things are still pretty clunky, for example I am not using the variable 'set' within the forloop. Recommendations to get something more elegant working would be appreciated, thanks!

from multiprocessing.resource_sharer import stop
import pandas as pd
import numpy as np

'''Create df'''
boxes = {'Color': ['Red','Orange','Yellow','Green','Red','Orange','Yellow','Green','Red','Orange','Yellow','Green'],
         'Shape': ['Square','Square','Square','Rectangle','Rectangle','Rectangle','Square','Rectangle','Circle','Circle','Circle','Circle'],
        'Length': [15,25,25,15,15,15,20,25,26,26,23,29],
         'Width': [8,5,5,4,8,8,5,4,2,2,3,5,],
        'Height': [30,35,35,40,30,35,40,40,36,35,39,46]}

df = pd.DataFrame(boxes, columns = ['Color','Shape','Length','Width','Height'])
df

'''Create new dataframe, populate it with data w/ columns relabeled'''
renamed_data = pd.DataFrame(columns = ['Color'])
a = df.iloc[0,0]
repeats = df['Color'].value_counts()[a]
i=1
for set in df:
    if i <= repeats:
        d = df.duplicated(subset=['Color'], keep = 'first')
        time = (df.iloc[0,2])
        df1 = df[d]
        df = df[~d]
        df = df.set_index('Color')
        df = df.add_prefix('_')
        df = df.add_prefix(time)
        df.reset_index(inplace=True)
        renamed_data = renamed_data.merge(df, on = 'Color', how='outer')
        df = df1
        i=i+1
    else:
        stop
renamed_data