Count and groupby in Python

Question

I have created the following code which counts the amount of times a person (by their login_id) has logged into a program over a period of a year.

data1.query("'2015-12-01' <= login_date <= '2016-12-01'").groupby(['employer_key','account_id']).size().reset_index().groupby(['employer_key','account_id'])[[0]].count()

The output looks something like this:

employer_key   account_id  # times logged in
 Apple            X1             1
 Google           Y5             2
 Facebook         X3             4
 Apple            X2             2
 Facebook         Y2             1

I would like to count the number of account_ids for each separate employer_key, so that I can determine how many accounts logged in for each individual employer over a period of a year.

The output would hopefully look something like this:

employer_key   user_logins
 Apple            2             
 Google           1             
 Facebook         2

No, my question is if you want to count unique users per employer. I think your data may already have only unique account_id in that column, so it may be a moot point. — juanpa.arrivillaga
– juanpa.arrivillaga, Commented Mar 6, 2017 at 23:57

DYZ · Accepted Answer · 2017-03-07 00:15:28Z

1

I guess this should work:

data.groupby(['employer_key','account_id']).count().\
             unstack().sum(axis=1).astype(int)
#employer_key
#Apple       2
#Facebook    2
#Google      1
#dtype: int64

answered Mar 7, 2017 at 0:15

DYZ

57.3k10 gold badges73 silver badges101 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

PhishFood Over a year ago

How is my above code factored into the code you provided? When I run your code, it returns me data for all dates, not for my specified one year period. I am not sure how to integrate the one year period into your code. Thanks!

DYZ Over a year ago

My answer is applicable to the output of your code, as shown in your first output block.

PhishFood Over a year ago

What specific part linking the two codes? Thanks so much for your help, I really appreciate it.

Collectives™ on Stack Overflow

Count and groupby in Python

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related