CODEWITHSUNDEEP
pythondictionarydataframe
CodeLearner
CodeLearner

Reputation: 67

Is there a way to create a dataframe from a dictionary with list of values?

I have a dictionary myDict and I'd like to create a dataframe df using this myDict as follows:

myDict = {
1: [''],
2: ['07/19/2017', ' 10/18/2007', '12/20/2002','12/20/2002' ],
3: ['07/19/2017', ' 10/18/2007'],
4: ['12/13/1993'],
5: [''],
6: ['08/01/2007'],
7: ['04/23/2007'],
8: ['02/06/2007'],
9: ['02/06/2007'],
10: ['11/08/2001'],
11: [''],
12: [''],
13: ['12/20/2002']
}

df
ID    Col1         Col2          Col3         Col4
1     
2     07/19/2017   10/18/2007    12/20/2002   12/20/2002 
3     07/19/2017   10/18/2007 
4     12/13/1993   
5     
6     08/01/2007
7     04/23/2007
8     02/06/2007
9     02/06/2007
10    11/08/2001
11    
12    
13    12/20/2002

How do I make this possible? Thank you.

Putting everything into a function isn't working...

def split_Date(df):
    Dates1 = df.set_index('IDX')['Date'].to_dict()
    dates = {}
    for k, v in Dates1.items():
       v = v.split(',')
       dates[k] = [i for i in v]
    dates = {k: sorted(v, key=lambda x: datetime.strptime(x.strip(), "%m/%d/%Y") if x != "" else x) for k, v in dates.items()}
    df_dates = pd.DataFrame.from_dict(dates, orient="index").fillna('').rename_axis("IDX").rename(columns="Date{}".format).reset_index()
    df = pd.merge(df, df_dates, on='IDX', how='inner', suffixes=('_chem', '_df'))
    return df #Adding this doesn't make any difference

Running this code outside a function works perfectly. However, this requires me to change the value of myData in all the lines for everytime I have a new data. This isn't as productive as having a function

Dates1 = myData.set_index('IDX')['Date'].to_dict()
dates = {}
for k, v in Dates1.items():
    v = v.split(',')
    dates[k] = [i for i in v]
dates = {k: sorted(v, key=lambda x: datetime.strptime(x.strip(), "%m/%d/%Y") 
if x != "" else x) for k, v in dates.items()}
df_dates = pd.DataFrame.from_dict(dates, orient="index").fillna('').rename_axis("IDX").rename(columns="Date{}".format).reset_index()
myData = pd.merge(myData, df_dates, on='IDX', how='inner', suffixes=('_chem', '_df'))

Upvotes: 1

Views: 58

Answers (1)

akuiper
akuiper

Reputation: 214927

You can read it in using pd.DataFrame.from_dict and set the key as index via orient parameter:

pd.DataFrame.from_dict(myDict, orient="index").fillna('')

#            0           1           2           3
#1              
#2  07/19/2017  10/18/2007  12/20/2002  12/20/2002
#3  07/19/2017  10/18/2007      
#4  12/13/1993          
#5              
#6  08/01/2007          
# ...

To set the keys as a separate column, you can use reset_index:

(pd.DataFrame.from_dict(myDict, orient="index")
 .fillna('')
 .rename_axis("ID")
 .rename(columns="Col{}".format)
 .reset_index())

#  ID         Col0        Col1        Col2        Col3
#0  1               
#1  2   07/19/2017  10/18/2007  12/20/2002  12/20/2002
#2  3   07/19/2017  10/18/2007      
#3  4   12/13/1993          
#4  5
# ...

Upvotes: 4

Related Questions