SORTED.group by and count average of two columns [ sum of col 1 / sum of col 2 ]

SORTED.group by and count average of two columns [ sum of col 1 / sum of col 2 ] - Printable Version

+- Python Forum (https://python-forum.io)
+-- Forum: Python Coding (https://python-forum.io/forum-7.html)
+--- Forum: Data Science (https://python-forum.io/forum-44.html)
+--- Thread: SORTED.group by and count average of two columns [ sum of col 1 / sum of col 2 ] (/thread-40979.html)

SORTED.group by and count average of two columns [ sum of col 1 / sum of col 2 ] - BSDevo - Oct-23-2023

Hi, im trying to group by and get an average of two columns in Pandas then display inside Streamlit_Folium chropleth.geojson map but my KPI and Map averages are different.Trying to group by states and get each state average.
I have a RPM column which is only for dataframe purpose and i created by doing this:

    df["RPM"] = df["Rate"]/df["Miles"]

for KPI i did this:

    filtered_average_rpm = round(filtered_total_earnings / filtered_total_miles, 3)

For chorplet map my code is like this:

filtered_df["StateRPM"] = filtered_df["RPM"].groupby(filtered_df["PuState"]).transform('mean')

    for feature in choropleth.geojson.data["features"]:
        state_name = feature["properties"]["stusab"]
        feature["properties"]["rpm"] = "Average RPM: $" + str(round(indexed_df.loc[state_name, "StateRPM"].mean(), 2)) if state_name in list(indexed_df.index) else ''

But this is no go. KPI average 2.212 and Map average 2.26
KPI average is money / miles and Map average is sum of values and divides by the count and i need sum of money / sum of miles - same way as KPI

If i do:

filtered_df["StateRPM"] = (filtered_df["Rate"]/filtered_df["Miles"]).groupby(filtered_df["PuState"]).transform('mean')

I get this:

Error:
TypeError: ufunc 'divide' not supported for the input types, and the inputs could not be safely coerced to any supported types according to the casting rule ''safe''

If i do:

filtered_df["StateRPM"] = filtered_df["Rate"]/filtered_df["Miles"].groupby(filtered_df["PuState"])

i get this:

Error:
ValueError: setting an array element with a sequence. The requested array has an inhomogeneous shape after 2 dimensions. The detected shape was (44, 2) + inhomogeneous part.

I also tried like this:

filtered_df["RPM"] = filtered_df["RPM"].groupby(filtered_df["PuState"])

got this:

Error:
ValueError: Length of values (1) does not match length of index (58)

How do i get KPI`s average ?
Thank You.

RE: group by and count average of two columns [ sum of col 1 / sum of col 2 ] in Pandas - BSDevo - Oct-23-2023

Solved.

filtered_df["RPM"] = filtered_df["Rate"]/filtered_df["Miles"].groupby(filtered_df["PuState"]).transform('mean')

Turns out i solved it by removing ()

EDIT: No , it makes incorrect calculations

EDIT#2 - sorted.

filtered_df["StateRate"] = filtered_df["Rate"].groupby(filtered_df["PuState"]).transform('mean')
filtered_df["StateRPM"] = filtered_df["StateRate"]/filtered_df["Miles"].groupby(filtered_df["PuState"]).transform('mean')