CODEWITHSUNDEEP
pythonpandas
Nate
Nate

Reputation: 167

How to turn dictionary with varied list pairs, into dataframe columns and rows?

I have a dictionary, with keys that are intended to be an index, and multiple list pairs. How can i turn that dictionary into a dataframe, where the first item in the list is a column in the dataframe and the second item is the respective column value? Each key has a varied number of list pairs.

My list looks something like this:

{1234: [['Facebook','www.facebook.com/4countyelectric'], ['Name', '4 County Electric'], ['Phone', '1-800-555-1111']], 
1235: [['Name', '3 Phases Energy'], ['Twitter', '@3phases'], ['Phone', '1-800-444-1111'],
1236: [['Phone', '585-719-1234'], ['Outage Number', '585-719-1235'], ['Name', 'Rochester Gas & Electric']]}

I'm trying to create a dataframe that looks like this, and takes into account whether or not the first list pair item is currently a dataframe column:

Index  Name                       Twitter  Facebook           Phone            Outage Number

1234   4 County Electric           Null    www.facebook.com/  1-800-555-1111   Null
1235   3 Phases Energy            @3phases Null               1-800-444-1111   Null
1236   Rochester Gas & Electric    Null    Null               585-719-1234     585-719-1235

How can this be done?

Each time I've tried I keep getting "list index out of range" as an error.

Upvotes: 0

Views: 67

Answers (1)

pakpe
pakpe

Reputation: 5479

First, convert your dictionary of nested lists to a dictionary of dictionaries. Then it's easy to convert it to a DataFrame:

import pandas as pd

dic = {1234: [['Facebook','www.facebook.com/4countyelectric'], ['Name', '4 County Electric'], ['Phone', '1-800-555-1111']],
1235: [['Name', '3 Phases Energy'], ['Twitter', '@3phases'], ['Phone', '1-800-444-1111']], 1236: [['Phone', '585-719-1234'], ['Outage Number', '585-719-1235'], ['Name', 'Rochester Gas & Electric']]}
    
for k,v in dic.items():
    inner_dic = {}
    for item in v:
        inner_dic[item[0]] = item[1]
    dic[k] = inner_dic
    
df = pd.DataFrame(dic).transpose()
    
#to display all columns, I truncated the facebook address.
with pd.option_context('display.max_columns', 10):
    print (df)

#output:
[3 rows x 5 columns]
               Facebook                      Name           Phone   Twitter  \
1234  www.facebook.com/         4 County Electric  1-800-555-1111       NaN   
1235                NaN           3 Phases Energy  1-800-444-1111  @3phases   
1236                NaN  Rochester Gas & Electric    585-719-1234       NaN   

     Outage Number  
1234           NaN  
1235           NaN  
1236  585-719-1235

Upvotes: 2

Related Questions