I am doing a group by on a subset of a dataframe. I am working with 9m+ rows atm, subject to sharp increase. I tried different approaches already and wanted to know if i could squeeze a bit more juice in performances. What I tried :
Option 1

df2=df[["ColA","ColB","ColC"]].query("(ColA == 'X') & (ColB == 2015)")[["ColA","ColC"]].groupby('ColC')['ColA'].size().to_frame(name='My_col_name')

Option 2

df2=df[["ColA","ColB","ColC"]].query("(ColA == 'X') & (ColB == 2015)")[["ColA","ColC"]].groupby("ColC")["ColA"].count().to_frame(name="My_col_name")

Option 3

df2=df[["ColA","ColC"]][(df["ColA"]=='X') & (df["ColB"]==2015)].groupby("ColC")["ColA"].count().to_frame(name="My_col_name")

Option 4

df2=df[["ColA","ColC"]][(df["ColA"]=='X') & (df["ColB"]==2015)].groupby("ColC")["ColA"].size().to_frame(name="My_col_name")

Option 5

df2=df[["ColA","ColB","ColC"]].query("(ColA == 'X') & (ColB == 2015)")[["ColA","ColC"]].groupby('ColC', sort = False)["ColA"].size().to_frame(name='My_col_name')

The best so far is the first one. I am not bounded to pandas specifically but that's probably the method I'm most familiar with

  • \$\begingroup\$ you should add times in question. Maybe you should keep it in database and use SQL. \$\endgroup\$ – furas Jul 24 at 8:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.