Group multiple columns as key:value dicts in pandas

user_9227940 0
Viewed 297

I have dataframe with > 50000 rows with data like this (there are 6 more columns for another countries):

Item no.          Property EN            Value EN            Property DE  \
0  1.10231  Inner diameter [mm]                  11  Innendurchmesser [mm]   
1  1.10231  Outer diameter [mm]                  18  Außendurchmesser [mm]   
2  1.10231          Length [mm]                  68             Länge [mm]   
3  1.10231             Warranty    2 years warranty               Garantie   
4  1.10231           Valve type  for exhaust valves              Ventilart   
5  1.10231           Valve type    for inlet valves              Ventilart   
6  1.10230  Inner diameter [mm]                  12  Innendurchmesser [mm]   

             Value DE             Property ES                   Value ES  
0                  11  Diámetro interior [mm]                         11  
1                  18  Diámetro exterior [mm]                         18  
2                  68           Longitud [mm]                         68  
3    2 Jahre Garantie                Garantía         2 años de garantía  
4  für Auslassventile         Tipo de válvula    para válvulas de escape  
5  für Einlassventile         Tipo de válvula  para válvulas de admisión  
6                  12  Diámetro interior [mm]                         12

I don't understand how to convert it to dataframe like this:

Item no.  Property EN                                                                        Property DE
1.10231   {Inner diameter [mm]: 11, Valve type: [for exhaust valves, for inlet valves], ...} {Innendurchmesser [mm]: 11, ...}
1.10230   {Inner diameter [mm]: 12, Valve type: [for exhaust valves, for inlet valves], ...} {Innendurchmesser [mm]: 12, ...}

Another words group by 'Item no.', and for each property column convert all values to dict like {property:value}, and if there are more than one value for property - add them all to list (like "Valve type" in example above). And like this for each country property-value pair.

Global task is to merge multiple csv by 'Item no.' with different grouping for each one, and then export it all to JSON.

I'm trying to use this code, but it works not like I need, I think it's the wrong way to do it...

properties = ['Property EN', 'Property DE', 'Property ES', 
    'Property FR', 'Property IT', 'Property NL', 'Property PTBR', 'Property RU']
df = df.set_index(properties).groupby('Item no.').agg(
            prop_EN = ('Value EN', lambda s: s.to_dict()),
            prop_DE = ('Value DE', lambda s: s.to_dict()),
            prop_ES = ('Value ES', lambda s: s.to_dict()),
            prop_FR = ('Value FR', lambda s: s.to_dict()),
            prop_IT = ('Value IT', lambda s: s.to_dict()),
            prop_NL = ('Value NL', lambda s: s.to_dict()),
            prop_PTBR = ('Value PTBR', lambda s: s.to_dict()),
            prop_RU=('Value RU', lambda s: s.to_dict())
        )

How can I do it?

pandas python
1 Answers

Here is one way to approach the problem

def dictify(g):
    return {k: list(v) if len(v) > 1 else v.item() 
            for k, v in g.groupby('Property')['Value']}

s = df.set_index('Item no.')
s.columns = s.columns.str.split(expand=True)

s.stack().groupby(level=[0, 1]).apply(dictify).unstack().add_prefix('Property ')

Result

Item no. Property DE Property EN Property ES
1.1023 {'Innendurchmesser [mm]': '12'} {'Inner diameter [mm]': '12'} {'Diámetro interior [mm]': '12'}
1.10231 {'Außendurchmesser [mm]': '18', 'Garantie': '2 Jahre Garantie', 'Innendurchmesser [mm]': '11', 'Länge [mm]': '68', 'Ventilart': ['für Auslassventile', 'für Einlassventile']} {'Inner diameter [mm]': '11', 'Length [mm]': '68', 'Outer diameter [mm]': '18', 'Valve type': ['for exhaust valves', 'for inlet valves'], 'Warranty': '2 years warranty'} {'Diámetro exterior [mm]': '18', 'Diámetro interior [mm]': '11', 'Garantía': '2 años de garantía', 'Longitud [mm]': '68', 'Tipo de válvula': ['para válvulas de escape', 'para válvulas de admisión']}
user_128331660
Related