CODEWITHSUNDEEP
pandasdataframedictionary
JamesHudson81
JamesHudson81

Reputation: 2273

Getting a dataframe from a dictionary of dictionaries

I have a resulting_dictionary with these keys and values:

{"A":{"z1":[1,2,3],"z2":[3,2,2],"z3":"mary"},
"B":{"z1":[4,4,3],"z2":[2,1,1],"z3":"john","z4":[6,5,4],"z5":[2,2,2]}}

I am looking forward to turn it to a df to look like this:

    z1    z2    z3    z4     z5
A   1     3     mary   nan   nan
A   2     2     mary   nan   nan
A   3     2     mary   nan   nan
B   4     2     john    6     2
B   4     1     john    5     2
B   3     1     john    4     2

This is my code:

    df = pd.DataFrame.from_dict(resulting_dictionary, orient='index').reset_index() 

    lst_col=list(df.columns)
    empty_df=pd.DataFrame()
    for nums in lst_col:
        try:
            res = pd.DataFrame({
                col: np.repeat(df[col].values, df[nums].str.len())
                for col in df.columns.drop(nums)}).assign(
                **{nums: np.concatenate(df[nums].values)})[df.columns]

        except TypeError:
            empty_df[nums] = [None] * 6

        empty_df['index'] = res['index']
        empty_df['z3'] = res['z3']
        empty_df[nums] = res[nums]
    print(empty_df)

I have tried some code but I think I am extending myself very much to reach to the desired output. Any alternative way to easily get the df from the resulting_dictionary?

Upvotes: 1

Views: 51

Answers (1)

Parthasarathy Subburaj
Parthasarathy Subburaj

Reputation: 4264

You could do this:

import pandas as pd
import numpy as np

data = {"A":{"z1":[1,2,3],"z2":[3,2,2],"z3":"mary"},
"B":{"z1":[4,4,3],"z2":[2,1,1],"z3":"john","z4":[6,5,4],"z5":[2,2,2]}}

final_df = pd.DataFrame()
for i,j in data.items():
  q = pd.concat({k: pd.Series(v) for k, v in j.items()}, axis=1)
  q.index=np.repeat(i,len(j[list(j.keys())[0]]))
  q.groupby("z3").ffill()
  q['z3'] = q['z3'].ffill()
  final_df= pd.concat([final_df, q])

Output:

    z1  z2  z3  z4  z5
A   1   3   mary    NaN NaN
A   2   2   mary    NaN NaN
A   3   2   mary    NaN NaN
B   4   2   john    6.0 2.0
B   4   1   john    5.0 2.0
B   3   1   john    4.0 2.0

Upvotes: 1

Related Questions