pd.DataFrame(data, columns=[]). How to pass a data which is with nested dictionary?

Question

My data is like:

data = {'Sr.No.': Sr_no,
        'CompanyNames': Company_Names,
        'YourChoice1': ['45','45','45','45','45','45','45','45','45'],
        'YourChoice2': ['45','45','45','45','45','45','45','45','45'],
        'Bollinger Bands': {'Field1': ['45','45','45','45','45','45','45','45'],
                            'Field2': ['45','45','45','45','45','45','45','45'],
                            'Field3':['45','45','45','45','45','45','45','45']}
                           }

Which I am passing to the dataframe as:

df = pd.DataFrame(data, columns = ['Sr.No.','CompanyNames','YourChoice1','YourChoice2','Bollinger Bands'])

But I am receiving error as:

ValueError: Mixing dicts with non-Series may lead to ambiguous ordering.

Can anyone help me with this?

The CSV file should look like:

I tried the 1st solution like this:

df1 = pd.DataFrame(data, columns = ['Sr.No.', 'CompanyNames','YourChoice1','YourChoice2'])
bbands = data.pop('Bollinger Bands')
df2 = pd.DataFrame(bbands)

df = pd.concat([df1, df2], axis=1, keys=['','Bollinger Bands'])

But I have obtained the output as:

I want that 'Bollinger Bands' should be in only the first column not in all...'

The desired output is:

 |      |     |     |     |Bollinger Bands|        |        |
 |Sr.No.|Comp |     |     |Field1         |Field2  |Field3  |

Answer 1

pd.DataFrame is expecting a dictionary with list values, but you are feeding an irregular combination of list and dictionary values.

Your desired output is distracting, because it does not conform to a regular MultiIndex, which should avoid empty strings as labels for the first level. Yes, you can obtain your desired output for presentation purposes, but it's not advisable to store your data in an unstructured way.

Instead, I suggest you flatten your dictionary before constructing your dataframe:

data.update(data.pop('Bollinger Bands'))

Then construct a regular dataframe with one header level:

df = pd.DataFrame(data, columns=['Sr.No.','CompanyNames','YourChoice1','YourChoice2',
                                 'Field1', 'Field2', 'Field3'])

This gives:

   Sr.No.  CompanyNames YourChoice1 YourChoice2 Field1 Field2 Field3
0       0             8          45          45     45     45     45
1       1             9          45          45     45     45     45
2       2            10          45          45     45     45     45
3       3            11          45          45     45     45     45
4       4            12          45          45     45     45     45
5       5            13          45          45     45     45     45
6       6            14          45          45     45     45     45
7       7            15          45          45     45     45     45

Sample input data for the above example:

data = {'Sr.No.': list(range(8)),
        'CompanyNames': list(range(8, 16)),
        'YourChoice1': ['45','45','45','45','45','45','45','45'],
        'YourChoice2': ['45','45','45','45','45','45','45','45'],
        'Bollinger Bands': {'Field1': ['45','45','45','45','45','45','45','45'],
                            'Field2': ['45','45','45','45','45','45','45','45'],
                            'Field3':['45','45','45','45','45','45','45','45']}}