pandas - Remove carriage return and newline feeds within a list of dictionaries - Python

Question

I have a JSON file which I am reading into my Python script, flattening and then exporting it as a CSV.

My problem is that I noticed there are various carriage returns and newline feeds within the JSON file so it's messing up the whole structure of the CSV.

Updated Current Code:

from pymongo import MongoClient
import pandas as pd
from azure.storage.filedatalake import DataLakeServiceClient
from azure.core._match_conditions import MatchConditions
from azure.storage.filedatalake._models import ContentSettings
from pandas import json_normalize
from datetime import datetime, timedelta
import numpy as np

mongo_client = MongoClient("XXXX") 
db = mongo_client.scaling
table = db.planning
document = table.find()
docs = list(document)
docs = json_normalize(docs) 
docs['pressure'] = docs['pressure'].str.strip().str.replace(" 
","")
docs.to_csv("planning.csv", sep = ",",index=False) 

I'm getting the following error:

Traceback (most recent call last):
  File "XXXXV2.py", line 16, in <module>
    docs['pressureLevels'] = docs['pressureLevels'].str.strip().str.replace(" 
","")
  File "XXXX.venvlibsite-packagespandascoregeneric.py", line 5456, in __getattr__
    return object.__getattribute__(self, name)
  File "XXXX.venvlibsite-packagespandascoreaccessor.py", line 180, in __get__
    accessor_obj = self._accessor(obj)
  File "XXXX.venvlibsite-packagespandascorestringsaccessor.py", line 154, in __init__
    self._inferred_dtype = self._validate(data)
  File "XXXX.venvlibsite-packagespandascorestringsaccessor.py", line 218, in _validate
    raise AttributeError("Can only use .str accessor with string values!")
AttributeError: Can only use .str accessor with string values!

How do I get rid of the carriage returns, newline feeds when there's an integer present in the dictionary?

Any help will be appreciated.

question from:https://stackoverflow.com/questions/65846852/remove-carriage-return-and-newline-feeds-within-a-list-of-dictionaries-python

Answer 1

You are getting the error since you are trying to use strip with int object.

Try this:

for i in docs:
    x = {}
    for k, v in i.items():
        if type(v) == str:
            x[k.strip()] = v.strip().replace("
","")
        else:
            x[k.strip()] = v
    docs2.append(x)