[FIXED] pandas: change table structue

Issue

I want to change my table structure for plotting

Input:

	Year	Company	Number
0	2006	ABC	500
1	2007	ABC	700
2	2007	PQR	800
3	2006	PQR	600
4	2006	ABC	500

to output:

Year	ABC	PQR
2006	1000	600
2007	700	800

In addition to sum, i also want to take log of the sum. For eg in Year=2006, under column ABC, i want 3 (log1000). Similarly, for other columns as well.

I tried aggfunc=math.log(np.sum) in pivot_table but this is not working.

Solution

Use:

df.pivot_table(index='Year', columns='Company', values='Number',
               aggfunc=lambda x: np.log10(np.sum(x)))

output:

Company       ABC       PQR
Year                       
2006     3.000000  2.778151
2007     2.845098  2.903090

Answered By - mozway