Create Pivot table from a dataframe

Question

I want to convert the data given here from:

to this:

where the numbers are Sales against each company. How can I do this with Python?

Answer 1

pandas' groupby function is your friend. You can use it to group by month and company, and then rearrange to grouping by company (which appears as index) into columns:

df.groupby(['Month', 'Company'])['Sales'].sum().unstack()

After the groupby(['Month', 'Company'])['Sales'].sum() you have companies as indices, therefore the use of unstack() to change them in to columns.

Answer 2

You can use DataFrame.pivot

The pivot call will reshape the dataframe from rows to columns and fillna(0) will replace the empty cells with zero, finally reset_index will get rid of hierarchical column

df.pivot(index='Month', columns='Company', values='Sales').fillna(0).reset_index()

OUTPUT:

Company  Month  Apple    HP
0            1    8.0   0.0
1            2    8.0   0.0
2            3   14.0   0.0
3            4    3.0   0.0
4            5    4.0  10.0
5            6    3.0   9.0
6            7   14.0   9.0
7            8    0.0  10.0
8            9    0.0   3.0
9           10    0.0   4.0

PS: It is recommended to add your data as text while asking the question, we can not copy the contents from image.