Ben Chuanlong Du's Blog

It is never too late to learn.

Pivot Table in pandas DataFrame

DataFrame.pivot vs pandas.pivot_table

  1. Both DataFrame.pivot and pandas.pivot_table can generate pivot tables. pandas.pivot_table aggregate values while DataFrame.pivot not.
In [3]:
import pandas as pd
import numpy as np

df = pd.DataFrame(
    {"id": ["a", "b", "c"], "x": [1, 3, 5], "y": [2, 4, 6], "z": [7, 8, 9]}
)
df
Out[3]:
id x y z
0 a 1 2 7
1 b 3 4 8
2 c 5 6 9
In [5]:
dfm = pd.melt(df, id_vars="id", value_vars=["x", "y", "z"])
dfm
Out[5]:
id variable value
0 a x 1
1 b x 3
2 c x 5
3 a y 2
4 b y 4
5 c y 6
6 a z 7
7 b z 8
8 c z 9

DataFrame.pivot

The values option is not flexible at this time. It accepts a column name or None but cannot accept a list of columns at this time. One way to achieve it is to specify None for values and then select columns you want manually.

In [6]:
dfp = dfm.pivot(index="id", columns="variable", values="value")
dfp
Out[6]:
variable x y z
id
a 1 2 7
b 3 4 8
c 5 6 9
In [36]:
dfp.index
Out[36]:
Index(['a', 'b', 'c'], dtype='object', name='id')
In [ ]:
 unstack pivot_table, ...
In [ ]:
jj = j.unstack()

pandas.pivot_table

In [7]:
pd.pivot_table(dfm, index="id", columns="variable", values="value")
Out[7]:
variable x y z
id
a 1 2 7
b 3 4 8
c 5 6 9
In [8]:
df = pd.DataFrame(
    data=[
        ["foo", "one", "small", 1],
        ["foo", "one", "large", 2],
        ["foo", "one", "large", 2],
        ["foo", "two", "small", 3],
        ["foo", "two", "small", 3],
        ["bar", "one", "large", 4],
        ["bar", "one", "small", 5],
        ["bar", "two", "small", 6],
        ["bar", "two", "large", 7],
    ],
    columns=["a", "b", "c", "d"],
)
df
Out[8]:
a b c d
0 foo one small 1
1 foo one large 2
2 foo one large 2
3 foo two small 3
4 foo two small 3
5 bar one large 4
6 bar one small 5
7 bar two small 6
8 bar two large 7
In [51]:
pd.pivot_table(df, index=["a", "b"], columns="c", values="d", aggfunc=np.sum)
Out[51]:
c large small
a b
bar one 4.0 5.0
two 7.0 6.0
foo one 4.0 1.0
two NaN 6.0
In [ ]:

Comments