0

Hey there everybody I have a xlsx file which I am trying to analyse using Pandas and python. For it I have read the xlsx file using pandas enter image description here

Now from this data frame I have selected only metro cities using pandas loc[]. here is my code

df = pd.read_excel('PCS_NWR_Sheet.xlsx')
metro_cities_df = df.loc[(df['City'] == 'Mumbai') | (df['City'] == 'Chennai') | (df['City'] == 'Bhubneswar') | (df['City'] == 'Kolkata') | (df['City'] == 'Kanpur') | (df['City'] == 'Indore') | (df['City'] == 'Ahmedabad') | (df['City'] == 'Delhi')]
metro_cities_df= metro_cities_df.reset_index()
metro_cities_df = metro_cities_df[['City']]
metro_cities_df

Now I want to count each city for example here is a city name Delhi. I need help with counting each city that how many times they get repeated and I want to put all of the city inside a new dataframe with their name in one column and their count in another columns.

I want to know which function such as count() or something like that can I use to do my work?

Thanks In Advance..

Kapil Yadav
  • 25
  • 6
  • Hello please check if that helps: https://stackoverflow.com/questions/10373660/converting-a-pandas-groupby-output-from-series-to-dataframe – kakou Jun 16 '21 at 06:10
  • 1
    `df[df.City.isin(['Mumbai', 'Chennai', 'Bhubneswar'])].groupby('City').size().reset_index(name='count')` ??? – Pygirl Jun 16 '21 at 06:15

0 Answers0