I'd approach this by making the field tuple a bit more complex. It seems that the first records are raw data and the last few are field values. You can just break it into two mappings like this:
# First Dictionary is direct data writes, second is field values to pull from the Origin dataset
conversion_map: list[tuple[dict[str, Any], dict[str, Any]]] = [
(
{'Plot_': 'Macroplot', 'SurveyType': 'Pre', 'Transect': '1'},
{'OneHour': 'onehr_pre_1', 'TenHr': 'tenhr_pre_1', 'HunHr': 'hundhr_pre_1'}
),
(
{'Plot_': 'Macroplot', 'SurveyType': 'Pre', 'Transect': '2'},
{'OneHour': 'onehr_pre_2', 'TenHr': 'tenhr_pre_2', 'HunHr': 'hundhr_pre_2'}
),
(
{'Plot_': 'Macroplot', 'SurveyType': 'Post', 'Transect':'1'},
{'OneHour': 'onehr_post_1', 'TenHr': 'tenhr_post_1', 'HunHr': 'hundhr_post_1'}
),
(
{'Plot_': 'Macroplot', 'SurveyType': 'Post', 'Transect':'2'},
{'OneHour': 'onehr_post_2', 'TenHr': 'tenhr_post_2', 'HunHr': 'hundhr_post_2'}
),
]
That will make it clearer later what the raw data mapping is and what the extracted field values are.
When dealing with multiple cursors, I tend to like mapping the rows instead of using an index. This can be done with this incredibly simple helper function:
def as_dict(cur: SearchCursor) -> Iterator[dict[str, Any]]:
for row in cur:
yield dict(zip(cur.fields, row))
You can wrap a cursor with this before iterating and you'll get back a dictionary that can be updated
Now we need to implement the mapping:
def convert(origin: str, target: str, fields: list[str], conversion_map: ConversionMap, null_value: Any=None):
with (
SearchCursor(origin, fields) as s_cur,
InsertCursor(target, fields) as i_cur
):
for row in as_dict(s_cur):
for fuel_data, fuel_fields in conversion_map:
new_row = fuel_data.copy()
new_row.update({k: row.get(v, null_value) for k, v in fuel_fields.items()})
i_cur.insertRow([new_row.get(f, null_value) for f in i_cur.fields])
Here we create a context block with two cursors, one to search through the origin table ("Forest_Surface_Fuels") and one to insert into the target table ("1_10_100").
- (line: 2) Create a context block with 2 cursors for the Target and Origin
- (line: 6) Iterate dictionary mappings of the origin rows
- (line: 7) Iterate all defined row conversions
- (line: 8 ) Create a copy of the template row data
- (line: 9) Get the field value of the current origin row for each mapped target field
- (line: 10) Create a row in the correct shape by iterating the insert fields and grabbing the new_row values
The null_value here shouldn't ever trigger, since it'll only exist if the fieldname doesn't exist in the origin table. If you don't have any 0 values, you can re-map nulls by using an or instead:
new_row.update({k: row.get(v) or null_value for k, v in fuel_fields.items()})
This abuses pythons truthiness, so any value that's considered falsey will get the null_value ('', 0, False, None, etc.)
Finally, we need to put all this together into a script that doesn't pollute the global namespace with cursor references. Basically just put your logic into an if __name__ == '__main__' block:
from collections.abc import Iterator
from typing import Any
from arcpy.da import SearchCursor, InsertCursor
ConversionMap = list[tuple[dict[str, Any], dict[str, Any]]]
def as_dict(cur: SearchCursor) -> Iterator[dict[str, Any]]:
for row in cur:
yield dict(zip(cur.fields, row))
def convert(origin: str, target: str, fields: list[str], conversion_map: ConversionMap, null_value: Any=None):
with (
SearchCursor(origin, fields) as s_cur,
InsertCursor(target, fields) as i_cur
):
for row in as_dict(s_cur):
for fuel_data, fuel_fields in conversion_map:
new_row = fuel_data.copy()
new_row.update({k: row.get(v, null_value) for k, v in fuel_fields.items()})
i_cur.insertRow([new_row.get(f, null_value) for f in i_cur.fields])
if __name__ == '__main__':
# Fields that data will be inserted into in the final excel table
FIELDS = ['Plot_', 'SurveyType', 'Transect', 'OneHour', 'TenHr', 'HunHr']
ORIGIN_DATA = "Forest_Surface_Fuels"
TARGET_DATA = "1_10_100"
# Update this with the Null Value you want to use, e.g. ['' or -1 or None]
NULL_VALUE = None
# First Dictionary is direct data writes, second is field values to pull from the Origin dataset
CONVERSION_MAP: ConversionMap = [
(
# Direct Data Insertion
{'Plot_': 'Macroplot', 'SurveyType': 'Pre', 'Transect': '1'},
# Field Value Map from Origin
{'OneHour': 'onehr_pre_1', 'TenHr': 'tenhr_pre_1', 'HunHr': 'hundhr_pre_1'}
),
(
{'Plot_': 'Macroplot', 'SurveyType': 'Pre', 'Transect': '2'},
{'OneHour': 'onehr_pre_2', 'TenHr': 'tenhr_pre_2', 'HunHr': 'hundhr_pre_2'}
),
(
{'Plot_': 'Macroplot', 'SurveyType': 'Post', 'Transect':'1'},
{'OneHour': 'onehr_post_1', 'TenHr': 'tenhr_post_1', 'HunHr': 'hundhr_post_1'}
),
(
{'Plot_': 'Macroplot', 'SurveyType': 'Post', 'Transect':'2'},
{'OneHour': 'onehr_post_2', 'TenHr': 'tenhr_post_2', 'HunHr': 'hundhr_post_2'}
),
]
convert(ORIGIN_DATA, TARGET_DATA, FIELDS, CONVERSION_MAP, NULL_VALUE)
