I have a pandas data frame like this:
ts | thing_0 | qty_0 | thing_1 | qty_1 | thing_2 | qty_2
--------------------------------------------------------
1 | dog | 5 | cat | 3 | mouse | 1
2 | house | 6 | dog | 4 | cat | 2
...
I want to transform this in a way that the things become columns and the qtys become the cell values. Like this:
ts | dog | cat | mouse | house
------------------------------
1 | 5 | 3 | 1 | 0
2 | 4 | 2 | 0 | 6
...
Currently, I'm doing this transformation manually by iterating over the df.values array manually, but this is very slow. Is there a faster way to implement this with pandas means?
I have seen df.pivot, but couldn't find a way to describe the relationship between thing_0 and qty_0.