CODEWITHSUNDEEP
pythonpandasgroup-bydataframe
madman
madman

Reputation: 153

Grouping in Pandas

I want to group data in a dataframe I have oo the Column "Count" and by another column "State". I would like to output a list of list, each sub set list would just be the count for each state.

example output: [[120,200], [40, 20, 40], ...]

120 and 200 would be counts for let's say the State California

I tried the following: 

df_new = df[['State']].groupby(['Count']).to_list()

I get a keyerror: 'count'

Traceback:

Traceback (most recent call last):
  File "C:\Users\Michael\workspace\UCIIntrotoPythonDA\src\Michael_Madani_week3.py", line 84, in <module>
    getStateCountsDF(filepath)
  File "C:\Users\Michael\workspace\UCIIntrotoPythonDA\src\Michael_Madani_week3.py", line 81, in getStateCountsDF
    df_new = df[['State']].groupby(['Count']).to_list() 
  File "C:\Users\Michael\Anaconda\lib\site-packages\pandas\core\generic.py", line 3159, in groupby
    sort=sort, group_keys=group_keys, squeeze=squeeze)
  File "C:\Users\Michael\Anaconda\lib\site-packages\pandas\core\groupby.py", line 1199, in groupby
    return klass(obj, by, **kwds)
  File "C:\Users\Michael\Anaconda\lib\site-packages\pandas\core\groupby.py", line 388, in __init__
    level=level, sort=sort)
  File "C:\Users\Michael\Anaconda\lib\site-packages\pandas\core\groupby.py", line 2148, in _get_grouper
    in_axis, name, gpr = True, gpr, obj[gpr]
  File "C:\Users\Michael\Anaconda\lib\site-packages\pandas\core\frame.py", line 1797, in __getitem__
    return self._getitem_column(key)
  File "C:\Users\Michael\Anaconda\lib\site-packages\pandas\core\frame.py", line 1804, in _getitem_column
    return self._get_item_cache(key)
  File "C:\Users\Michael\Anaconda\lib\site-packages\pandas\core\generic.py", line 1084, in _get_item_cache
    values = self._data.get(item)
  File "C:\Users\Michael\Anaconda\lib\site-packages\pandas\core\internals.py", line 2851, in get
    loc = self.items.get_loc(item)
  File "C:\Users\Michael\Anaconda\lib\site-packages\pandas\core\index.py", line 1572, in get_loc
    return self._engine.get_loc(_values_from_object(key))
  File "pandas\index.pyx", line 134, in pandas.index.IndexEngine.get_loc (pandas\index.c:3824)
  File "pandas\index.pyx", line 154, in pandas.index.IndexEngine.get_loc (pandas\index.c:3704)
  File "pandas\hashtable.pyx", line 686, in pandas.hashtable.PyObjectHashTable.get_item (pandas\hashtable.c:12280)
  File "pandas\hashtable.pyx", line 694, in pandas.hashtable.PyObjectHashTable.get_item (pandas\hashtable.c:12231)
KeyError: 'Count'

I feel like this should be a simple line of code, what am I doing wrong here?

Upvotes: 0

Views: 538

Answers (1)

chris-sc
chris-sc

Reputation: 1718

It is possible as a one-liner:

import pandas as pd                                                                           

df = pd.DataFrame.from_dict({"State": ["ny", "or", "ny", "nm"],                     
                             "Counts": [100,300,200,400]})

list_new = df.groupby("State")["Counts"].apply(list).tolist()
print(list_new)

[[400], [100, 200], [300]]

You should read the doc of groupby to see what the expected outcome of the grouping is and how to change that (http://pandas.pydata.org/pandas-docs/stable/groupby.html).

Upvotes: 1

Related Questions