python split map into rows-CodePudding

I have data as below:

ID	country
01	{"23":1,"45":1,"65":1}
02	{"23":1,"48":1}
03	{"65":1}

And I want to split/explode it like this:

ID	country
01	23
01	45
01	65
02	23
02	48
03	65

how can I do this in python or SQL? P.S., for all the country keys, the value is :1.

CodePudding user response：

df = pd.DataFrame(data={'ID':['01', '02','03'], 'country':[{"23":1,"45":1,"65":1}, {"23":1,"48":1}, {"65":1}]})
df

Dataframe:

    ID  country
0   01  {'23': 1, '45': 1, '65': 1}
1   02  {'23': 1, '48': 1}
2   03  {'65': 1}

Using pandas.DataFrame.explode

df.explode('country', ignore_index=True)

Output:

    ID  country
0   01  23
1   01  45
2   01  65
3   02  23
4   02  48
5   03  65

CodePudding user response：

Let's assume you have a dataframe df

data = {'ID':['01', '02', '03'], 
        'country':[{"23":1,"45":1,"65":1}, 
                                             {"23":1,"48":1},{"65":1}]

       }

df = pd.DataFrame(data)

    ID  country
0   01  {'23': 1, '45': 1, '65': 1}
1   02  {'23': 1, '48': 1}
2   03  {'65': 1}
 
df.explode('country').reset_index(drop=True)

Output:

   ID   country
0   01  23
1   01  45
2   01  65
3   02  23
4   02  48
5   03  65