CODEWITHSUNDEEP
pythonpandasdataframe
user11493942
user11493942

Reputation:

Convert array of strings in arrays of integers in a dataframe column

I'm trying to convert an array of strings in arrays of integers associating its ids in a dataframe column.

That's because I need to map a list of home rooms per id like the next shows:

That's the JSON I have to map:

[ 
   {
     "id": 1,
     "name": "dining room",
   }, 
   {
      "id": 2,
      "name": "living room",
   },
   {
      "id": 3,
      "name": "guest room",
   },
   {
      "id": 4,
      "name": "bathroom",
   },
   {
      "id": 5,
      "name": "game room",
   },
   {
      "id": 6,
      "name": "kitchen",
   },
   {
      "id": 7,
      "name": "storage room",
   },
   {
      "id": 8,
      "name": "bedroom",
   },
   {
      "id": 9,
      "name": "family room",
   }
]

That's the dataframe I have:

index     home_rooms             
0         [dining room, living room, bathroom]                     
1         [guest room, kitchen, game room] 
2         [storage room, family room, bedroom] 
3         [dining room, living room, bathroom] 
4         [guest room, kitchen, game room]
5         [storage room, family room, bedroom] 
6         [dining room, living room, bathroom] 
7         [guest room, kitchen, game room]
8         [storage room, family room, bedroom]

And that's the dataframe I need:

index     home_rooms             
0         [1, 2, 4]                     
1         [3, 6, 5] 
2         [7, 9, 8] 
3         [1, 2, 4]
4         [3, 6, 5]
5         [7, 9, 8] 
6         [1, 2, 4] 
7         [3, 6, 5]
8         [7, 9, 8]

Any solution?

Thanks in advance.

Upvotes: 1

Views: 187

Answers (2)

Andy L.
Andy L.

Reputation: 25239

Let's call the json string as l_str. Load it to dataframe as df_map. From df_map construct dictionary d in the structure name: id. Use itemgetter and list comprehension to construct list of id per index

from operator import itemgetter

df_map = pd.read_json(l_str)
d = dict(zip(df_map.name, df_map.id))
df['home_rooms'] = [list(itemgetter(*x)(d)) for x in df.home_rooms]

Out[415]:
   index home_rooms
0      0  [1, 2, 4]
1      1  [3, 6, 5]
2      2  [7, 9, 8]
3      3  [1, 2, 4]
4      4  [3, 6, 5]
5      5  [7, 9, 8]
6      6  [1, 2, 4]
7      7  [3, 6, 5]
8      8  [7, 9, 8]

Upvotes: 2

Scott Boston
Scott Boston

Reputation: 153460

Try:

mapper = pd.read_json(jsonstr).set_index('name')['id']
df_out = df.explode('home_rooms').replace('dinig room', 'dining room') #fix typo with replace
df_out['home_rooms'] = df_out['home_rooms'].map(mapper)
df_out.groupby('index').agg(list).reset_index()

Output:

   index home_rooms
0      0  [1, 2, 4]
1      1  [3, 6, 5]
2      2  [7, 9, 8]
3      3  [1, 2, 4]
4      4  [3, 6, 5]
5      5  [7, 9, 8]
6      6  [1, 2, 4]
7      7  [3, 6, 5]
8      8  [7, 9, 8]

Upvotes: 1

Related Questions