extract strings and insert as multiple rows based on original index

user_15707526 0
Viewed 267

I have put example dataset (df), expected output (df2) and my code so far below. I have a df where some rows in column i2 contain a list - in the json format, which need exploding and reinserting back into the df, from the row in which they were extracted. But the need to be inputted into a different column (i1). i need to extract a unique identifier (the 'id_2' value) from the string and insert that into the id_2 column.

in my code so far i am parsing the json-like data with pd.normalize, and then inserting the original string from the column i1 onto the top of the extracted strings (it should be much more clear if you take a look below) and then reinsert them based on the index. But I have to specify the index, which is not good. I would like it to be less dependent on manual input of indices in case it changes in the future with more of these nested cells or somehow the index changes.

Any suggestions are very welcome, thanks so much

example data

import pandas as pd

df = pd.DataFrame(data={'id': [1, 2, 3, 4, 5], 'id_2': ['a','b','c','d','e'], 'i1': ['How old are you?','Over the last month have you felt','Do you live alone?','In the last week have you had','When did you last visit a doctor?'], 'i2': [0,0,0,0,0]})
df['i2'] = df['i2'].astype('object')

a = [{'id': 'b1', 'item': 'happy?', 'id_2': 'hj59'}, {'id': 'b2', 'item': 'sad?', 'id_2': 'dgb'}, {'id': 'b3', 'item': 'angry?', 'id_2':'kj9'}, {'id': 'b4', 'item': 'frustrated?','id2':'lp7'}]
b = [{'id': 'c1', 'item': 'trouble sleeping?'}, {'id': 'c2', 'item': 'changes in appetite?'}, {'id': 'c3', 'item': 'mood swings?'}, {'id': 'c4', 'item': 'trouble relaxing?'}]

df.at[1, 'i2'] = a 
df.at[3, 'i2'] = b

expected output

df2 = pd.DataFrame(data={'id': [1,2,2,2,2,3,4,4,4,4,5], 
                         'id_2': ['a','hj59','dgb','kj9','lp7','c','d','d','d','d','e'],
                         'i1': ['How old are you?',
                                'Over the last month have you felt happy?',
                                'Over the last month have you felt sad?',
                                'Over the last month have you felt angry?',
                                'Over the last month have you felt frustrated?',
                                'Do you live alone?',
                                'In the last week have you had trouble sleeping?',
                                'In the last week have you had changes in appetite?',
                                'In the last week have you had mood swings?',
                                'In the last week have you had trouble relaxing?',
                                'When did you last visit a doctor?'], 
                         'i2': [0,1,1,1,1,0,1,1,1,1,0]})

my ugly code so far

s={}
s = df[df.i2 != 0]

n={}

for i in range(len(s)):
    n[i] = pd.json_normalize(s.loc[s.index[i]]['i2']).reset_index(inplace=False, drop=False)  
    n[i]['i1'] = s.iloc[i].i1 + ' ' + n[i]['item']
    def insert_row(i, d1, d2): return d1.iloc[:i, ].append(d2)
    for i in n:
        if i == 0:
            x = insert_row(s.iloc[i].name, df, n[i])
        elif i == 1:
            x = insert_row(s.iloc[i].name+1+n[i]['index'].count()+1, x, n[i]) 
            y = x.append(df.iloc[s.iloc[i].name+1:, ])
pandas insert indexing python json
1 Answers

Explode the dataframe on column i2, then retrieve the values associated with key item from the column i2 using the str accessor, then using indexing with loc update the values in column i2 to 1 and concatenate the strings in i1 with the retrieved item values

df2 = df.explode('i2', ignore_index=True)
s = df2['i2'].str['item']
df2.loc[s.notna(), 'i2'] =  1
df2.loc[s.notna(), 'i1'] += ' ' + s

    id                                                  i1 i2
0    1                                    How old are you?  0
1    2            Over the last month have you felt happy?  1
2    2              Over the last month have you felt sad?  1
3    2            Over the last month have you felt angry?  1
4    2       Over the last month have you felt frustrated?  1
5    3                                  Do you live alone?  0
6    4     In the last week have you had trouble sleeping?  1
7    4  In the last week have you had changes in appetite?  1
8    4          In the last week have you had mood swings?  1
9    4     In the last week have you had trouble relaxing?  1
10   5                   When did you last visit a doctor?  0
user_128331660
Related