Ranking category columns by values in another column

Question

Simplified example - I have a dataframe with an 'ID' column, a 'value' column and a 'category' column. Each ID is given a category between 0 and 4 and currently have no meaning other to differentiate the groups.

I want to give the group numbers meaning based on the 'value' column - e.g the group with the lowest values in the 'value' column become group 0, the group with the highest values in the 'value' column become group 4, instead of the random group numbers they have currently. Ideas on how to approach this? I've looked at ranks etc but this ranks all IDs in a group, and the groups can have 50+ IDs. I need to retain the current groupings, but give them more meaningful category numbers

Dataframe sample as is:

Expected result:

Background - the groups have actually been arrived at through kmeans clustering the value column. Kmeans though doesn't give any meaning to the category numbers as there would normally be several dimensions to the clustering not just one, and so doing this wouldn't make sense outside of on a single column. In the real world, I'm doing this on a big df with tons of value and category columns so I'd be doing the process on each. I know there may be better ways of categorising a 1D array other than kmeans, but i want to ignore that for now and deal with the problem as above (there will be other applications for solving it for me this way elsewhere).

Thanks

user_11246056 · Answer

Given the following toy dataframe:

import pandas as pd

df = pd.DataFrame(
    {
        "ID": [
            "E01018000",
            "E01018001",
            "E01018002",
            "E01018003",
            "E01018004",
            "E01018005",
            "E01018006",
            "E01018007",
            "E01018008",
            "E01018009",
            "E01018010",
            "E01018011",
            "E01018012",
            "E01018013",
        ],
        "Category": [3, 3, 0, 0, 0, 0, 0, 0, 4, 4, 4, 4, 4, 1],
        "Value": [
            158000,
            193000,
            225000,
            238750,
            242500,
            242500,
            245000,
            257000,
            274950,
            277500,
            285000,
            310000,
            310000,
            340000,
        ],
    }
)

Here is one basic way to do it, based on the existsing categories of Category column:

df["Category"] = df["Category"].map(
    lambda x: {value: i for i, value in enumerate(df["Category"].unique())}[x]
)

print(df)
# Output
           ID  Category   Value
0   E01018000         0  158000
1   E01018001         0  193000
2   E01018002         1  225000
3   E01018003         1  238750
4   E01018004         1  242500
5   E01018005         1  242500
6   E01018006         1  245000
7   E01018007         1  257000
8   E01018008         2  274950
9   E01018009         2  277500
10  E01018010         2  285000
11  E01018011         2  310000
12  E01018012         2  310000
13  E01018013         3  340000

A more general approach would be to calculate some sort of distance between values and group them in clusters accordingly.

Here is a naive way to do it:

# Calculate distance between values
df = df.sort_values("Value")
df["previous"] = df["Value"].shift(1).fillna(method="bfill")
df["distance"] = df["Value"] - df["previous"]

# Group values if distance > 5% of values mean
# (arbitrarily chosen way of clustering values)
clusters = df.copy().loc[df["distance"] > 0.05 * df["Value"].mean()]
clusters["cluster"] = [f"c{i}" for i in range(clusters.shape[0])]

# Add clusters back to dataframe and cleanup
df = (
    pd.merge(
        how="left",
        left=df,
        right=clusters["cluster"],
        left_index=True,
        right_index=True,
    )
    .fillna(method="ffill")
    .fillna(method="bfill")
    .drop(columns=["previous", "distance"])
    .reset_index(drop=True)
)

print(df)
# Output
           ID  Category   Value cluster
0   E01018000         3  158000      c0
1   E01018001         3  193000      c0
2   E01018002         0  225000      c1
3   E01018003         0  238750      c2
4   E01018004         0  242500      c2
5   E01018005         0  242500      c2
6   E01018006         0  245000      c2
7   E01018007         0  257000      c2
8   E01018008         4  274950      c3
9   E01018009         4  277500      c3
10  E01018010         4  285000      c3
11  E01018011         4  310000      c4
12  E01018012         4  310000      c4
13  E01018013         1  340000      c5

Ranking category columns by values in another column

1 Answers