-2

I have a spreadsheet in excel, named as Students

Year - City - Anniversary - Gender - Course Status - School Origin

 2018 - SP - 13/05/1990 - M - Registered - Public
 2019 - RJ - 12/05/1990 - F - Registered - Particular
 2017 - SP - 13/05/1990 - M - Closed Enrollment - Public

I'm using google colab, I know how to count the values, but I don't know how to do a situational count, for example:

  1. I would like to know how many male students have left the course.

  2. Which city had more students who dropped out of the course.

  3. By year, what was the number of students from public and private schools.

Where should I start and how should I code it?

Skye
  • 3
  • 2
  • 2
    Does this answer your question? [Pandas counting and summing specific conditions](https://stackoverflow.com/questions/20995196/pandas-counting-and-summing-specific-conditions) – Ahmed AEK Sep 26 '22 at 12:55
  • No, because the values ​​I want to relate are in different columns, I don't know how to work that way. – Skye Sep 26 '22 at 13:00
  • did you read the second part where he used the & operator ? – Ahmed AEK Sep 26 '22 at 13:01
  • Yes, but what would the contif code look like? I've never used it, it's giving error here – Skye Sep 26 '22 at 13:04
  • [counting-duplicate-values-in-pandas-dataframe](https://stackoverflow.com/a/33993693/15649230) will also be useful for getting dropped students per city. – Ahmed AEK Sep 26 '22 at 13:08
  • and i think the third one is just doing a filter on private or public school then a groupby year then count. – Ahmed AEK Sep 26 '22 at 13:11

1 Answers1

0

df is your dataframe , just combining this answer and this answer

import pandas as pd
df = pd.read_excel('sheet.xlsx')

count_males_left = int(df[(df["Gender"]=="M") & (df["Course Status"]!="Registered")].count()[0])

df_cities_count = df.groupby(['City']).size().reset_index(name='count')

df_public_by_year = df[df["School Origin"] == "Public"].groupby(['Year']).size().reset_index(name='count')
Ahmed AEK
  • 8,584
  • 2
  • 7
  • 23