Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How do you use pandas.DataFrame columns as index, columns, and values?

I can't seem to figure out how to ask this question in a searchable way, but I feel like this is a simple question.

Given a pandas Dataframe object, I would like to use one column as the index, one column as the columns, and a third column as the values.

For example:

   a   b   c
0  1  dog  2 
1  1  cat  1
2  1  rat  6
3  2  cat  2
4  3  dog  1
5  3  cat  4

I would like to user column 'a' as my index values, column 'b' as my columns, and column 'c' as the values for each row/column and fill with 0 for missing values (if possible). For example...

   dog   cat   rat
1   2     1     6
2   0     2     0
3   1     4     0

This would be an 'a' by 'b' matrix with 'c' as the filling values

like image 330
Jake Avatar asked Sep 28 '22 16:09

Jake


2 Answers

It's (almost) exactly as you phrase it:

df.pivot_table(index="a", columns="b", values="c", fill_value=0)

gives

b  cat  dog  rat
a               
1    1    2    6
2    2    0    0
3    4    1    0

HTH

like image 109
jrjc Avatar answered Oct 03 '22 07:10

jrjc


http://pandas.pydata.org/pandas-docs/dev/reshaping.html

Starting with the example dataframe you give,

df.pivot(index='a', columns='b', values='c')

will produce pretty much exactly the output you want.

FWIW, df.melt() is the opposite transformation.

like image 21
Alex I Avatar answered Oct 03 '22 08:10

Alex I