I have a csv which contains all of the countries, capital cities, lat, long, continent etc and I am trying to perform some data analysis on the file to return an output showing how many cpatial cities are within each continent. I know i need to use a groupby function on the continents and then somehow count the capitals within each but im just not sure of the syntax or commands for groupby yet. Any suggestions or help would be welcomed. Thanks in advance. My code so far.
import pandas as pd
file = 'C:/Users/Kenne/Documents/College/Python 1/caps.csv'
caps = pd.read_csv(file, delimiter = '\t',
names = [ 'Country', 'Capital','Latitude','Longitude','Country Code','Continent'])
result = caps.groupby(['Continent', 'Capital'], as_index=True).count()
print(result)
output
Continent Capital
Africa Abuja 1 1 1 1
Accra 1 1 1 1
Addis Ababa 1 1 1 1
Algiers 1 1 1 1
Antananarivo 1 1 1 1
Asmara 1 1 1 1
if i understand correctly, you want this:
result = caps.groupby('Continent').count()