Suppose there is a pandas dataframe which has one column consisting of names of something, and there are multiple entries respective to each entry in the first column. To count the number of entries in the second column for each entry in the first, applying count method grouped by entries on the first is the classical solution. And it works as intended.

Here's something concrete-

There are multiple entries for each of the county e.g. California. I found the number of entries for each county by executing this code-

enter image description here

Now I want to have something like this-

|               county|             number_of_venues|
|Alameda              |                  some number|
|San Diego            |                  some number|
|Yolo                 |                  some number|

and so on.

I tried achieving this by-

df = pd.DataFrame(california_venues.groupby('county').count())

Which gives me this weird dataframe-

enter image description here

Where county is not a key. It just sits on top of it. Trying to access the entries of the column through county raises KeyError.

My plan was to extract the two relevant columns once the desired dataframe had been formed.And finally rename the column name(s).

What are my options to get the form described by me in the markdown table?


As county is not a key when you make the new dataframe so you can't access it. So you just need to reset index so that county can be accessed when required.

df = pd.DataFrame(california_venues.groupby('county').count().reset_index())

Not the answer you're looking for? Browse other questions tagged or ask your own question.