How to pivot without aggregation in python or sql

I want to pivot a table such that each row in the type column is now its own row. Each metric is then a row and the values are the intersection of the metric and type. There are a variable number of types and metrics.Example. I do not want to alter the values in any way with aggregations. Any help with python or sql would be greatly appreciated!

I tried the pivoting function in SQL however I do not want to aggregate any values

Python example:
d = {‘Type’: [‘a’,’a’,’a’,’a’,’a’,’b’,’b’,’b’,’b’,’b’],
‘Metric’: [‘q rate’,’w rate’,’e rate’,’r rate’, ‘t rate’,’q rate’,’w rate’,’e rate’,’r rate’, ‘t rate’],
‘Value’:[1,2,3,4,5,9,8,7,6,5]}
df = pd.DataFrame(data=d)
df

SELECT * FROM
(
SELECT Type, Metric, Value
FROM table
)
PIVOT(MAX(Value) FOR Type in (‘List_of_types’))

>Solution :

Since you you have a tag, here is a proposition with pandas.DataFrame.pivot :

out = (
        df
          .pivot(index="Metric", columns="Type")
          .reset_index()
          .droplevel(0, axis=1)
          .rename_axis(None, axis=1)
       )

Output :
​

print(out)

           a  b
0  e rate  3  7
1  q rate  1  9
2  r rate  4  6
3  t rate  5  5
4  w rate  2  8

Leave a Reply