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

user_10801499 0
Viewed 4346

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:

enter image description here

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:

enter image description here

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  |
pandas python-3.x python
2 Answers

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']}}
user_92095460

The reason you are getting the error is that you have nested dicts in data. Since you only have Bollinger Bands as a second level dict, you can pop that out and concat that later to your dataframe.

bbands = data.pop('Bollinger Bands')
new_df = pd.concat([pd.DataFrame(data), pd.DataFrame(bbands)], axis=1).set_index('Sr.No.')

(I am assuming that Sr.No. is your index column.)

The above code will create a new dataframe without the Bollinger Bands part of the header. You will have to add that manually to the file and append your dataframe to the same file.

with open('my_csv.csv', 'w') as f:
    f.write("      |     |     |     |Bollinger Bands|        |        \n")
    new_df.to_csv(f, sep='|')

I am not sure why you would need leading and trailing |. So I have omitted in the solution.

user_36793770
Related