Thu. Oct 6th, 2022

Hello Android

All android in one place

Pandas pivot_table() – DataFrame Data Analysis

3 min read

What is a Pivot Table?

A pivot table is a table of statistics that summarizes the data of a more extensive table. The summary of data is reached through various aggregate functions – sum, average, min, max, etc.

A pivot table is a data processing technique to derive useful information from a table.

Pandas pivot_table() function

Pandas pivot_table() function is used to create pivot table from a DataFrame object. We can generate useful information from the DataFrame rows and columns. The pivot_table() function syntax is:

def pivot_table(
  • data: the DataFrame instance from which pivot table is created.
  • values: column to aggregate.
  • index: the column to group by on the pivot table index.
  • columns: the column to group by on the pivot table column.
  • aggfunc: the aggregate function to run on the data, default is numpy.mean
  • fill_value: value to replace null or missing value in the pivot table.
  • margins: add all rows/columns. It’s useful in generating grand total of the records.
  • dropna: don’t include columns whose entries are all NaN.
  • margins_name: Name of the row / column that will contain the totals when margins is True.
  • observed: This only applies if any of the groupers are Categoricals. If True: only show observed values for categorical groupers. If False: show all values for categorical groupers.

Pandas Pivot Table Examples

It’s better to use real-life data to understand the actual benefit of pivot tables. I have downloaded a sample CSV file from this link. Here is the direct download link for the CSV file.

The CSV file is a listing of 1,460 company funding records reported by TechCrunch. The below image shows the sample data from the file.

We are interested in the columns – ‘company’, ‘city’, ‘state’, ‘raisedAmt’, and ’round’. Let’s create some pivot tables to generate useful statistics from this data.

1. Average Funding by State

import pandas as pd
import numpy as np

df = pd.read_csv('TechCrunchcontinentalUSA.csv', usecols=['company', 'city', 'state', 'raisedAmt', 'round'])

print('DataFrame Records:n', df.head(6))

# average funding by State
df1 = pd.pivot_table(df, values='raisedAmt', columns='state')

print('nAverage Funding by State:n', df1)


DataFrame Records:
         company        city state  raisedAmt round
0      LifeLock       Tempe    AZ    6850000     b
1      LifeLock       Tempe    AZ    6000000     a
2      LifeLock       Tempe    AZ   25000000     c
3   MyCityFaces  Scottsdale    AZ      50000  seed
4      Flypaper     Phoenix    AZ    3000000     a
5  Infusionsoft     Gilbert    AZ    9000000     a

Average Funding by State:
 state             AZ            CA  ...            VA            WA
raisedAmt  5613750.0  1.072324e+07  ...  1.158261e+07  8.140103e+06

[1 rows x 33 columns]

We can also call pivot_table() function directly on the DataFrame object. The above pivot table can be generated using the below code snippet too.

df1 = df.pivot_table(values='raisedAmt', columns='state')

2. Total Funding by State

The default aggregate function is numpy.mean. We can specify the aggregate function as numpy.sum to generate the total funding by state.

df1 = pd.pivot_table(df, values='raisedAmt', columns='state', aggfunc=np.sum)

print('nTotal Funding by State:n', df1)


Total Funding by State:
 state            AZ          CA         CO  ...         UT         VA         WA
raisedAmt  50523750  9361385000  126470000  ...  153080000  266400000  789590000

[1 rows x 33 columns]

3. Total Funding by Company

df1 = pd.pivot_table(df, values='raisedAmt', columns='company', aggfunc=np.sum)

print('nTotal Funding by Company:n', df1)


Total Funding by Company:
 company    23andMe     3Jam  4HomeMedia  ...    vbs tv       x+1    xkoto
raisedAmt  9000000  4000000     2850000  ...  10000000  16000000  7500000

[1 rows x 909 columns]

4. Average Funding by Round grouped by State

The trick is to generate a pivot table with ’round’ as the index column.

df1 = pd.pivot_table(df, values='raisedAmt', columns='state', index='round')
print('nAverage Funding by round in State:n', df1)


Average Funding by round in State:
 state                   AZ            CA  ...          VA            WA
round                                     ...                          
a             6.000000e+06  7.158314e+06  ...   9910000.0  6.570476e+06
angel         2.337500e+05  1.006784e+06  ...         NaN  8.935714e+05
b             6.850000e+06  1.238483e+07  ...   9850000.0  1.187826e+07
c             2.500000e+07  2.369708e+07  ...  19500000.0  1.592222e+07
d                      NaN  3.012188e+07  ...  20000000.0  8.500000e+06
debt_round             NaN  1.660833e+07  ...         NaN           NaN
e                      NaN  3.132500e+07  ...         NaN  2.200000e+07
seed          1.466667e+05  8.778214e+05  ...    350000.0  7.800000e+05
unattributed           NaN  1.933000e+07  ...         NaN  2.050000e+07

[9 rows x 33 columns]

5. Replacing Null Values with a default value

df1 = pd.pivot_table(df, values='raisedAmt', columns='state', index='round', aggfunc=np.sum, fill_value=0)
print('nTotal Funding by round in State:n', df1)


Total Funding by round in State:
 state               AZ          CA        CO  ...        UT        VA         WA
round                                         ...                               
a             18000000  2526885000  25650000  ...  31800000  99100000  275960000
angel           233750    74502000   3950000  ...         0         0   12510000
b              6850000  2898050000  66900000  ...  67200000  68950000  273200000
c             25000000  2109040000  28850000  ...  54000000  78000000  143300000
d                    0   963900000         0  ...         0  20000000   17000000
debt_round           0   199300000    500000  ...         0         0          0
e                    0   250600000         0  ...         0         0   44000000
seed            440000    49158000    620000  ...     80000    350000    3120000
unattributed         0   289950000         0  ...         0         0   20500000

[9 rows x 33 columns]


Leave a Reply

Your email address will not be published. Required fields are marked *

Hello android © All rights reserved. | Newsphere by AF themes.