Python: Pandas Create new csv with contents counted

Question

I'm reading in a csv and I'm trying to count how many times each entry appears The csv looks like this:

     Image#     color1     color2    color3
     1          red        blue      yellow
     2          blue       blue      red
     3          white      red       pink

What I'm wanting to do is write it to a new csv with something like:

df = pd.read_csv("colors.csv")
counted = df.groupby(["color1", "color2", "color3"]).size()
df.to_csv("counted.csv")

except I want it to output

Red    3
Blue   3
Pink   1
Yellow 1
White  1

I'm fine if there isn't a way to do this, but how would I at least get the total occurrences of each of those colors? Thanks

Community · Accepted Answer · 2017-05-23 12:06:32Z

2

Use Stack.

http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.stack.html

Get count of values across columns-Pandas DataFrame

import pandas as pd

Dict = {'Image': [1,2,3],
    'color1': ['red','blue','white'],
    'color2': ['blue','blue','red'],
    'color3': ['yellow','red','pink']}

df = pd.DataFrame(Dict)
df.set_index('Image',inplace=True)

Color_Counts = df.stack().value_counts()
Color_Counts.to_csv('Color_Counts.csv')

edited May 23, 2017 at 12:06

CommunityBot

11 silver badge

answered Feb 11, 2015 at 1:24

Liam Foley

7,8822 gold badges28 silver badges24 bronze badges

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

1 Comment

Johnsonge Over a year ago

Thanks! Worked like a charm!

Collectives™ on Stack Overflow

Python: Pandas Create new csv with contents counted

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related