How to pivot without aggregation in python or sql-CodePudding

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'))

CodePudding user response：

Since you you have a pivot 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