1

I have information from a csv file that is presented rowwise:

Color     Shape    Value
red       triangle    10
red       circle      11
blue      triangle    12
blue      circle      13

I need to convert this to a new dataFrame in a matrix form, where the columns are colors and the indexes are shapes

           red  blue
triangle    10    12
circle      11    13

I managed to do this by iterating in a loop

new_df = pd.DataFrame(columns=list_of_colors, index=list_of_shapes)

for color_i in list_of_colors:
  # this gives me the values of each color sorted* by Shape
  df[df['Color'] == color_i].sort_values('Shape')['Value']

  # so I can append this to the new dataframe
  ...
  • I really don't need the shapes to be sorted, but I need to guarantee that in each iteration the retrieved shapes list are in the same order, otherwise the resulting table will be wrong

This works, this I think I'm overdoing it. Is there a direct way of taking rowwise info and converting it to a table form?

Thanks

Improve this question

1 Answer 1

Reset to default
2

You can use pivot_table():

For the data: import pandas as pd

df = pd.DataFrame({'Color': ['red', 'red', 'blue', 'blue'],
                   'Shape': ['triangle', 'circle', 'triangle', 'circle'],
                   'Value': [10, 11, 12, 13]})


df.pivot_table(index = 'Color', columns = 'Shape', values = 'Value')

The result is:

Shape   circle  triangle
Color       
blue     13       12
red      11       10
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments