I have a large list of dataframe with duplicate observations for the same index, analagous to the 'df' below:

import pandas as pd
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'])

desired_boxes = {'Color': ['Red','Orange','Yellow','Green'],'Shape1': ['Square','Square','Square','Rectangle',],
    'Length1': [15,25,25,15], 'Width1': [8,5,5,4],'Height1': [30,35,35,40],'Shape2': ['Rectangle','Rectangle','Square','Rectangle'],
    'Length2': [15,15,20,25],'Width2': [8,8,5,4], 'Height2': [30,35,40,40], 'Shape3':['Circle','Circle','Circle','Circle'],
    'Length3': [26,26,23,29], 'Width3': [2,2,3,5,],'Height3': [36,35,39,46]}


df df

desired_df desired_df

I am trying to sort through the dataframe based on a column ('Color' here) and create new columns of the data associated with a duplicate color. See desired_df for clarity.

Column names don't necessarily need to change, but I think they might have to in order to preserve different data from a column of a duplicate name. Here is a column name forloop from another post:

cols = []
for column in df.columns:
    if column == 'Shape':
df.columns = cols

And here is a loop for trying to merge duplicate dataframes into the original:

for value in df:
    df=df.merge(separate_i, on='Color', how='inner')

I am fairly new to coding so I apologize for the poor attempts at this, I can't figure it out. Any help would be appreaciated! Thanks.

  • $\begingroup$ Changing your data structure to this form seems like a bad idea. Is there any specific reason you want to move it to this format? $\endgroup$ Sep 9, 2022 at 21:00
  • $\begingroup$ It is for environmental samples which will be mapped later based on a single lat/long value associated with a specific sample, but the same sample has several different metal measurements associated with it, but prints out vertically like in the example df. The ultimate goal is to get all metal measurements for one sample within one labelled row, so in this example all the different kinds of red shapes into one row with the attached details... does that answer your question? $\endgroup$ Sep 9, 2022 at 21:54
  • $\begingroup$ Yeah I think so. Does this mean that there will be a varying amount of duplicates for each sample, with lots of nan values? Have you considered using booleans for each specific shape, indicating whether or not they are present? $\endgroup$ Sep 10, 2022 at 5:51
  • $\begingroup$ df.groupby('Color').apply(lambda df_: df_[['Shape','Length','Width','Height']].values.flatten()).apply(pd.Series).reset_index() $\endgroup$ Sep 10, 2022 at 6:23
  • 1
    $\begingroup$ For columns you can do something like: df_desired.columns = ['Shape'] + ['Shape','Length','Width','Height']*df.a.value_counts().max() $\endgroup$ Sep 10, 2022 at 6:30


Your Answer

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

Browse other questions tagged or ask your own question.