This sounds like a great use case for a few lines of petl: https://petl.readthedocs.io/en/stable/

Petl makes it very ergonomic to write ETL scripts like what you’re describing. Take a look at the regex.search method and the fieldmap method.