Map Two Data Frames to create a dictionary with mu

2019-08-01 12:46发布

问题:

I want to create a dictionary that will have multiple values for a key.

I have the following DataFrame df1 like this:

    ID       SUM       COUNT
2   3002    256401.0    15
1   3021    5412.49     21
4   3022    45454.12    12
3   3025    15487.23    45
5   3026    154876.1    88

And I would like to map the ID's to the big lookup table to get multiple values for each ID. My other look up table df2 looks like this,

    ID      Var1    Var2    Var3    Var4
0   3021    Cfd45   shkjs   shkjs   465
1   3021    sfs     None    fjslk   323
2   3021    hjfks   shka    fsad    457
3   3022    None    skank   vssf    236
4   3022    sdkj    vbjajl  bbf     124
5   3025    klgf    vcbj    jgjg    895
6   3026    tuo     None    gdsfs   457
7   3026    klo     ahk     None    569
8   3026    nbjd    alkdjl  sfsfs   124

Now, I want to map these two DataFrames by ID and create a dictionary that has unique ID and multiple values from df2's var1, var2 and var3 columns,

{3021: ['Cfd45', 'shkjs', 'sfs', 'jkdg', 'fjslk', 'hjfks', 'shka', 'fsad'],
3022: ['skank', 'vssf', 'sdkj', vbjajl', 'bbf'],
3025: ...}

The values could be in a list or tuple.

For my scenario, the closest I could find was this post. However, I want values from multiple columns when I map to the table. How can I proceed with this? Any ideas would be appreciated.

回答1:

Let try this on df2:

df2.set_index('ID')[['Var1','Var2','Var3']].stack().groupby(level=0).apply(lambda x: x.unique()).to_dict()

Output:

    {3021: array(['Cfd45', 'shkjs', 'sfs', 'None', 'fjslk', 'hjfks', 'shka', 'fsad'], dtype=object),
 3022: array(['None', 'skank', 'vssf', 'sdkj', 'vbjajl', 'bbf'], dtype=object),
 3025: array(['klgf', 'vcbj', 'jgjg'], dtype=object),
 3026: array(['tuo', 'None', 'gdsfs', 'klo', 'ahk', 'nbjd', 'alkdjl', 'sfsfs'], dtype=object)}

OR

df2.set_index('ID')[['Var1','Var2','Var3']].stack().groupby(level=0).apply(lambda x: x.unique()).to_json()

Output:

'{"3021":["Cfd45","shkjs","sfs","None","fjslk","hjfks","shka","fsad"],"3022":["None","skank","vssf","sdkj","vbjajl","bbf"],"3025":["klgf","vcbj","jgjg"],"3026":["tuo","None","gdsfs","klo","ahk","nbjd","alkdjl","sfsfs"]}'