4

I have a dictionary like this:

my_dict = {'Key': {'Service': {'Number': 61, 'Percent': 2.54 }, 'Service2': {'Number': 42, 'Percent': 2.2 } }, 'Key2': {'Service3': {'Number': 8, 'Percent': 2.74}, 'Service2': {'Number': 52, 'Percent': 2.5 } }}

I'm trying to convert this to a pandas dataframe. I got this solution to work

pandas.concat(map(pandas.DataFrame, my_dict.itervalues()), keys=my_dict.keys()).stack().unstack(0)

However, my problem is that that I get a table where the row index is a multindex of Service & Number/Percent. Instead, I want the index to be only the different Services that come up (not a multiindex), and want the columns to be the Keys like they are now, but with 1 column section being Number and the 2nd column section being all the Keys with percent, if that makes sense. Transposing is not what I want, because I don't want the entire index to change, just the Number/Percent part. I want it to look like this, after converting it to a dataframe from the dictionary I wrote above:

          Number         Percent
          Key    Key2    Key     Key2
Service   61     NaN     2.54    NaN
Service2  42     52      2.2     2.5
Service3  NaN    8       NaN     2.74

Any suggestions on this?

Jan G-M
  • 93
  • 1
  • 8

1 Answers1

9
pd.concat({k: pd.DataFrame(v) for k, v in my_dict.items()})

              Service  Service2  Service3
Key  Number     61.00      42.0       NaN
     Percent     2.54       2.2       NaN
Key2 Number       NaN      52.0      8.00
     Percent      NaN       2.5      2.74

pd.concat({k: pd.DataFrame(v) for k, v in my_dict.items()}, axis=1).stack(0).T

         Number       Percent      
            Key  Key2     Key  Key2
Service    61.0   NaN    2.54   NaN
Service2   42.0  52.0    2.20  2.50
Service3    NaN   8.0     NaN  2.74

This doesn't rely on comprehensions 

pd.DataFrame(my_dict).stack().apply(pd.Series).unstack()
# pandas.DataFrame(i).stack().apply(pandas.Series).unstack()

         Number       Percent      
            Key  Key2     Key  Key2
Service    61.0   NaN    2.54   NaN
Service2   42.0  52.0    2.20  2.50
Service3    NaN   8.0     NaN  2.74
piRSquared
  • 285,575
  • 57
  • 475
  • 624
  • Thank you for this -- I seem to be getting a SyntaxError that seems to point to the "for" when I try it - any ideas on why? pandas.concat({k: pandas.DataFrame(v) for k, v in i.items()}).T.swaplevel(0, 1, 1).sort_index(1) ^ SyntaxError: invalid syntax – Jan G-M May 05 '17 at 16:13
  • @jagold1 yeah, you have a typo `pandas.concat({k: pandas.DataFrame(v) for k, v in my_dict.items()}).T.swaplevel(0, 1, 1).sort_index(1)` – piRSquared May 05 '17 at 16:15
  • I'm sorry -- where is the typo? the "i" I indicated is the same as my_dict. I used the same syntax as your response I believe, but I am using pandas instead of pd, otherwise should this work?? --- pandas.concat( {k: pandas.DataFrame(v) for k, v in my_dict.items()} ).T.swaplevel(0, 1, 1).sort_index(1) – Jan G-M May 05 '17 at 16:19
  • @jagold1 Sorry, I assumed that was the typo... This should work. What version of pandas are you using? `pandas.__version__` – piRSquared May 05 '17 at 16:22
  • @piRSquared I'm using 0.10.1 maybe that's why? Is there any other way that might still work this version? – Jan G-M May 05 '17 at 16:24
  • @jagold1 Oh, that's an old version. – piRSquared May 05 '17 at 16:27
  • 3
    What version of python are you using? A SyntaxError doesn't seem likely from an old pandas version. However, dictionary comprehensions weren't introduced until Python 2.7, which would cause a SyntaxError if you are using an earlier version. – root May 05 '17 at 16:29
  • Thanks @root. I wouldn't have thought of that. – piRSquared May 05 '17 at 16:30
  • @jagold1 I've included another possible solution that may work for you. – piRSquared May 05 '17 at 16:32
  • Great, thank you! Really appreciate all the help! Yes sorry I didn't mention I'm having to use version 2.6 only. This works great! – Jan G-M May 05 '17 at 16:40