Convert Comma Separated String in Nested JSON to List Python

Question

My JSON data looks like this:

   [{
        "coverageResponse": "100000\/500000\/1000000",
        "insuranceLine": "COMMERCIAL",
        "coverageCategory": "COVERAGE",
        "carriers": [{
            "carrierId": "LMICO",
            "states": "GA, CA",
            "excludedStates": "PA, NY"
        }, {
            "carrierId": "CNICO",
            "states": "NY, PA",
            "excludedStates": "CA, MI, OH"
        }]
    },
    {
        "coverageResponse": "222\/333\/111",
        "insuranceLine": "COMMERCIAL",
        "coverageCategory": "COVERAGE",
        "carriers": [{
            "carrierId": "LMICO",
            "states": "GA, CA",
            "excludedStates": "PA, NY"
        }, {
            "carrierId": "CNICO",
            "states": "NY, PA",
            "excludedStates": "CA, MI, OH"
        }]
    }]

I want states and excludedStates inside carriers to be in a list

Here is my output expectation:

 [{
        "coverageResponse": "100000\/500000\/1000000",
        "insuranceLine": "COMMERCIAL",
        "coverageCategory": "COVERAGE",
        "carriers": [{
            "carrierId": "LMICO",
            "states": ["GA, CA"],
            "excludedStates": ["PA, NY"]
        }, {
            "carrierId": "CNICO",
            "states": ["NY, PA"],
            "excludedStates": ["CA, MI, OH"]
        }]
    },
   {
    "coverageResponse": "222\/333\/111",
    "insuranceLine": "COMMERCIAL",
    "coverageCategory": "COVERAGE",
    "carriers": [{
        "carrierId": "LMICO",
        "states": "GA, CA",
        "excludedStates": "PA, NY"
    }, {
        "carrierId": "CNICO",
        "states": "NY, PA",
        "excludedStates": "CA, MI, OH"
    }]
}]

What have you tried, and what exactly is the problem with it? — jonrsharpe, Aug 27 '20 at 07:36
I extracted the json data from a dataframe using this: `jsondata = (mydata.groupby(['coverageResponse', 'insuranceLine', 'coverageCategory'], as_index = True).apply(lambda x: x[['carrierId', 'states', 'excludedStates']].to_dict('r')).reset_index().rename(columns = {0: 'carriers'}).to_json(orient = 'records'))` The problem is, the comma separated values need to be in a list. — , Aug 27 '20 at 07:38
So your question boils down to ["how to convert a comma-separated string into a list"](https://stackoverflow.com/questions/7844118/how-to-convert-comma-delimited-string-to-list-in-python)? — trincot, Aug 27 '20 at 07:41
I can use split() for that. But I am not sure how to produce the JSON output where the fields in question are in a list instead of a comma separated string. — , Aug 27 '20 at 07:42
Well, that is what `split` does, no? It produces a list instead of a comma separated string. Combine this with ["loop through all nested dictionary values"](https://stackoverflow.com/questions/10756427/loop-through-all-nested-dictionary-values)? ... and I suppose you know about JSON encoding and decoding with the [json API](https://docs.python.org/3/library/json.html)? — trincot, Aug 27 '20 at 07:43

score 0 · Accepted Answer · answered Aug 27 '20 at 07:47

0

Solution for your problem:

init_json = #your json
for i, external in enumerate(init_json):
   for j, internal in enumerate(external['carriers']): 
      init_json[i]['carriers'][j]['states'] = internal['states'].split(', ')
      init_json[i]['carriers'][j]['excludedStates'] = internal['excludedStates'].split(', ')

answered Aug 27 '20 at 07:47

RoyalGoose

453
9
24

getting NoneType object has no attribute 'split' – Aug 27 '20 at 07:58
@balkotdude check the code https://imgur.com/aafsD4Q – RoyalGoose Aug 27 '20 at 08:00
there are multiple json objects in my data. – Aug 27 '20 at 08:03
1

@balkotdude then provide a full json, because, depending on the degree of nesting, you need to use different constructions. And of course you can stupidly do if ... == nonetype:... – RoyalGoose Aug 27 '20 at 08:04
@balkotdude For me everything works with your new json: https://imgur.com/a/nfS0sZi – RoyalGoose Aug 27 '20 at 08:16
@balkotdude if my answer helped you, please mark it as solution – RoyalGoose Aug 31 '20 at 09:51
1

It did. Marked as solution. – Aug 31 '20 at 13:59

Convert Comma Separated String in Nested JSON to List Python

1 Answers1