How to count unique values in a dictionary of lists with Pandas?

Question

So I have the following data that I'd like to use pandas to show the following output:

             MakeWheel  UpdateWheel  MakeGlass   UpdateGlass MakeChair UpdateChair ...
Toyota.         1            1           1            1          0         0
Mercedes.       2            0           0            0          0         0
Hyndai.         0            0           0            0          8         4
Jeep.           0            0           0            0          2         2    
...

The grouping is based on whether the keys match e.g. UpdateChair, or MakeWheel. If Mercedes we grouped them because the MakeWheel is the same, so we just merge them and count the items in both list, if the items are the same, include them as well, for example in MakeChair case, although right and left are the same items in the list, we would count them all, so we got 8 keywords (Make, Update) to show adjacent to each other.

The cars_dict is

{
    "Toyota": [
        {
            "MakeWheel": [
                "left-wheel"
            ]
        },
        {
            "UpdateWheel": [
                "right-wheel"
            ]
        },
        {
            "MakeGlass": [
                "right-wheel"
            ]
        },
        {
            "UpdateGlass": [
                "right-wheel"
            ]
        }
    ],
    "Mercedes": [
        {
            "MakeWheel": [
                "left-and-right"
            ]
        },
        {
            "MakeWheel": [
                "only-right"
            ]
        }
    ],
    "Hyndai": [
        {
            "MakeChair": [
                "right",
                "left"
            ]
        },
        {
            "MakeChair": [
                "right",
                "left"
            ]
        },
        {
            "MakeChair": [
                "right",
                "left"
            ]
        },
        {
            "MakeChair": [
                "right",
                "left"
            ]
        },
        {
            "UpdateChair": [
                "right",
                "left"
            ]
        },
        {
            "UpdateChair": [
                "right",
                "left"
            ]
        }
    ],
    "Jeep": [
        {
            "MakeChair": [
                "left-and-right",
                "back-only"
            ]
        },
        {
            "UpdateChair": [
                "right-and-left",
                "left"
            ]
        }
    ]
}

For some reason, I'm getting wrong output.

r_list = []
for car_k, car_v in cars_dict.items():
    for i in car_v:
        r = {k: len(v) for k, v in i.items()}
        r_list.append({car_k: r})

pd_list = []
for r in r_list:
    df = pd.DataFrame.from_dict(r)
    pd_list.append(df)
df = pd.concat(pd_list, axis=0)
output = df.transpose().fillna(0)

user_11246056 · Answer

With the initial dictionary you provided, here is one way to do it:

# Import data
df = pd.DataFrame.from_dict(cars_dict, orient="index")

# Transform dicts in Series and append as new columns
df = pd.concat(
    [df[col].apply(lambda x: pd.Series(x, dtype="object")) for col in df.columns]
).dropna(how="all")

# Deal with list of values
for col in df.columns:
    df = df.explode(column=col)

# Count values and cleanup
df = (
    df.groupby(df.index)
    .count()
    .reindex(
        index=["Toyota", "Mercedes", "Hyndai", "Jeep"],
        columns=[
            "MakeWheel",
            "UpdateWheel",
            "MakeGlass",
            "UpdateGlass",
            "MakeChair",
            "UpdateChair",
        ],
    )
)

print(df)
# Output
          MakeWheel  UpdateWheel  MakeGlass  UpdateGlass  MakeChair  UpdateChair
Toyota            1            1          1            1          0            0
Mercedes          2            0          0            0          0            0
Hyndai            0            0          0            0          8            4
Jeep              0            0          0            0          2            2

How to count unique values in a dictionary of lists with Pandas?

1 Answers