Getting data from Excel in python using pandas in dictionary format

Question

I Have a Excel Data Like This

  Category  Item
  old       apple
  new       mango
  old       grape
  new       ginger

I need to get that data in python using pandas in the dictionary format like -

{'old': ['apple', 'grape'], 'new': ['mango', ginger']}

From one of the references from stack overflow They provide code like this

import pandas as pd

df = pd.read_excel("Skills.xlsx", index_col=0)
df = df.where(pd.notnull(df), None)

print(df.to_dict())

I am getting like output like:

{'Skills': {'old': 'grape', 'new': 'ginger'}}

But I need the output like this:

{'Skills': {'old': ['apple', 'grape'], 'new': ['mango', ginger']}}

Answer 1

You can use apply function after groupby.

df

    Category      Item
0          old   apple
1          new   mango
2          old   grape
3          new  ginger

df.groupby('Category')['Item'].apply(list).to_dict()

{'new': ['mango', 'ginger'], 'old': ['apple', 'grape']}