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']}}

Lazyer · Accepted Answer · 2022-10-27 09:16:53Z

2

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']}

answered Oct 27, 2022 at 9:16

Lazyer

9851 gold badge6 silver badges19 bronze badges

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

2 Comments

Thanks, Lazyer For your answer it is working for me. If you don't mind can you send the document link where I can found that

I recommend looking at this article!