Dictionary Creation from DataFrame, Pandas, Python

Question

I want to create a dictionary where the key is a state name and the value is the sum of all the count corresponding to the state.

Example from dataFrame

State         Count
California    100
Colorado      100
California    54
Colorado      9254

I was trying to use the following, but not sure how to include a sum function inside this, also not sure if I should be using a zip method here:

df_dict = dict(zip(df.State, df.Count))
print df_dict

Pekka · Accepted Answer · 2015-08-06 09:55:31Z

1

Calculate the sum by group in the dataframe and then select the 'Count' column and use to_dict() to convert the resulting Series to dictionary:

df_dict = df.groupby('State').agg('sum')['Count'].to_dict()
print df_dict

If you have more columns in the df than those two you probably want to do

df[['State','Count']].groupby('State').agg('sum')['Count'].to_dict()

to avoid calculating the sum for all of the columns.

Edit:

as EdChum suggested .agg('sum') can be shortened to .sum() which gives you the same result.

edited Aug 6, 2015 at 9:55

answered Aug 6, 2015 at 6:24

Pekka

2,4582 gold badges23 silver badges34 bronze badges

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

4 Comments

madman Over a year ago

perfect, not sure if the ['Count'] is needed. unless i am missing something? @Pekka

Pekka Over a year ago

It is needed to select the first (in this case only) column from the resulting DataFrame. Try it without it. You will get a nested dictionary. In that case your desired result is in the Count key.

Pekka Over a year ago

So when we use the ['Count'] selection we get Series instead of DataFrame. Then we convert the Series (not DataFrame) to dictionary. Note that also DataFrames can be converted to dictionary with to_dict() but the result is different (nested dict). See: pandas.pydata.org/pandas-docs/stable/generated/… and pandas.pydata.org/pandas-docs/stable/generated/…

EdChum Over a year ago

I think the agg is unnecessary here

ShadowRanger · Accepted Answer · 2015-08-06 05:59:11Z

1

That won't work properly, since the dict constructor will replace the value of each state as it goes, rather than summing. It's not a one-liner, but:

from collections import Counter

df_dict = Counter()
for state, count in zip(df.State, df.Count):
    df_dict[state] += count

will get you the total counts, and the Counter class has some extra functionality that may be useful for dictionaries of counts.

answered Aug 6, 2015 at 5:59

ShadowRanger

158k12 gold badges221 silver badges314 bronze badges

Collectives™ on Stack Overflow

Dictionary Creation from DataFrame, Pandas, Python

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related