0

I have a nested dictionary of sets of dates, d

d= {"A": {'a': {datetime1, datetime2}, 'b': {datetime3}, 'c':{datetime4},
    "B": {'a': datetime5, datetime1, datetime3}}

I want a pandas DataFrame df

          dates   
A    a    datetime1
          datetime2
     b    datetime3
          ...

This might be a duplicate question of Nested dictionary to multiindex dataframe where dictionary keys are column labels

However, I couldn't get the advise given in that question to work here, so I dare to repost the question. (However, I have previously successfully used methods in that question on other nested dictionaries). So, doing something like 

df = pd.DataFrame.from_dict({(i, j): d[i][j]
                                     for i in d.keys()
                                     for j in d[i].keys()},
                                     orient='index')

creates a mess of thousands of integers as columns (one for each date maybe?), and tuple (i,j) as a single index (instead of two levels of indexes, i and j). Is the problem simply because here I have only one column in the dataframe? Can't I have a multiindexed series? Or am I missing something very obvious?

Community
  • 1
  • 1
Antti Ellonen
  • 111
  • 6

1 Answers1

2

I think you should separate the dict into the indices and their data, then make a pd.MultiIndex and a pd.Series. I'm working off the example given here, but modified slightly for your dictionary.

index, series = zip(*[((i, j), d[i][j]) for i in d for j in d[i]])

index = pd.MultiIndex.from_tuples(index, names=['Captial', 'lowercase'])
series = pd.Series(series, index=index)
Patrick Haugh
  • 59,226
  • 13
  • 88
  • 96