Python Pandas dict to dataframe (not working)

Question

I am using Spyder as Python IDE. I have downloaded data via API. The API only allows the data to be downloaded as a "dict" type. The "Dict" type has 3 levels i.e. Dict,Unicode, Dataframe. I am currently trying to extract the information from Dataframe into a separate DataFrame variable which I intend to export to SQLite. However, I am unable to do this as the information is saved under type "Dict" and using command

frame4=pd.DataFrame.from_dict(response)

returns the following error message:

frame4=pd.DataFrame(response) Traceback (most recent call last):

File "", line 1, in frame4=pd.DataFrame(response)

File "/Users/Sebster/anaconda/lib/python2.7/site-packages/pandas/core/frame.py", line 224, in init mgr = self._init_dict(data, index, columns, dtype=dtype)

File "/Users/Sebster/anaconda/lib/python2.7/site-packages/pandas/core/frame.py", line 360, in _init_dict return _arrays_to_mgr(arrays, data_names, index, columns, dtype=dtype)

File "/Users/Sebster/anaconda/lib/python2.7/site-packages/pandas/core/frame.py", line 5236, in _arrays_to_mgr arrays = _homogenize(arrays, index, dtype)

File "/Users/Sebster/anaconda/lib/python2.7/site-packages/pandas/core/frame.py", line 5546, in _homogenize raise_cast_failure=False)

File "/Users/Sebster/anaconda/lib/python2.7/site-packages/pandas/core/series.py", line 2922, in _sanitize_array subarr = _asarray_tuplesafe(data, dtype=dtype)

File "/Users/Sebster/anaconda/lib/python2.7/site-packages/pandas/core/common.py", line 1407, in _asarray_tuplesafe result[:] = [tuple(x) for x in values]

ValueError: cannot copy sequence with size 17 to array axis with dimension 10

the content of the dictionary i.e. dict(response) gives the following output:

{u'allowance': {u'allowanceExpiry': 554347,
  u'remainingAllowance': 9960,
  u'totalAllowance': 10000},
 u'instrumentType': u'CURRENCIES',
 u'prices':                          bid                                 ask           \
                         Open     High      Low    Close     Open     High   
 DateTime                                                                    
 2016:08:12-21:50:00  11163.7  11164.6  11163.7  11164.1  11165.2  11165.6   
 2016:08:12-21:51:00  11164.2  11164.8  11163.7  11164.7  11165.7  11166.2   
 2016:08:12-21:52:00  11164.5  11165.3  11164.4  11165.1  11166.0  11166.6   
 2016:08:12-21:53:00  11165.0  11165.8  11164.3  11164.5  11166.5  11167.2   
 2016:08:12-21:54:00  11164.6  11165.4  11164.3  11164.7  11166.1  11166.9   
 2016:08:12-21:55:00  11164.6  11165.8  11164.1  11165.1  11166.1  11167.2   
 2016:08:12-21:56:00  11165.3  11165.3  11163.9  11163.9  11166.8  11166.8   
 2016:08:12-21:57:00  11164.1  11164.9  11163.4  11164.6  11165.6  11166.4   
 2016:08:12-21:58:00  11164.5  11165.2  11164.0  11164.9  11165.1  11166.2   
 2016:08:12-21:59:00  11161.3  11162.8  11157.9  11159.2  11166.3  11167.8   

                                       spread                  last        \
                          Low    Close   Open High  Low Close  Open  High   
 DateTime                                                                   
 2016:08:12-21:50:00  11164.7  11165.6    1.5  1.0  1.0   1.5  None  None   
 2016:08:12-21:51:00  11165.2  11166.2    1.5  1.4  1.5   1.5  None  None   
 2016:08:12-21:52:00  11165.8  11166.6    1.5  1.3  1.4   1.5  None  None   
 2016:08:12-21:53:00  11165.8  11166.0    1.5  1.4  1.5   1.5  None  None   
 2016:08:12-21:54:00  11165.8  11166.2    1.5  1.5  1.5   1.5  None  None   
 2016:08:12-21:55:00  11165.6  11166.6    1.5  1.4  1.5   1.5  None  None   
 2016:08:12-21:56:00  11165.4  11165.4    1.5  1.5  1.5   1.5  None  None   
 2016:08:12-21:57:00  11164.3  11165.2    1.5  1.5  0.9   0.6  None  None   
 2016:08:12-21:58:00  11164.6  11165.5    0.6  1.0  0.6   0.6  None  None   
 2016:08:12-21:59:00  11162.9  11164.2    5.0  5.0  5.0   5.0  None  None   


                       Low Close Volume  
 DateTime                                
 2016:08:12-21:50:00  None  None     37  
 2016:08:12-21:51:00  None  None     45  
 2016:08:12-21:52:00  None  None     46  
 2016:08:12-21:53:00  None  None     80  
 2016:08:12-21:54:00  None  None     45  
 2016:08:12-21:55:00  None  None     58  
 2016:08:12-21:56:00  None  None     35  
 2016:08:12-21:57:00  None  None    115  
 2016:08:12-21:58:00  None  None     60  
 2016:08:12-21:59:00  None  None    162  }

Can someone please help me out with this endeavour of extracting the Dataframe from the Dict variable into a separate Dataframe?

hello Daniel, I attempted the pd.DataFrame(response) statement earlier. it returns the same error message. — Seb_aj

MaxU MaxU · Accepted Answer · 2016-08-14T11:13:08

looking at your response dict - a value of the key prices seems to be a DataFrame, so there is no need to construct it again:

frame4 = response['prices']

or simply:

import sqlalchemy

engine = sqlalchemy.create_engine('sqlite:///path/to/your_db.sqlite')
response['prices'].to_sql('table_name', engine, if_exists='replace')

Python Pandas dict to dataframe (not working)

1 Answers