Convert Python dict into a dataframe
PythonPandasDataframePython Problem Overview
I have a Python dictionary like the following:
{u'2012-06-08': 388,
u'2012-06-09': 388,
u'2012-06-10': 388,
u'2012-06-11': 389,
u'2012-06-12': 389,
u'2012-06-13': 389,
u'2012-06-14': 389,
u'2012-06-15': 389,
u'2012-06-16': 389,
u'2012-06-17': 389,
u'2012-06-18': 390,
u'2012-06-19': 390,
u'2012-06-20': 390,
u'2012-06-21': 390,
u'2012-06-22': 390,
u'2012-06-23': 390,
u'2012-06-24': 390,
u'2012-06-25': 391,
u'2012-06-26': 391,
u'2012-06-27': 391,
u'2012-06-28': 391,
u'2012-06-29': 391,
u'2012-06-30': 391,
u'2012-07-01': 391,
u'2012-07-02': 392,
u'2012-07-03': 392,
u'2012-07-04': 392,
u'2012-07-05': 392,
u'2012-07-06': 392}
The keys are Unicode dates and the values are integers. I would like to convert this into a pandas dataframe by having the dates and their corresponding values as two separate columns. Example: col1: Dates col2: DateValue (the dates are still Unicode and datevalues are still integers)
Date DateValue
0 2012-07-01 391
1 2012-07-02 392
2 2012-07-03 392
. 2012-07-04 392
. ... ...
. ... ...
Any help in this direction would be much appreciated. I am unable to find resources on the pandas docs to help me with this.
I know one solution might be to convert each key-value pair in this dict, into a dict so the entire structure becomes a dict of dicts, and then we can add each row individually to the dataframe. But I want to know if there is an easier way and a more direct way to do this.
So far I have tried converting the dict into a series object but this doesn't seem to maintain the relationship between the columns:
s = Series(my_dict,index=my_dict.keys())
Python Solutions
Solution 1 - Python
The error here, is since calling the DataFrame constructor with scalar values (where it expects values to be a list/dict/... i.e. have multiple columns):
pd.DataFrame(d)
ValueError: If using all scalar values, you must must pass an index
You could take the items from the dictionary (i.e. the key-value pairs):
In [11]: pd.DataFrame(d.items()) # or list(d.items()) in python 3
Out[11]:
0 1
0 2012-07-02 392
1 2012-07-06 392
2 2012-06-29 391
3 2012-06-28 391
...
In [12]: pd.DataFrame(d.items(), columns=['Date', 'DateValue'])
Out[12]:
Date DateValue
0 2012-07-02 392
1 2012-07-06 392
2 2012-06-29 391
But I think it makes more sense to pass the Series constructor:
In [21]: s = pd.Series(d, name='DateValue')
Out[21]:
2012-06-08 388
2012-06-09 388
2012-06-10 388
In [22]: s.index.name = 'Date'
In [23]: s.reset_index()
Out[23]:
Date DateValue
0 2012-06-08 388
1 2012-06-09 388
2 2012-06-10 388
Solution 2 - Python
When converting a dictionary into a pandas dataframe where you want the keys to be the columns of said dataframe and the values to be the row values, you can do simply put brackets around the dictionary like this:
>>> dict_ = {'key 1': 'value 1', 'key 2': 'value 2', 'key 3': 'value 3'}
>>> pd.DataFrame([dict_])
key 1 key 2 key 3
0 value 1 value 2 value 3
It's saved me some headaches so I hope it helps someone out there!
EDIT: In the pandas docs one option for the data parameter in the DataFrame constructor is a list of dictionaries. Here we're passing a list with one dictionary in it.
Solution 3 - Python
As explained on another answer using pandas.DataFrame() directly here will not act as you think.
What you can do is use pandas.DataFrame.from_dict with orient='index':
In[7]: pandas.DataFrame.from_dict({u'2012-06-08': 388,
u'2012-06-09': 388,
u'2012-06-10': 388,
u'2012-06-11': 389,
u'2012-06-12': 389,
.....
u'2012-07-05': 392,
u'2012-07-06': 392}, orient='index', columns=['foo'])
Out[7]:
foo
2012-06-08 388
2012-06-09 388
2012-06-10 388
2012-06-11 389
2012-06-12 389
........
2012-07-05 392
2012-07-06 392
Solution 4 - Python
Pass the items of the dictionary to the DataFrame constructor, and give the column names. After that parse the Date column to get Timestamp values.
Note the difference between python 2.x and 3.x:
In python 2.x:
df = pd.DataFrame(data.items(), columns=['Date', 'DateValue'])
df['Date'] = pd.to_datetime(df['Date'])
In Python 3.x: (requiring an additional 'list')
df = pd.DataFrame(list(data.items()), columns=['Date', 'DateValue'])
df['Date'] = pd.to_datetime(df['Date'])
Solution 5 - Python
p.s. in particular, I've found Row-Oriented examples helpful; since often that how records are stored externally.
Solution 6 - Python
Pandas have built-in function for conversion of dict to data frame.
> pd.DataFrame.from_dict(dictionaryObject,orient='index')
For your data you can convert it like below:
import pandas as pd
your_dict={u'2012-06-08': 388,
u'2012-06-09': 388,
u'2012-06-10': 388,
u'2012-06-11': 389,
u'2012-06-12': 389,
u'2012-06-13': 389,
u'2012-06-14': 389,
u'2012-06-15': 389,
u'2012-06-16': 389,
u'2012-06-17': 389,
u'2012-06-18': 390,
u'2012-06-19': 390,
u'2012-06-20': 390,
u'2012-06-21': 390,
u'2012-06-22': 390,
u'2012-06-23': 390,
u'2012-06-24': 390,
u'2012-06-25': 391,
u'2012-06-26': 391,
u'2012-06-27': 391,
u'2012-06-28': 391,
u'2012-06-29': 391,
u'2012-06-30': 391,
u'2012-07-01': 391,
u'2012-07-02': 392,
u'2012-07-03': 392,
u'2012-07-04': 392,
u'2012-07-05': 392,
u'2012-07-06': 392}
your_df_from_dict=pd.DataFrame.from_dict(your_dict,orient='index')
print(your_df_from_dict)
Solution 7 - Python
This is what worked for me, since I wanted to have a separate index column
df = pd.DataFrame.from_dict(some_dict, orient="index").reset_index()
df.columns = ['A', 'B']
Solution 8 - Python
pd.DataFrame({'date' : dict_dates.keys() , 'date_value' : dict_dates.values() })
Solution 9 - Python
The simplest way I found is to create an empty dataframe and append the dict.
You need to tell panda's not to care about the index, otherwise you'll get the error: TypeError: Can only append a dict if ignore_index=True
import pandas as pd
mydict = {'foo': 'bar'}
df = pd.DataFrame()
df = df.append(mydict, ignore_index=True)
Solution 10 - Python
This is how it worked for me :
df= pd.DataFrame([d.keys(), d.values()]).T
df.columns= ['keys', 'values'] # call them whatever you like
I hope this helps
Solution 11 - Python
You can also just pass the keys and values of the dictionary to the new dataframe, like so:
import pandas as pd
myDict = {<the_dict_from_your_example>]
df = pd.DataFrame()
df['Date'] = myDict.keys()
df['DateValue'] = myDict.values()
Solution 12 - Python
In my case I wanted keys and values of a dict to be columns and values of DataFrame. So the only thing that worked for me was:
data = {'adjust_power': 'y', 'af_policy_r_submix_prio_adjust': '[null]', 'af_rf_info': '[null]', 'bat_ac': '3500', 'bat_capacity': '75'}
columns = list(data.keys())
values = list(data.values())
arr_len = len(values)
pd.DataFrame(np.array(values, dtype=object).reshape(1, arr_len), columns=columns)
Solution 13 - Python
Accepts a dict as argument and returns a dataframe with the keys of the dict as index and values as a column.
def dict_to_df(d):
df=pd.DataFrame(d.items())
df.set_index(0, inplace=True)
return df
Solution 14 - Python
I think that you can make some changes in your data format when you create dictionary, then you can easily convert it to DataFrame:
input:
a={'Dates':['2012-06-08','2012-06-10'],'Date_value':[388,389]}
output:
{'Date_value': [388, 389], 'Dates': ['2012-06-08', '2012-06-10']}
input:
aframe=DataFrame(a)
output: will be your DataFrame
You just need to use some text editing in somewhere like Sublime or maybe Excel.
Solution 15 - Python
d = {'Date': list(yourDict.keys()),'Date_Values': list(yourDict.values())}
df = pandas.DataFrame(data=d)
If you don't encapsulate yourDict.keys() inside of list() , then you will end up with all of your keys and values being placed in every row of every column. Like this:
Date \ 0 (2012-06-08, 2012-06-09, 2012-06-10, 2012-06-1... 1 (2012-06-08, 2012-06-09, 2012-06-10, 2012-06-1... 2 (2012-06-08, 2012-06-09, 2012-06-10, 2012-06-1... 3 (2012-06-08, 2012-06-09, 2012-06-10, 2012-06-1... 4 (2012-06-08, 2012-06-09, 2012-06-10, 2012-06-1...
But by adding list() then the result looks like this:
Date Date_Values 0 2012-06-08 388 1 2012-06-09 388 2 2012-06-10 388 3 2012-06-11 389 4 2012-06-12 389 ...
Solution 16 - Python
I have run into this several times and have an example dictionary that I created from a function get_max_Path(), and it returns the sample dictionary:
{2: 0.3097502930247044, 3: 0.4413177909384636, 4: 0.5197224051562838, 5: 0.5717654946470984, 6: 0.6063959031223476, 7: 0.6365209824708223, 8: 0.655918861281035, 9: 0.680844386645206}
To convert this to a dataframe, I ran the following:
df = pd.DataFrame.from_dict(get_max_path(2), orient = 'index').reset_index()
Returns a simple two column dataframe with a separate index:
index 0 0 2 0.309750 1 3 0.441318
Just rename the columns using f.rename(columns={'index': 'Column1', 0: 'Column2'}, inplace=True)
Solution 17 - Python
The point is how to put each element in a dataFarame. Row-wise:
'pd.DataFrame(dic.items(), columns=['Date', 'Value'])'
or columns-wise:
'pd.DataFrame([dic])'