1

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.

Improve this question
5
  • Do you really want a column with lists? You are losing all the speed and ease of pandas. Commented Jun 25, 2020 at 16:40
  • Yes, I need a column with lists, because the idea is generate a dict with other columns. Later, I'll post a JSON to API which attribute of home_rooms must be an array of integers. Commented Jun 25, 2020 at 16:45
  • You should do that conversion as late as possible, but your decision. Commented Jun 25, 2020 at 16:46
  • 1
    I guess the column home_room in your dataframe contains the typo in the spelling of dining, right? Commented Jun 25, 2020 at 16:49
  • 1
    Right, @ShubhamSharma. Commented Jun 25, 2020 at 16:58

2 Answers 2

Reset to default
2

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]
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

I get this error: "type object argument after * must be an iterable, not float".
It means your columns df.home_rooms besides having list also having float. As Scott commented, you should revised or post new question with more detail representing your data
Yes, I'll use a representative data because the example was short.
1

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]
Improve this answer

12 Comments

@ShubhamSharma Thanks.
I get this error: "Reindexing only valid with uniquely valued Index objects".
@StivenRamírezArango Do you have duplicate names in your json string?
Okay, you are extending this question, best to close this question and start a new question.
I agree OP should close this question and revise it with more detail. It seems his home_rooms also has single value of float.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.