create and concatenate a data frame from the dictionary of lists

user_14885707 0
Viewed 54

I have a two dictionaries like below

dic1 = { 'T1': ['INC1','INC2','INC3'] , 'T2': ['INC2','INC4','INC5'],'T3': ['INC2']}
dic2 = { 'INC1' :'LOC1','INC2' :'LOC2','INC3' :'LOC3','INC4' :'None','INC5' :'None'}

i have created the data frame for dic1 using following code

import pandas as pd
df_1 = pd.DataFrame.from_dict(dic1, orient='index').stack()

I have got output like below

T1  0    INC1
    1    INC2
    2    INC3
T2  0    INC2
    1    INC4
    2    INC5
T3  0    INC2
dtype: object

now i want to conacatenate dic2 by compariing key value pair of dic1 ..and i expected the output like below

T1  0    INC1  LOC1
    1    INC2  LOC2
    2    INC3  LOC3
T2  0    INC2  LOC2
    1    INC4  None
    2    INC5  Nnoe
T3  0    INC2  LOC2
dtype: object

I have tried with following code and it says list is not iterable and i tried to use for loop but no luck.

result = {k:dic2[v] for k, v in dic1.items()}
df_2 = pd.DataFrame.from_dict(result , orient='index').stack()
Final_result = pd.concat([df_1, df_2], axis=1)

Could you please help me out to achive the same. Thanks in Advance!!

pandas python-3.x dictionary python
2 Answers

You can use map then convert to frame (to_frame) and assign a column:

out = df_1.to_frame('col1').assign(col2=df_1.map(dic2))

print(out)

      col1  col2
T1 0  INC1  LOC1
   1  INC2  LOC2
   2  INC3  LOC3
T2 0  INC2  LOC2
   1  INC4  None
   2  INC5  None
T3 0  INC2  LOC2
user_98406370

As an alternative, this can be done with list comprehension:

pd.DataFrame([[x, y, dic2[y]] for x, ys in dic1.items() for y in ys])

Output:

    0     1     2
0  T1  INC1  LOC1
1  T1  INC2  LOC2
2  T1  INC3  LOC3
3  T2  INC2  LOC2
4  T2  INC4  None
5  T2  INC5  None
6  T3  INC2  LOC2
user_67927430
Related