Count Values In Pandas Dataframe

Want to learn more? I recommend these Python books: Python for Data Analysis, Python Data Science Handbook, and Introduction to Machine Learning with Python.

Import the pandas module

import pandas as pd

Create all the columns of the dataframe as series

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

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

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

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

horsekick
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

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

result = horsekick.apply(pd.value_counts).fillna(0); result
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

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

pd.value_counts(horsekick['guardCorps'].values, sort=False)
0    9
1    7
2    3
3    1
dtype: int64

List all the unique values in guardCorps

horsekick['guardCorps'].unique()
array([0, 2, 1, 3])