Adding data to Pandas Dataframe in for loop

Question

I am trying to populate a pandas dataframe from multiple dictionaries. Each of the dictionaries are in the form below:

{'Miami': {'DrPepper': '5', 'Pepsi': '8'}}

{'Atlanta:{'DrPepper':'10','Pepsi':'25'}}

Ultimately what I want is a dataframe that looks like this (After this I plan to use pandas to do some data transformations then output the dataframe to a tab delimited file):

      DrPepper  Pepsi
Miami    5       8
Atlanta  10      25

root · Accepted Answer · 2017-01-10 20:49:30Z

2

If you don't mind using an additional library, you can use toolz.merge to combine all of the dictionaries, followed by DataFrame.from_dict:

import toolz

d1 = {'Miami': {'DrPepper': '5', 'Pepsi': '8'}}
d2 = {'Atlanta': {'DrPepper': '10', 'Pepsi': '25'}}

df = pd.DataFrame.from_dict(toolz.merge(d1, d2), orient='index')

This method assumes that you don't have repeat index values (i.e city names). If you do, the repeats will be overwritten with the last one in the list of dictionaries taking precedence.

The resulting output:

        DrPepper Pepsi
Atlanta       10    25
Miami          5     8

edited Jan 10, 2017 at 20:49

answered Jan 10, 2017 at 20:34

root

34.1k6 gold badges77 silver badges89 bronze badges

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

3 Comments

piRSquared Over a year ago

yet again, you've taught me something tremendously useful.

root Over a year ago

@piRSquared: Thanks, I've found that the toolz library contains tons of useful small functions. Seems like a package you'd enjoy. Also note that there's cytoolz, which is a cython implementation of toolz for enhanced performance

Katie Over a year ago

I have never heard of the toolz library, thanks for this recommendation!

jezrael · Accepted Answer · 2017-01-10 20:25:40Z

You can use concat DataFrames created from dict by DataFrame.from_dict:

d1 = {'Miami': {'DrPepper': '5', 'Pepsi': '8'}}
d2 = {'Atlanta':{'DrPepper':'10','Pepsi':'25'}}

print (pd.DataFrame.from_dict(d1, orient='index'))
      Pepsi DrPepper
Miami     8        5

print (pd.concat([pd.DataFrame.from_dict(d1, orient='index'),
                  pd.DataFrame.from_dict(d2, orient='index')]))
        Pepsi DrPepper
Miami       8        5
Atlanta    25       10

Another solution with transpose by T:

print (pd.DataFrame(d1))
         Miami
DrPepper     5
Pepsi        8

print (pd.concat([pd.DataFrame(d1).T, pd.DataFrame(d2).T]))
        DrPepper Pepsi
Miami          5     8
Atlanta       10    25

Is possible use list comprehension also:

L = [d1,d2]
print (pd.concat([pd.DataFrame(d).T for d in L]))
        DrPepper Pepsi
Miami          5     8
Atlanta       10    25

Collectives™ on Stack Overflow

Adding data to Pandas Dataframe in for loop

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related