CODEWITHSUNDEEP
pythonpandasdataframerollup
Kshitij Marwah
Kshitij Marwah

Reputation: 1151

Rolling up data frame along with count of rows in python

I am still in a learning phase in python and wanted to know how do we roll up the data and count the duplicate data rows in a column called count

The data frame structure is as follows 

Col1| Value
A   |  1
B   |  1
A   |  1
B   |  1
C   |  3
C   |  3
C   |  3
C   |  3

My result should be as follows

Col1|Value|Count
 A  |  1  | 2
 B  |  1  | 2
 C  |  3  | 4

Upvotes: 2

Views: 3913

Answers (3)

deepika
deepika

Reputation: 81

You can also try:

df.groupby('Col1')['Value'].value_counts().reset_index(name='Count')

Upvotes: 0

Ami Tavory
Ami Tavory

Reputation: 76391

Roman Pekar's fine answer is correct for this case. However, I saw it after trying to write a solution for the general case stated in the text of your question, not just the example with specific column names. So, for the general case, consider:

df.groupby([df[c] for c in df.columns]).size().reset_index().rename(columns={0: 'Count'})

For example:

import pandas as pd

df = pd.DataFrame({'Col1': ['a', 'a', 'a', 'b', 'c'], 'Value': [1, 2, 1, 3, 2]})

>>> df.groupby([df[c] for c in df.columns]).size().reset_index().rename(columns={0: 'Count'})
    Col1    Value   Count
0   a   1   2
1   a   2   1
2   b   3   1
3   c   2   1

Upvotes: 3

roman
roman

Reputation: 117636

>>> df2 = df.groupby(['Col1', 'Value']).size().reset_index()
>>> df2.columns = ['Col1', 'Value', 'Count']
>>> df2
  Col1  Value  Count
0    A      1      2
1    B      1      2
2    C      3      4

Upvotes: 5

Related Questions