Reorder CSVs prior to S3 upload

[Ben-Hodgkiss]

Overview
Given the changes to using the Config Manager mean that we are generally appending data to the bottom, we should create a new Github Action to sort this data every time we commit to main. This will additionally help with debugging in Github as it will be easier to visually find data.

Each collection/pipeline file will need a bespoke ordering. These are listed below (or will be). We should also ensure that the CSVs for each file have the same header order (i.e. the column CSV for Dataset A doesn't have a different order than the column CSV for Dataset B). We may need to check with @pooleycodes before changing the order of these as it may impact the generation of new lines via the Add Data process. The default ordering for new collections is set in create_collection.py, but we'd have to manually change any existing files.

• collection/endpoint.csv - Sort by entry-date (asc), then endpoint (asc)
• collection/old-resource.csv - No sorting necessary
• collection/source.csv - Sort by entry-date (asc), then endpoint (asc)
• pipeline/column.csv - Sort by dataset, then endpoint, then resource, then field
• pipeline/combine.csv - Sort by dataset, then endpoint, then field
• pipeline/concat.csv - Sort by dataset, then endpoint, then resource, then field
• pipeline/convert.csv - No sorting necessary - may be deleted following Convert.CSV research #2326
• pipeline/default-value.csv - Sort by dataset, then field
• pipeline/default.csv - Sort by dataset, then field, then default-field
• pipeline/entity-organisation.csv - Sort by dataset, then organisation, then entity-minimum
• pipeline/expect.csv - Sort by dataset, then operation, then organisations
• pipeline/filter.csv - Sort by dataset, then endpoint, then field
• pipeline/lookup.csv - Sort by prefix then entity
• pipeline/old-entity.csv - Sort by old-entity
• pipeline/patch.csv - Sort by dataset, then endpoint, then field
• pipeline/skip.csv - Sort by dataset, then endpoint, then pattern
• pipeline/transform.csv - Sort by dataset, then replacement-field

[sianteesdale]

Four parts to this ticket:

Column reordering

• Reorder the columns across all datasets. Use A4D as the point of truth.
• Edit create_collection.py so that future datasets also have the same consistent column order

Row reordering

• Go through the list in initial comment above and execute those changes

Standardise file endings

• Make all the CSVs file endings be CRLF

Make the row reordering run every night

• Add these rules to a script to either: a) run as a one off Github workflow; b) run every night e.g. at 10:30pm before S3 action; c) add to S3 action; d) follow what specification repo does where a push to main triggers the action

[sianteesdale]

Column reordering is now in a PR: #2445

Waiting until Tuesday for Ben/Swati to review - hopefully DLB will be sorted by then so we can merge into main.

I will do row reordering in a separate PR next week.

[sianteesdale]

Reorder config columns PR merged into main now. Will see how the pipelines perform tonight with the changes.

The row reordering (following the rules in this ticket and for line endings) is in this PR: #2447
We will wait until tomorrow to merge this into main, so we can monitor the column reordering tonight.

[sianteesdale]

Spoken to Ben - we decided to push the row reordering PR into main today, so that the pipeline does a full re-do of all collections for only one night (instead of across two nights).

I triggered upload to S3 and tested on central-activities-zone collection, and that ran without any hitches. So 🤞 the pipeline runs well tonight with the row and column reordering.

[sianteesdale]

Reordering rows and columns raised no alerts in Airflow last night.

Final PR for this ticket will add my standardising rows script into a GitHub Workflow to be run every night: #2451