axo | Dynamic SSR GUI for Illustrator

I am working with a dataset that has the corresponding year attached to variable names as suffix, e.g. AXOX1991, where AXO is the variable. I am trying to separate the year from the variable label/column names to generate a year column so that the dataset can be analyzed as time-series data.

In other words, the existing dataset looks like:

Country AXOX1991 AXOX1992 BXOX1991 BXOX1992 CXOX1991 CXOX1992 Afghanistan 1 2 3 4 5 6 USA 6 5 4 3 2 1

And I am trying to create the following:

Country Year AXO BXO CXO Afghanistan 1991 1 3 5 Afghanistan 1992 2 4 6 USA 1991 6 4 2 USA 1992 5 3 1

As you can see, X not only acts as the delimiter that divides the variable name and the year, but it is also part of the variable name. Is there any way in R to separate the year from the variable name in existing column names and then to create a year column as shown above?

I have been thinking of workarounds, such as loops, but I haven't gotten very far, and I'm truly stumped. I have more than 900 variable-years, so I want to avoid doing it by hand if possible.

Thank you!

...