0

I recently inherited a python file that has a really long dictionary that I would like to convert to a pandas dataframe. I will then take that dataframe and export it to a database with SqlAlchemy to use as a lookup table.

Here is a very small example of what I'm dealing with:

example_dict = { 'Foo': ['A', 'B', 'C', 'D'],
               'Buzz': ['E', 'F', 'G', 'H'],
                'Fizz': ['I', 'J', 'K'],
                'Fuu':['L']}

As you probably noticed, the length of each list is not the same.

I would like to convert to a dataframe that resembles

Col1    Col2  
A       Foo  
B       Foo  
C       Foo  
D       Foo  
E       Buzz  
F       Buzz  
G       Buzz  
..      ..  
L       Fuu

I've tried using df = pd.DataFrame(example_dict) and then using various methods to try and reshape [df.T, df.melt, df.pivot, df.stack, df.unstack]

I also tried df = pd.DataFrame.from_dict(example_dict, orient='index')

I thought .explode() might be a good route because the pandas documentation example is very similar to my problem, but I keep running into errors because the arrays are not of equal size.

Any help is greatly appreciated. Thank you!

Barmar
  • 741,623
  • 53
  • 500
  • 612
Tommy
  • 695
  • 2
  • 10
  • 15
  • 1
    Easiest would be to write a loop to convert the dictionary into a list of lists, then convert that to a DF. – Barmar Aug 27 '20 at 22:48
  • Does this answer your question? [create a DataFrame from dict of unequal length lists](https://stackoverflow.com/questions/43865806/create-a-dataframe-from-dict-of-unequal-length-lists) – deadshot Aug 27 '20 at 22:55
  • @deadshot that question is similar, but was not very helpful for my situation. Well at least not at my Python level (beginner). Especially with the NaN values. – Tommy Aug 27 '20 at 23:19

1 Answers1

1

Construct a series from example_dict . Next, use explode and reset_index to get the dataframe.

s = pd.Series(example_dict)
df = s.explode().rename_axis('Col2').reset_index(name='Col1')

Out[287]:
    Col2 Col1
0    Foo    A
1    Foo    B
2    Foo    C
3    Foo    D
4   Buzz    E
5   Buzz    F
6   Buzz    G
7   Buzz    H
8   Fizz    I
9   Fizz    J
10  Fizz    K
11   Fuu    L

Note: you may contruct series directly from example_dict

Andy L.
  • 24,909
  • 4
  • 17
  • 29
  • 1
    worked like a champ. I thought I was onto something with .explode(), but wouldn't have got there without your help. thank you so much! – Tommy Aug 27 '20 at 23:15
  • 1
    @Tommy: you are welcome. I was cautious in splitting `values` and `keys` on series construction. In fact, you can directly pass `example_dict` to construct the series. I edited the answer for that :) – Andy L. Aug 27 '20 at 23:19