CODEWITHSUNDEEP
pythonpandasdataframe
Dexter py
Dexter py

Reputation: 53

Find count of unique value of each column and save in CSV

I have data like this:

+---+---+---+
| A | B | C |
+---+---+---+
| 1 | 2 | 7 |
| 2 | 2 | 7 |
| 3 | 2 | 1 |
| 3 | 2 | 1 |
| 3 | 2 | 1 |
+---+---+---+

Need to count unique value of each column and report it like below:

+---+---+---+
| A | 3 | 3 |
| A | 2 | 1 |
| A | 1 | 1 |
| B | 2 | 5 |
| C | 1 | 3 |
| C | 7 | 2 |
+---+---+---+

I have no issue when number of column are limit and manually name them, when input file is big it become hard,need to have simple way to have output

here is the code I have

import pandas as pd 
df=pd.read_csv('1.csv')

A=df['A']
B=df['B']
C=df['C']

df1=A.value_counts()
df2=B.value_counts()
df3=C.value_counts()

all = {'A': df1,'B': df2,'C': df3}
result = pd.concat(all)
result.to_csv('out.csv')

Upvotes: 1

Views: 159

Answers (3)

Saifudheen P A
Saifudheen P A

Reputation: 11

to find unique values of a data-frame.

df.A.unique()

to know the count of the unique values.

len(df.A.unique())

unique create an array to find the count use len() function

Upvotes: 1

Mohsen
Mohsen

Reputation: 1089

You can loop over column and insert them in dictionary. you can initiate dictionary by all={}. To be scalable you can read column by colm=df.columns. This would give you all column in your df.

Try this code:

import pandas as pd 
df=pd.read_csv('1.csv')
all={}
colm=df.columns
for i in colm:
    all.update({i:df[i].value_counts()})

result = pd.concat(all)
result.to_csv('out.csv')

Upvotes: 1

jezrael
jezrael

Reputation: 863291

Use DataFrame.stack with SeriesGroupBy.value_counts and then convert Series to DataFrame by Series.rename_axis and Series.reset_index and :

df=pd.read_csv('1.csv')

result = (df.stack()
            .groupby(level=1)
            .value_counts()
            .rename_axis(['X','Y'])
            .reset_index(name='Z'))

print (result)
   X  Y  Z
0  A  3  3
1  A  1  1
2  A  2  1
3  B  2  5
4  C  1  3
5  C  7  2

   X  Y  Z
2  A  3  3
0  A  1  1
1  A  2  1
3  B  2  5
4  C  1  3
5  C  7  2

result.to_csv('out.csv', index=False)

Upvotes: 3

Related Questions