Convert multiple key dictionary to pandas by comparing the first index each list value in python-CodePudding

I have a dictionary in the following format:

{
    "Description" : [[".1","test"],[".3","test1"],[".4","test2"]],
    "Description1": [[".1","196"],[".4","197"],[".3","198"]],
    "Description3": [[".1","2"],[".3","2"]],
    "Description3": [[".1",".1.3"],[".3",".1.4"],[".4",".1.5"]]
}

where each key has 2D array, and the first value of list is a search/index in my use case. I have to take first element(.1 or .3 or .4) from the list and filter the value of other keys in the same dictionaries and make it in row format like below.

excepted output is:

0 : ["test","196","2",".1.3"]
1 : ["test1","198","2","1.4"]

Note: If index doesn't present in other keys, can fill it up as "None".

Is there any way to do this in better way with Pandas or numpy?

CodePudding user response：

Create dictionaries by nested lists and pass to DataFrame constructor:

d = {
"Description" : [[".1","test"],[".3","test1"],[".4","test2"]],
"Description1": [[".1","196"],[".4","197"],[".3","198"]],
"Description2": [[".1","2"],[".3","2"]],
"Description3": [[".1",".1.3"],[".3",".1.4"],[".4",".1.5"]]
}

df = pd.DataFrame({k: dict(v) for k, v in d.items()})
print (df)
   Description Description1 Description2 Description3
.1        test          196            2         .1.3
.3       test1          198            2         .1.4
.4       test2          197          NaN         .1.5

Or:

df1 = pd.DataFrame.from_dict({k: dict(v) for k, v in d.items()}, orient='index')
print (df1)
                .1     .3     .4
Description   test  test1  test2
Description1   196    198    197
Description2     2      2    NaN
Description3  .1.3   .1.4   .1.5

If need new dictionary:

print (df.T.to_dict('list'))
{'.1': ['test', '196', '2', '.1.3'], 
 '.3': ['test1', '198', '2', '.1.4'], 
 '.4': ['test2', '197', nan, '.1.5']}