Count similar string values in pandas dataframe per year

user_11272958 0
Viewed 111

Having the dataset below. I would like to count the occurrences of a string in a certain year. In reality I have a much bigger dataframe a multiple years. How do I combine a groupby approach and count the different string values?

df = pd.DataFrame(data={'col1':['A','B','D','A'],'col2':['B','C','E','E'],'col3':['C','D','',''],'col4':['D','E','','']},index = ['01-01-2000','02-01-2000','01-01-2001','02-01-2001'])

           col1 col2 col3 col4
01-01-2000    A    B    C    D
02-01-2000    B    C    D    E
01-01-2001    D    E          
02-01-2001    A    E

desired result:

           count
A    2000    1
A    2001    1
B    2000    2    
B    2001    0
C    2000    2
C    2001    0
D    2000    2
D    2001    1
E    2000    1
E    2001    2
pandas pivot-table python
2 Answers

You can create new column year in DataFrame.assign, then reshape by DataFrame.melt, remove empty strings by DataFrame.query, get counts by GroupBy.size, add missing categories by Series.unstack and DataFrame.stack and last use Series.reset_index:

df.index = pd.to_datetime(df.index)

df = (df.assign(year = df.index.year)
        .melt('year', value_name='val')
        .query("val != ''")
        .groupby(['val','year'])
        .size()
        .unstack(fill_value=0)
        .stack()
        .reset_index(name='count')
        )
print (df)
  val  year  count
0   A  2000      2
1   A  2001      1
2   B  2000      2
3   B  2001      0
4   C  2000      2
5   C  2001      0
6   D  2000      1
7   D  2001      1
8   E  2000      1
9   E  2001      2
user_29010020

First of all, let's replace your empty cells with empty values (for example, np.nan), and let's set your index to be a datetime index:

import pandas as pd
import numpy as np
df = pd.DataFrame(data={'col1':['A','B','D','A'],'col2':['B','C','E','E'],'col3':['C','D','',''],'col4':['D','E','','']},index = ['01-01-2000','02-01-2000','01-01-2001','02-01-2001'])
df.index = pd.DatetimeIndex(df.index)
df.replace('',np.nan, inplace=True)

(I've also changed the code to fit the example in your question)

To get your result:

result = df.apply(lambda row: row.value_counts(), axis=1).resample('1Y').sum().stack().reorder_levels([1, 0]).sort_index()

Which yields:

A  2000-12-31    1.0
   2001-12-31    1.0
B  2000-12-31    2.0
   2001-12-31    0.0
C  2000-12-31    2.0
   2001-12-31    0.0
D  2000-12-31    2.0
   2001-12-31    1.0
E  2000-12-31    1.0
   2001-12-31    2.0

Let's break it down:

  • df.apply(lambda row: row.value_counts(), axis=1) transforms your dataframe to have 'A' to 'E' in columns, and the number of occurrences in cell:

                A   B   C   D   E
2000-01-01  1.0 1.0 1.0 1.0 NaN
2000-02-01  NaN 1.0 1.0 1.0 1.0
2001-01-01  NaN NaN NaN 1.0 1.0
2001-02-01  1.0 NaN NaN NaN 1.0

  • .resample('1Y').sum() is using resample, which is just a fancy groupby that deals well with a datetime index, to sum occurrences by year. It yields:

                A   B   C   D   E
2000-12-31  1.0 2.0 2.0 2.0 1.0
2001-12-31  1.0 0.0 0.0 1.0 2.0

  • stack, reorder_levels and sort_index just bring the results to the desired format (well, almost... note that instead of a year it shows the last day of that year)

user_88746960
Related