Pandas: Convert DataFrame column values ​​to new indexes and Dataframe columns

Question

I have a dataframe that looks like this:

abc 0 1 10 1 2 10 2 2 20 3 3 30 4 1 40 4 3 10 

The data metric is higher by default (0,1,2,3,4 ...). I would like to convert it to a dataframe that looks like this:

  1 2 3 0 10 0 0 1 0 10 0 2 0 20 0 3 0 0 30 4 40 0 10 

If column “a” in the first data frame becomes an index in the second data frame, values ​​“b” become column names and values ​​c are copied, with 0 or NaN filling in the missing values. The original data set is large and will result in a very sparse second data frame. Then I intend to add this framework to much more, which is simple.

Can anyone advise a better way to achieve this?

Answer 1

You can use the pivot method for this.

See the docs: http://pandas.pydata.org/pandas-docs/stable/reshaping.html#reshaping-by-pivoting-dataframe-objects

Example:

 In [1]: import pandas as pd In [2]: df = pd.DataFrame({'a':[0,1,2,3,4,4], 'b':[1,2,2,3,1,3], 'c':[10,10,20,3 0,40,10]}) In [3]: df Out[3]: abc 0 0 1 10 1 1 2 10 2 2 2 20 3 3 3 30 4 4 1 40 5 4 3 10 In [4]: df.pivot(index='a', columns='b', values='c') Out[4]: b 1 2 3 a 0 10 NaN NaN 1 NaN 10 NaN 2 NaN 20 NaN 3 NaN NaN 30 4 40 NaN 10 

If you need zero instead of NaN, as in your example, you can use fillna :

 In [5]: df.pivot(index='a', columns='b', values='c').fillna(0) Out[5]: b 1 2 3 a 0 10 0 0 1 0 10 0 2 0 20 0 3 0 0 30 4 40 0 10