0

I'm attempting to get the count of each unique value for a given column. For example, I'd like to get the following results from the dataframe:

df = pd.DataFrame.from_dict({'id': [1,1,2,3,3,3], 'a': [1,2,3,4,5,6], 'b': [6,5,4,3,2,1]})

What I want is a dataframe that looks like:

pd.DataFrame.from_dict({'id': [1,2,3], 'count': [2, 1, 3]})

I found that I can get close to this with:

df.groupby('id', as_index=False).agg('count')[['id', 'a']]

But I'm looking for a way that's a bit more descriptive of what is happening / "pythonic". Additionally, I'd have to do a column rename as well. Is there a better way to accomplish this?

David
  • 2,080
  • 5
  • 29
  • 44
  • 1
    You can look into groupby and will see many answers.Use name parameter to name grouped column, df.groupby('id')['a'].count().reset_index(name = 'count') – Vaishali Feb 10 '19 at 05:34
  • Is there a way to do this without having to reference some arbitrary column (such as 'a' in this example)? I'm looking for a descriptive way to perform this operation. I'll edit the original question to clarify being descriptive instead of just being "pythonic". – David Feb 10 '19 at 06:25
  • 1
    @David - I think you can check [this](https://stackoverflow.com/q/53781634/2901002) - part `4. How to aggregate counts?` – jezrael Feb 10 '19 at 06:29
  • Thanks @jezrael Looks like `df['id'].value_counts().sort_index().rename_axis('id').reset_index(name='count')` would closest match to what I'm trying to do. – David Feb 10 '19 at 07:01
  • @David - Yes, I rather add link like answer, because a bit wide problematic. Be free upvote Q/A if helpful. – jezrael Feb 10 '19 at 07:03

0 Answers0