Turn a single entry in a dictionary of dictionaries into a dataframe where keys are the columns

Question

Given a dictionary such as:

records = {0:{'name':'John', 'salary':'5000'}, 1:{'name':'Bob', 'salary':'3500'}}

If I want to get and store (in csv) a dataframe such as:

name  salary
John   5000

By using records[0] as a way to access the dictionary inside, how would I do that?

I have tried:

df = pd.DataFrame(records[0], index=[0])

df= pd.DataFrame(list(records[0].values()), columns=list(records[0].keys()))

df= pd.DataFrame.from_dict(records[key], orient='columns')

But none of them worked as intended (2nd one gave me an error, 1st and last have just one column)

A dictionary isn't always ordered in a particular way. Are you ordering it a certain way prior to selecting the data you need? How is the original dictionary getting created? Will you always need only the first value? — dasvootz, Jun 21 '19 at 13:17
Use pandas to make dataframe from csv file > turn to dict > start loop:: use for key in dict: to loop over each "row" > add two fields > append row to an initially empty csv file > end loop — Mohamad Moustafa, Jun 21 '19 at 13:19
Why do you need pandas to write to the csv file? You can just write the dictionary to the csv: https://stackoverflow.com/questions/10373247/how-do-i-write-a-python-dictionary-to-a-csv-file — dasvootz, Jun 21 '19 at 13:26

jezrael · Accepted Answer · 2019-06-21T13:13:04.890

6

Use DataFrame.from_dict with orient='index':

df = pd.DataFrame.from_dict(records, orient='index')
print (df)
   name salary
0  John   5000
1   Bob   3500

EDIT - For first value of record pass to nested list:

df = pd.DataFrame([records[0]])
print (df)
   name salary
0  John   5000

edited Jun 21 '19 at 13:13

answered Jun 21 '19 at 13:10

jezrael

822,522
95
1,334
1,252

I just want to turn the first entry to a dataframe. I dont want to turn both, I already know how to do that. – Mohamad Moustafa Jun 21 '19 at 13:11
I didnt want to turn it all to a dataframe because I am dealing with a dictionary with thousands of entries and I will be doing this operation every step while iterating over the dictionary. The dictionary comes from a csv file that's larger than 500mb, so turning the entire thing to a dataframe and indexing at every iteration will take too much time I think. – Mohamad Moustafa Jun 21 '19 at 13:17
@MohamadMoustafa Are you turning the entire file into an csv sheet or only a subset? You're working with a dictionary so you can just iterate through the dict and keep only what you want before turning it into a dataframe. (Or read the csv line and put what you want into the dataframe). – Error - Syntactical Remorse Jun 21 '19 at 13:19
use this to turn only the first entry - `pd.DataFrame.from_dict([records[0]], orient='columns')` – nag Jun 21 '19 at 13:22
I use to_dict to turn entire dataframe created using csv file to dict. I then loop over each "row" (dict inside dict) , make changes, turn "row" to dataframe, and append it to empty csv file. – Mohamad Moustafa Jun 21 '19 at 13:22
@nag I am not using a static number,I use key. This is inside a ```for key in dict:``` loop. – Mohamad Moustafa Jun 21 '19 at 13:23

score 3 · Answer 2 · answered Jun 21 '19 at 13:13

You could use a combination of DataFrame.from_dict() and DataFrame.T :

records = {0:{'name':'John', 'salary':'5000'}, 1:{'name':'Bob', 'salary':'3500'}}
pd.DataFrame.from_dict(records).T

Output

+----+-------+--------+
|    | name  | salary |
+----+-------+--------+
| 0  | John  |   5000 |
| 1  | Bob   |   3500 |
+----+-------+--------+

EDIT To get just the first record

df[df.index==0]

+----+-------+--------+
|    | name  | salary |
+----+-------+--------+
| 0  | John  |   5000 |
+----+-------+--------+

Turn a single entry in a dictionary of dictionaries into a dataframe where keys are the columns

2 Answers2