# Extract data from json format and paste to column using python

In my column with json data, I have this list I want to extract to column:

"list":[

{
"id":"list",
"item":[
{
"value":"Hergestellt in Italien aus 100% reinem Platin-Flüssigsilikon"
},
{
"value":"Geruchs- und geschmacksneutral"
},
{
"value":"Kältebeständig bis -60°C"
},
{
"value":"Inklusive Rezeptbuch und 50 Eisstielen"
},
{
"value":"spülmaschinengeeignet"
}
],
"decorators":[
]
},


I have extracted other data using this code but the value was string and not a dictionary:

if 'item' in item and item['item']:
if isinstance(item['item'], str):
cur_model_info[item['id']] = item['item']
elif isinstance(item['item'], list):
elements = [element['value'] for element in item['item']]
cur_model_info[item['id']] = ','.join(elements)


I tried to use this for the above format of data but I got this error: TypeError: sequence item 0: expected str instance, dict found

What should I change in order to be able to export in a separate column the data having each element from the list in a new cell of that column?

expected output

Using the above code, the data is exported in this format

• Given the above json data, which values do you want to extract and what do you expect as the output? Jan 29, 2021 at 14:52
• @Oxbowerce I want to extract the values after "value":(Hergestellt in Italien aus 100% reinem Platin-Flüssigsilikon and the rest of them) in a column, information present after "value" in a separate cell. Given the above example, I should have a column with information for 5 cells
– Isa
Jan 29, 2021 at 14:54
• And what is the type of the original data? A dictionary or a list? Jan 29, 2021 at 15:04
• do you json-decode the input or do you handle it as string? it is not clear from your post Jan 29, 2021 at 18:10
• @Oxbowerce I use json data and json.loads for handling data with python
– Isa
Jan 29, 2021 at 20:33

Given that the data snippet you provided is not a valid json structure I assume that the data is a list with dictionaries in it. Using this assumption, you can extract the values to a nested list using a list comprehension:

data = [
{
"id":"list",
"item":[
{
"value":"Hergestellt in Italien aus 100% reinem Platin-Flüssigsilikon"
},
{
"value":"Geruchs- und geschmacksneutral"
},
{
"value":"Kältebeständig bis -60°C"
},
{
"value":"Inklusive Rezeptbuch und 50 Eisstielen"
},
{
"value":"spülmaschinengeeignet"
}
],
"decorators":[]
}
]

print([[i["value"] for i in x["item"]] for x in data])

# [['Hergestellt in Italien aus 100% reinem Platin-Flüssigsilikon',
#  'Geruchs- und geschmacksneutral',
#  'Kältebeständig bis -60°C',
#  'Inklusive Rezeptbuch und 50 Eisstielen',
#  'spülmaschinengeeignet']]

• thank you! The data is in json format,I posted just the part that need to be extracted and which is in a dictionary format. My code worked for cases where the value was a string, all that needs to be changed, is the last line so that the values can be exported on rows, meaning this line cur_model_info[item['id']] = ','.join(item['item']) I have changed it to elements = [element['value'] for element in item['item']] and added cur_model_info[item['id']] = ','.join(elements) which, exports the list of values but in one single row.
– Isa
Jan 29, 2021 at 21:41
• So if I understand it correctly the issue is solved? Jan 29, 2021 at 21:49
• unfortunately not, I have modified my code in the description. Is exporting the values not but all in one cell. Do you have any idea on how I should modify the last line of code so that I can export it on multiple rows? From my example of data should be 5 rows, one for each value. Thank you so much for taking your time
– Isa
Jan 29, 2021 at 21:52
• If possible, please provide a full data extract as this makes it easier to debug. When you say cell/row, what exactly do you mean? Cells and rows are not a data structure within native python. Jan 29, 2021 at 21:55
• I have updated the description with the expected format result.
– Isa
Jan 29, 2021 at 22:09