0

Situation

I have a dataframe attributes that holds some attribute information about cars in 3 series:

attributes = {'brand': ['Honda Civic','Honda Civic','Honda Civic','Toyota Corolla','Toyota Corolla','Audi A4'],
          'attributeName': ['wheels','doors','fuelType','wheels','color','wheels'],
          'attributeValue': ['4','2','hybrid','4','red','4']
        }

Expected result

result = {'brand':   ['Honda Civic','Toyota Corolla','Audi A4'],
          'wheels':  ['4','4','4'],
          'doors':   ['2','',''],
          'fuelType':['hybrid','',''],
          'color':   ['','red','']
         }

How can I realize this?

Transform the values from attributeName into series to represent its attributeValue for each brand/car in one row.

With get_dummies I get this transformation, but only with true/false values not with the original values.

HedgeHog
  • 22,146
  • 4
  • 14
  • 36

1 Answers1

1

This is a simple pivot:

attributes.pivot(index='brand',
                 columns='attributeName',
                 values='attributeValue').fillna('')

or, shorter as your columns are in right order:

attributes.pivot(*attributes).fillna('')

To format it exactly as your provided output (except column order, please give details on that), you can use:

(attributes.pivot(index='brand', columns='attributeName', values='attributeValue')
           .fillna('').rename_axis(None, axis=1)
           .reset_index()
)

output:

            brand color doors fuelType wheels
0         Audi A4                           4
1     Honda Civic           2   hybrid      4
2  Toyota Corolla   red                     4
mozway
  • 194,879
  • 13
  • 39
  • 75
  • Comunity wiki for dupes? – jezrael Sep 03 '21 at 07:35
  • Why answering for dupes? – jezrael Sep 03 '21 at 09:01
  • @jezrael I haven't noticed your comments yet, but I found the answer and especially the `attributes.pivot(*attributes).fillna('')` part very helpful, I didn't know any better at the time either. I appreciate your super precise answers and not only yours. Sometimes you just don't realise that it's a duplicate, a question that's been asked a thousand times, because you're maybe fresh into a subject. so i still think it's good if time allows and you feel like leaving an answer. It can't hurt and sometimes you find real pearls. thanks to you and mozway – HedgeHog Jan 23 '23 at 07:07
  • 1
    @HedgeHog - No problem, sometimes happens. – jezrael Jan 23 '23 at 07:15