Grouping with Python

Question

I have a dataset I am trying to group by some common values and then sum up some other values. The tricky part is I want to add some sort of weighting that keeps the largest number, I'll try to elaborate more below:

I've created a dummy data frame that is along the lines of my data just for example purposes:

df = pd.DataFrame({'Family': ['Contactors', 'Contactors', 'Contactors'], 
                   'Cell': ['EP&C', 'EXR', 'C&S'],
                    'Visits': ['25620', '626', '40']})

This produces a table like so:

So, in this example I would want all of the 'Contactors' to be grouped up by EP&C (as this has the highest visits to start with) but I would like all of the visits summed up and the other 'Cell' values dropped, so I would be left with something like this:

Could anyone advise?

Thanks.

mozway · Accepted Answer · 2022-05-16 12:22:15Z

2

IIUC, you can use:

(df
 # convert to numeric
 .assign(Visits=pd.to_numeric(df['Visits']))
 # ensure the top row per group is the highest visits
 .sort_values(by=['Family', 'Visits'], ascending=False)
 # for groups per Family
 .groupby('Family', sort=False, as_index=False)
 # aggregate per group: Cell (first row, i.e top) and Visits (sum of rows)
 .agg({'Cell': 'first', 'Visits': sum})
)

output:

       Family  Cell  Visits
0  Contactors  EP&C   26286

edited May 16, 2022 at 12:22

answered May 16, 2022 at 11:15

mozway

267k13 gold badges56 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Stuquan Over a year ago

Brilliant, thank you! If you wouldn't mind, could you explain your code a bit more? I'm unfamiliar with .agg for example.

Stuquan Over a year ago

Hi @mozway, may I ask: is it possible to use inplace=true here to store the new dataframe? I am having trouble with this.

mozway Over a year ago

@Stuquan given the different shape, not directly, what would be the expected output?

Stuquan Over a year ago

I'd want to export this output into a .csv file.

mozway Over a year ago

I meant the explicit data, not the storage ;)

|

Collectives™ on Stack Overflow

Grouping with Python

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related