0
$\begingroup$

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:

df

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:

wanted dataframe

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
$\endgroup$
2
  • $\begingroup$ One reason this isn't working is because you are trying to assign a single value to df.columns. If you want to rename a single column use df.rename(columns={colname: newname}). To debug this further, try adding some print statements inside your for loop to show the values of key variables. This should give you an idea of what's going wrong. $\endgroup$
    – Lynn
    Sep 22, 2022 at 13:11
  • $\begingroup$ I see, I wasn't really clear on how to concat. a value to a list and apply that for the columns, I kept having an issue where strings and intergers couldn't be concatenated, lists too. Inserting print statements is a good idea, thanks! $\endgroup$ Sep 22, 2022 at 17:04

1 Answer 1

1
$\begingroup$

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
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.