4

I have a pandas dataframe which looks like:

df = pd.DataFrame(data={'id':[1234, 1234, 1234, 1234, 1234], 'year':['2017', '2017', '2018', '2018', '2018'], 'count_to_today':[1, 2, 3, 3, 4]})
df
     id  year  count_to_today
0  1234  2017               1
1  1234  2017               2
2  1234  2018               3
3  1234  2018               3
4  1234  2018               4

And I need to count how many times count_to_today happens in each year per id. i.e. I have a running count since the beginning of time, and I want to count the number of times it increments per year.

           count_in_year
id   year               
1234 2017              2
     2018              2

I'm a bit confused about how to do this. I know I need to groupby id and year but I can't figure out how to get .count() or .value_counts() to give me the counts per year.

cs95
  • 379,657
  • 97
  • 704
  • 746
Tom Kealy
  • 2,537
  • 2
  • 27
  • 45

3 Answers3

3

If you want to count ID per Year try using -

df[['ID','Year']].groupby('Year').count()

or- 

df[['ID','Year']].groupby('Year').agg('count')

Change variables as you want to get your result.

Shahir Ansari
  • 1,682
  • 15
  • 21
  • This does not address the part of the question that asks "I want to count the number of times it increments per year."... – cs95 Mar 05 '19 at 10:02
2

You can use diff and groupby:

df.count_to_today.diff().ne(0).groupby([df.id, df.year]).sum()

id    year
1234  2017    2.0
      2018    2.0
Name: count_to_today, dtype: float64

(df.count_to_today.diff()
   .ne(0)
   .groupby([df.id, df.year])
   .sum()
   .astype(int)
   .reset_index())

     id  year  count_to_today
0  1234  2017               2
1  1234  2018               2
cs95
  • 379,657
  • 97
  • 704
  • 746
0

Use this structure:

df[['ID','Year']].groupby('Year').count()

and

df[['ID','Year']].groupby('Year').agg('count')

I hope this will work fine.Try this

Hasee Amarathunga
  • 1,901
  • 12
  • 17