How do I parse data without using the index because some characters are different lengths

Question

I need to parse this data so that each value in the data parsing column is deposited in its own column.

    userid          data_to_parse
0   54f3ad9a29ada   "value":"N;U;A7;W"}]
1   54f69f2de6aec   "value":"N;U;I6;W"}]
2   54f650f004474   "value":"Y;U;A7;W"}]
3   54f52e8872227   "value":"N;U;I1;W"}]
4   54f64d3075b72   "value":"Y;U;A7;W"}]

So for example, the four additional columns for the first entry would have values of “N”, “U”, “A7”, and “W”. I first attempted to split based upon index like so:

parsing_df['value_one'] = parsing_df['data_to_parse'].str[9:10]
parsing_df['value_two'] = parsing_df['data_to_parse'].str[11:12]
parsing_df['value_three'] = parsing_df['data_to_parse'].str[13:15]
parsing_df['value_four'] = parsing_df['data_to_parse'].str[16:17] 

This worked really well except that there are a few that are different lengths like 937 and 938.

935 54f45edd13582   "value":"N;U;A7;W"}]    N   U   A7  W
936 54f4d55080113   "value":"N;C;A7;L"}]    N   C   A7  L
937 54f534614d44b   "value":"N;U;U;W"}]     N   U   U;  "
938 54f383ee53069   "value":"N;U;U;W"}]     N   U   U;  "
939 54f40656a4be4   "value":"Y;U;A1;W"}]    Y   U   A1  W
940 54f5d4e063d6a   "value":"N;U;A4;W"}]    N   U   A4  W

Does anyone have any solutions that doesn't utilize hard-coded positions?

Thanks for the help!

Answer 1

A relatively simple way to approach the problem:

txt = """54f45edd13582  "value":"N;U;A7;W"}]
54f4d55080113  "value":"N;C;A7;L"}]
54f534614d44b  "value":"N;U;U;W"}]
54f383ee53069  "value":"N;U;U;W"}]
54f40656a4be4  "value":"Y;U;A1;W"}]
54f5d4e063d6a  "value":"N;U;A4;W"}]
"""

import pandas as pd

txt = txt.replace('}','').replace(']','').replace('"','') #first, clean up the data
#then, collect your data (it may be possible to do it w/ list comprehension, but I prefer this):
rows = []
for l in [t.split('\tvalue:') for t in txt.splitlines()]: 
#depending on your actual data, you may have to split by "\nvalue" or "  value" or whatever
    row = l[1].split(';')
    row.insert(0,l[0])
    rows.append(row)
#define your columns
columns = ['userid','value_one','value_two','value_three','value_four'] 
#finally, create your dataframe:
pd.DataFrame(rows,columns=columns)  

Output (pardon the formatting):

        userid          value_one     value_two value_three value_four
0       54f45edd13582   N   U   A7  W
1       54f4d55080113   N   C   A7  L
2       54f534614d44b   N   U   U   W
3       54f383ee53069   N   U   U   W
4       54f40656a4be4   Y   U   A1  W
5       54f5d4e063d6a   N   U   A4  W