Title: Count Values In Pandas Dataframe
Slug: pandas_dataframe_count_values
Summary: Count Values In Pandas Dataframe
Date: 2016-05-01 12:00
Category: Python
Tags: Data Wrangling
Authors: Chris Albon

Import the pandas module



In [1]:

    
import pandas as pd

Create all the columns of the dataframe as series



In [2]:

    
year = pd.Series([1875, 1876, 1877, 1878, 1879, 1880, 1881, 1882, 1883, 1884, 
                  1885, 1886, 1887, 1888, 1889, 1890, 1891, 1892, 1893, 1894])
guardCorps = pd.Series([0,2,2,1,0,0,1,1,0,3,0,2,1,0,0,1,0,1,0,1])
corps1 = pd.Series([0,0,0,2,0,3,0,2,0,0,0,1,1,1,0,2,0,3,1,0])
corps2 = pd.Series([0,0,0,2,0,2,0,0,1,1,0,0,2,1,1,0,0,2,0,0])
corps3 = pd.Series([0,0,0,1,1,1,2,0,2,0,0,0,1,0,1,2,1,0,0,0])
corps4 = pd.Series([0,1,0,1,1,1,1,0,0,0,0,1,0,0,0,0,1,1,0,0])
corps5 = pd.Series([0,0,0,0,2,1,0,0,1,0,0,1,0,1,1,1,1,1,1,0])
corps6 = pd.Series([0,0,1,0,2,0,0,1,2,0,1,1,3,1,1,1,0,3,0,0])
corps7 = pd.Series([1,0,1,0,0,0,1,0,1,1,0,0,2,0,0,2,1,0,2,0])
corps8 = pd.Series([1,0,0,0,1,0,0,1,0,0,0,0,1,0,0,0,1,1,0,1])
corps9 = pd.Series([0,0,0,0,0,2,1,1,1,0,2,1,1,0,1,2,0,1,0,0])
corps10 = pd.Series([0,0,1,1,0,1,0,2,0,2,0,0,0,0,2,1,3,0,1,1])
corps11 = pd.Series([0,0,0,0,2,4,0,1,3,0,1,1,1,1,2,1,3,1,3,1])
corps14 = pd.Series([ 1,1,2,1,1,3,0,4,0,1,0,3,2,1,0,2,1,1,0,0])
corps15 = pd.Series([0,1,0,0,0,0,0,1,0,1,1,0,0,0,2,2,0,0,0,0])

Create a dictionary variable that assigns variable names



In [3]:

    
variables = dict(guardCorps = guardCorps, corps1 = corps1, 
                 corps2 = corps2, corps3 = corps3, corps4 = corps4, 
                 corps5 = corps5, corps6 = corps6, corps7 = corps7, 
                 corps8 = corps8, corps9 = corps9, corps10 = corps10, 
                 corps11 = corps11 , corps14 = corps14, corps15 = corps15)

Create a dataframe and set the order of the columns using the columns attribute



In [4]:

    
horsekick = pd.DataFrame(variables, columns = ['guardCorps', 
                                                    'corps1', 'corps2', 
                                                    'corps3', 'corps4', 
                                                    'corps5', 'corps6', 
                                                    'corps7', 'corps8', 
                                                    'corps9', 'corps10', 
                                                    'corps11', 'corps14', 
                                                    'corps15'])

Set the dataframe's index to be year



In [5]:

    
horsekick.index = [1875, 1876, 1877, 1878, 1879, 1880, 1881, 1882, 1883, 1884, 
                  1885, 1886, 1887, 1888, 1889, 1890, 1891, 1892, 1893, 1894]

View the horsekick dataframe



In [6]:

    
horsekick

Count the number of times each number of deaths occurs in each regiment



In [7]:

    
result = horsekick.apply(pd.value_counts).fillna(0); result

Count the number of times each monthly death total appears in guardCorps



In [8]:

    
pd.value_counts(horsekick['guardCorps'].values, sort=False)









    Out[8]:





0    9
1    7
2    3
3    1
dtype: int64

List all the unique values in guardCorps



In [9]:

    
horsekick['guardCorps'].unique()









    Out[9]:





array([0, 2, 1, 3])

	guardCorps	corps1	corps2	corps3	corps4	corps5	corps6	corps7	corps8	corps9	corps10	corps11	corps14	corps15
0	9.0	11.0	12.0	11.0	12.0	10.0	9.0	11.0	13.0	10.0	10.0	6	6	14.0
1	7.0	4.0	4.0	6.0	8.0	9.0	7.0	6.0	7.0	7.0	6.0	8	8	4.0
2	3.0	3.0	4.0	3.0	0.0	1.0	2.0	3.0	0.0	3.0	3.0	2	3	2.0
3	1.0	2.0	0.0	0.0	0.0	0.0	2.0	0.0	0.0	0.0	1.0	3	2	0.0
4	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	1	1	0.0

	guardCorps	corps1	corps2	corps3	corps4	corps5	corps6	corps7	corps8	corps9	corps10	corps11	corps14	corps15
1875	0	0	0	0	0	0	0	1	1	0	0	0	1	0
1876	2	0	0	0	1	0	0	0	0	0	0	0	1	1
1877	2	0	0	0	0	0	1	1	0	0	1	0	2	0
1878	1	2	2	1	1	0	0	0	0	0	1	0	1	0
1879	0	0	0	1	1	2	2	0	1	0	0	2	1	0
1880	0	3	2	1	1	1	0	0	0	2	1	4	3	0
1881	1	0	0	2	1	0	0	1	0	1	0	0	0	0
1882	1	2	0	0	0	0	1	0	1	1	2	1	4	1
1883	0	0	1	2	0	1	2	1	0	1	0	3	0	0
1884	3	0	1	0	0	0	0	1	0	0	2	0	1	1
1885	0	0	0	0	0	0	1	0	0	2	0	1	0	1
1886	2	1	0	0	1	1	1	0	0	1	0	1	3	0
1887	1	1	2	1	0	0	3	2	1	1	0	1	2	0
1888	0	1	1	0	0	1	1	0	0	0	0	1	1	0
1889	0	0	1	1	0	1	1	0	0	1	2	2	0	2
1890	1	2	0	2	0	1	1	2	0	2	1	1	2	2
1891	0	0	0	1	1	1	0	1	1	0	3	3	1	0
1892	1	3	2	0	1	1	3	0	1	1	0	1	1	0
1893	0	1	0	0	0	1	0	2	0	0	1	3	0	0
1894	1	0	0	0	0	0	0	0	1	0	1	1	0	0