TypeError: Object of type dict_values is not JSON serializable

The real issue here is the general format you're putting that dict in to begin with. You end up nesting multiple lists unnecessarily, when all you need is one big list of county attributes.

The response.json()["features"] is a list of attributes. Carlow, for example, returns a list of 407 attributes for that county. So in your thisdict you end up with a dict of county keys the values of which are all list of attributes.

Then, you try to take the dict_values of that dict, which (if you cast dict_values as a list) yields an unnecessary list of lists of attributes.

This actually works, in terms of (de)serializing into/out of JSON, e.g. this does not throw a serialization exception:

toJSON = json.dumps(list(thisdict.values()))
data = json.loads(toJSON)

However, you will run into problems later when you try to pass this list of lists of dicts to pandas.json_normalize():

Traceback (most recent call last):
  File "/home/dephekt/pandas/main.py", line 25, in <module>
    df = pd.json_normalize(data)
  File "/home/dephekt/pandas/.venv/lib/python3.8/site-packages/pandas/io/json/_normalize.py", line 270, in _json_normalize
    if any([isinstance(x, dict) for x in y.values()] for y in data):
  File "/home/dephekt/pandas/.venv/lib/python3.8/site-packages/pandas/io/json/_normalize.py", line 270, in <genexpr>
    if any([isinstance(x, dict) for x in y.values()] for y in data):
AttributeError: 'list' object has no attribute 'values'

You can see in the traceback it's expecting that data is a list of dicts or a dict. It takes for y in data and then tries to call y.values(), assuming y was a dictionary, but since in this case it's a list nested in a list and a list has no values() method, it throws an AttributeError.

Consider the following code instead:

import json
import logging

import pandas as pd

import requests

logging.basicConfig(level=logging.DEBUG)

counties = [
    "Carlow",
    "Cavan",
    "Clare",
    "Cork",
    "Donegal",
    "Dublin",
    "Galway",
    "Kerry",
    "Kildare",
    "Kilkenny",
    "Laois",
    "Leitrim",
    "Limerick",
    "Longford",
    "Louth",
    "Mayo",
    "Meath",
    "Monaghan",
    "Offaly",
    "Roscommon",
    "Sligo",
    "Tipperary",
    "Waterford",
    "Westmeath",
    "Wexford",
    "Wicklow",
]

results = []
s = requests.Session()

for county in counties:
    url = f"https://services1.arcgis.com/eNO7HHeQ3rUcBllm/arcgis/rest/services/Covid19CountyStatisticsHPSCIreland/FeatureServer/0/query"
    fields = "CountyName,PopulationCensus16,TimeStamp,ConfirmedCovidCases,PopulationProportionCovidCases,ConfirmedCovidDeaths,ConfirmedCovidRecovered"
    params = {
        "where": f"CountyName='{county}'",
        "outFields": fields,
        "returnGeometry": False,
        "outSR": 4326,
        "f": "json",
    }
    response = s.get(url, params=params, timeout=10)
    response.raise_for_status()

    features = response.json().get("features")

    for feature in features:
        results.append(feature)

with open("sample.json", "w") as outfile:
    outfile.write(json.dumps(results))

df = pd.json_normalize(results)
print(df.head(10))

CD = df[["attributes.CountyName", "attributes.TimeStamp", "attributes.ConfirmedCovidCases"]]

print(CD)

What I do here is I make a requests session (see below for more on that) and then for each county's response, we get the list of attributes from the features key, then iterate on that list and put the individual attributes into our results list.

What you end up with is one big list of all the attribute dicts for all the counties.

Then, we avoid some unnecessary gymnastics where you were doing json.dumps() just to then json.loads() it back into a format you already had, e.g.:

toJSON = json.dumps(thisdict)
data = json.loads(toJSON)

Here, data ends up being identical to thisdict, there's no reason to deserialize it back into a dict. You only needed to dump the results to write them to the file. For your pandas arguments you can just pass thisdict (or in my example, results which is a list).

This all ends up with this as output:

  attributes.CountyName  ...  attributes.ConfirmedCovidRecovered
0                Carlow  ...                                None
1                Carlow  ...                                None
2                Carlow  ...                                None
3                Carlow  ...                                None
4                Carlow  ...                                None
5                Carlow  ...                                None
6                Carlow  ...                                None
7                Carlow  ...                                None
8                Carlow  ...                                None
9                Carlow  ...                                None

[10 rows x 7 columns]

      attributes.CountyName  ...  attributes.ConfirmedCovidCases
0                    Carlow  ...                               0
1                    Carlow  ...                               0
2                    Carlow  ...                               0
3                    Carlow  ...                               0
4                    Carlow  ...                               0
...                     ...  ...                             ...
10577               Wicklow  ...                            4460
10578               Wicklow  ...                            4473
10579               Wicklow  ...                            4483
10580               Wicklow  ...                            4491
10581               Wicklow  ...                            4497

[10582 rows x 3 columns]

You can play around more with the format before you ship it to your pandas dataframe if it's not exactly what you want, but hopefully this gives you a good example to work from.

Since you're making a bunch of requests in a loop, I've created a requests.Session() object because that leverages urllib3's connection pooling and reuses the same underlying TCP connection for each GET request, which has better performance. This way you're not renegotiating a new TCP connection for every loop execution. It makes one connection and sends all 26 requests over that, not 26 TCP connections each sending one tiny request.

I also split up your request query parameters from the actual URL endpoint, just for my own sanity. You can do the URL the way you were doing it, but this does give you a little more flexibility in the event you need to alter the value of those parameters. It will work just as well with your long URL, I just found it unwieldy.