At 3.34.1, I have a Geopackage attribute table containing a single text column with the following form:
Alaska_2021_05_01_to_2021_05_03
Hawaii_2022_11_15_to_2022_11_16
As you can see, each record contains three parts: location, starting date, ending date.
I need to output this to a .csv table, containing two columns, as follows (columns are separated here for clarity):
column1, column2
Alaska_2021_05_01_to_2021_05_03, 2021_05_01
Alaska_2021_05_01_to_2021_05_03, 2021_05_02
Alaska_2021_05_01_to_2021_05_03, 2021_05_03
Hawaii_2022_11_15_to_2022_11_16, 2022_11_15
Hawaii_2022_11_15_to_2022_11_16, 2022_11_16
Here,
- There are as many records as there are elapsed days in each input record.
- The first column is simply a repeat of the input table's value.
- The second column lists the date sequence, from starting to ending date.
Using QGIS date expressions, I am able to determine the number of elapsed days, which I presume could be used to calculate the number of output records. However, that is the limit of my expressions knowledge. A review of the QGIS docs, plus Youtube, did not provide help.
How do I output a table containing the reformatted text as shown?
EDIT:
I modified @Ben W's Python code to accommodate location names containing one or more underscores (such as New_York), as follows:
- I commented out his line:
location = attr.split('_')[0] - and replaced it with the following two lines:
index_position = (attr.index('_to_')- 11)
location = attr[0:index_position]
Here’s how the two lines work:
- As each input record is iterated through, the first code line finds the numeric index position of the string ‘_to_’ (ignore apotrophes). Because this string is in every input line, and is the only consistently unique string for every record, it serves as an identifying reference. The index value is then reduced by 11 to account for the preceeding string ‘_yyyy_MM_dd’. The reduced value, stored in the variable index_position, represents the ending position of the location name.
- The second line of code left-trims the input (attr) by selecting the number of characters derived from index_position, and outputs the selected text (such as New_York) to the variable location, which is then sent to the output file. Location can now contain one or more underscores.