how to extract python data frame from JSON string
Question: I've got the following JSON string in a txt file and I'm trying to extract a
data frame from the 'visualLogs' variable. I can read the JSON string in and I
can access the visualLogs list, but I have failed all day long to convert this
into a 9 column data frame of floating point numbers

    
    
    {
      "visualScore" : 0,
      "selfReportingResults" : 5,
      "voiceScore" : "No Data",
      "selfReportScore" : 0,
      "subject" : "Baseline for patient: 108",
      "email" : "steven.vannoy@gmail.com",
      "visualLogs" : [
        "time,anger,contempt,disgust,engagement,joy,sadness,surprise,valence\r22.61086,0.00633,0.19347,0.56258,0.18005,0.00223,0.0165,0.31969,0.0\r22.81096,0.00478,0.19439,0.45847,0.09747,0.00188,0.02188,0.22043,0.0\r"
      ],
      "askedQuestions" : [
        "What is your name?",
        "How old are you?",
        "What tim is it?"
      ],
      "voiceCompleteResults" : {
        "status" : "fail"
      }
    }
    
    with open(f4lJasonFileName) as data_file:
        feelDat = json.load(data_file)
    
    x = feelDat['visualLogs'][0] # Ultimately there will be more than one of these
    

All of my attempts to convert x to a data frame have failed. I've achieved
getting a 1 column data frame of text values, but that's not what I need.

I've replaced those '\r' characters with commas, which ends up getting the one
column text data frame, but I want 9 columns with the labels and then rows of
floating points.


Answer: Once you have loaded the json, you need to split on \r then on the commas:

    
    
    import  pandas as pd
    
    spl = d["visualLogs"][0].split("\r")
    
    
    df = pd.DataFrame([v for v in map(lambda x: x.split(","), spl[1:]) if v[0]], columns=spl[0].split(","))
    

Probably easier to understand broken into parts:

    
    
    import pandas as pd
    
    # split into lines creating an iterator so we don't have to slice.
    spl = iter(d["visualLogs"][0].rstrip().split("\r"))
    
    # split first line to get the  column names.
    columns = next(spl).split(",")
    
    # split remaining lines into individual rows, removing empty row.
    rows = [v for v in (sub_str.split(",") for sub_str in spl) if len(v) > 1]
    
    df = pd.DataFrame(data=rows, columns=columns)
    

We could also just `spl = iter(d["visualLogs"][0].split())` as there is no
other whitespace.

Or use _read_csv_ using a _StringIO_ object:

    
    
    import pandas as pd
    spl = d["visualLogs"][0]
    
    from io import StringIO
    df = pd.read_csv(StringIO(spl))
    

Which gives you:

    
    
          time    anger  contempt  disgust  engagement      joy  sadness  \
    0  22.61086  0.00633   0.19347  0.56258     0.18005  0.00223  0.01650   
    1  22.81096  0.00478   0.19439  0.45847     0.09747  0.00188  0.02188   
    
       surprise  valence  
    0   0.31969        0  
    1   0.22043        0  
    

