Sort JSON dictionaries using datetime format not consistent
Question: I have JSON file (post responses from an API) - I need to sort the
dictionaries by a certain key in order to parse the JSON file in chronological
order. After studying the data, I can sort it by the date format in metadata
or by the number sequences of the S5CV[0156]P0.xml

One text example that you can load in JSON here -
<http://pastebin.com/0NS5BiDk>

I have written 2 codes to sort the list of objects by a certain key. The 1st
one sorts by the 'text' of the xml. The 2nd one by [metadata][0][value].

The 1st one works, but a few of the XMLs, even if they are higher in number,
actually have documents inside older than I expected.

For the 2nd code the format of date is not consistent and sometimes the value
is not present at all. I am struggling to extract the datetime format in a
consistent way. The second one also gives me an error, but I cannot figure out
why - string indices must be integers.

    
    
    # 1st code (it works but not ideal)
    
    # load post response r1 in json (python 3.5)
    j=r1.json() 
    
    # iterate through dictionaries and sort by the 4 num of xml (ex. 0156)
    
    list = []
    for row in j["tree"]["children"][0]["children"]:
        list.append(row)
    
    newlist = sorted(list, key=lambda k: k['text'][-9:])
    print(newlist)
    
    
    # 2nd code. I need something to make consistent datetime,
    # except missing values and solve the list index error
    
    list = []
    for row in j["tree"]["children"][0]["children"]:
        list.append(row)
    
    # extract the last 3 blocks of characters from the [metadata][0][value]
    # usually are like this "7th april, 1922." and trasform in datatime format
    # using dparser.parse 
    
    def date(key):
        return dparser.parse((' '.join(key.split(' ')[-3:])),fuzzy=True)
    
    def order(slist):
        try:
            return sorted(slist, key=lambda k: k[date(["metadata"][0]["value"])])
    
        except ValueError:
            return 0
    
    print(order(list))
    
    
    #update
        orig_list = j["tree"]["children"][0]["children"]
    
    
        cleaned_list = sorted((x for x in orig_list if extract_date(x) != DEFAULT_DATE),
                              key=extract_date)    
    
    
        first_date = extract_date(cleaned_list[0])
        if first_date != DEFAULT_DATE:  # valid date found?
                cleaned_list [0] ['date'] = first_date
        print(first_date)
    
        middle_date = extract_date(cleaned_list[len(cleaned_list)//2])
        if middle_date != DEFAULT_DATE:  # valid date found?
                cleaned_list [0] ['date'] = middle_date
        print(middle_date)
    
        last_date = extract_date(cleaned_list [-1])
        if last_date != DEFAULT_DATE:  # valid date found?
                cleaned_list [0] ['date'] = last_date
        print(last_date)      
    


Answer: Clearly you can't use the .xml filenames to sort the data if it's unreliable,
so the most promising strategy seems to be what you're attempting to do in the
2nd code.

When I mentioned needing a datetime to sort the items in my comments to your
other question, I literally meant something like
[`datetime.date`](https://docs.python.org/3/library/datetime.html#date-
objects) instances, not strings like `"28th july, 1933"`, which wouldn't
provide the proper ordering needed since they would be compared
lexicographically with one another, not numerically like `datetime.date`s.

Here's something that seems to work. It uses the `re` module to search for the
date pattern in the strings that usually contain them (those with a `"name"`
associated with the value `"Comprising period from"`). If there's more than
one date match in the string, it uses the last one. This is then converted
into a `date` instance and returned as the value to key on.

Since some of the items don't have valid date strings, a default one is
substituted for sorting purposes. In the code below, a earliest valid date is
used as the default—which makes all items with date problems appear at the
beginning of the sorted list. Any items following them should be in the proper
order.

Not sure what you should do about items lacking date information—if it isn't
there, your only options are to guess a value, ignore them, or consider it an
error.

    
    
    # v3.2.1
    import datetime
    import json
    import re
    
    # default date when one isn't found
    DEFAULT_DATE = datetime.date(1, 1, datetime.MINYEAR)  # 01/01/0001
    MONTHS = ('january february march april may june july august september october'
              ' november december'.split())
    # dictionary to map month names to numeric values 1-12
    MONTH_TO_ORDINAL = dict( zip(MONTHS, range(1, 13)) )
    DMY_DATE_REGEX = (r'(3[01]|[12][0-9]|[1-9])\s*(?:st|nd|rd|th)?\s*'
                    + r'(' + '|'.join(MONTHS) + ')(?:[,.])*\s*'
                    + r'([0-9]{4})')
    MDY_DATE_REGEX = (r'(' + '|'.join(MONTHS) + ')\s+'
                    + r'(3[01]|[12][0-9]|[1-9])\s*(?:st|nd|rd|th)?,\s*'
                    + r'([0-9]{4})')
    DMY_DATE = re.compile(DMY_DATE_REGEX, re.IGNORECASE)
    MDY_DATE = re.compile(MDY_DATE_REGEX, re.IGNORECASE)
    
    def extract_date(item):
        metadata0 = item["metadata"][0]  # check only first item in metadata list
        if metadata0.get("name") != "Comprising period from":
            return DEFAULT_DATE
        else:
            value = metadata0.get("value", "")
            matches = DMY_DATE.findall(value)  # try dmy pattern (most common)
            if matches:
                day, month, year = matches[-1]  # use last match if more than one
            else:
                matches = MDY_DATE.findall(value)  # try mdy pattern...
                if matches:
                    month, day, year = matches[-1]  # use last match if more than one
                else:
                    print('warning: date patterns not found in "{}"'.format(value))
                    return DEFAULT_DATE
    
            # convert strings found into numerical values
            year, month, day = int(year), MONTH_TO_ORDINAL[month.lower()], int(day)
            return datetime.date(year, month, day)
    
    # test files: 'json_sample.txt', 'india_congress.txt', 'olympic_games.txt'
    with open('json_sample.txt', 'r') as f:
        j = json.load(f)
    
    orig_list = j["tree"]["children"][0]["children"]
    sorted_list = sorted(orig_list, key=extract_date)
    for item in sorted_list:
        print(json.dumps(item, indent=4))
    

To answer your latest follow-on questions, you could leave out all the items
in the list that don't have recognizable dates by using `extract_date()` to
filter them out beforehand in a [generator
expression](https://docs.python.org/3/howto/functional.html#generator-
expressions-and-list-comprehensions) with something like this:

    
    
    # to obtain a list containing only entries with a parsable date
    cleaned_list = sorted((x for x in orig_list if extract_date(x) != DEFAULT_DATE),
                          key=extract_date)
    

Once you have a sorted list of items that all have a valid date, you can do
things like the following, again reusing the `extract_date()` function:

    
    
    # extract and display dates of items in cleaned list
    print('first date: {}'.format(extract_date(cleaned_list[0])))
    print('middle date: {}'.format(extract_date(cleaned_list[len(cleaned_list)//2])))
    print('last date: {}'.format(extract_date(cleaned_list[-1])))
    

Calling `extract_date()` on the same item multiple times is somewhat
inefficient. To avoid that you could easily add the `datetime.date` value it
returns to the object on-the-fly since it's a dictionary, and then just refer
to it as often as needed with very little additional overhead:

    
    
    # add extracted datetime.date entry to a list item[i] if a valid one was found
    date = extract_date(some_list[i])
    if date != DEFAULT_DATE:  # valid date found?
        some_list[i]['date'] = date  # save by adding it to object
    

This effectively caches the extracted date by storing it in the item itself.
Afterwards, the `datetime.date` value can simply be referenced with
`some_list[i]['date']`.

As a concrete example, consider this revised example of displaying the datesof
the first, middle, and last objects:

    
    
    # display dates of items in cleaned list
    print('first date: {}'.format(cleaned_list[0]['date']))
    middle = len(cleaned_list)//2
    print('middle date: {}'.format(cleaned_list[middle]['date']))
    print('last date: {}'.format(cleaned_list[-1]['date']))
    

