Hi,

I currently have a database of all Australian legislators since the 20th century. The data is formated as follows:



+---------------------------------------------------------------------------------------+
_id _firstname _lastname _fromdate _todate
---------------------------------------------------------------------------------------
1. uk.org.publicwhip/member/1 Tony Abbott 1994-03-26 2019-05-18
2. uk.org.publicwhip/member/3 Dick Adams 1993-03-13 2013-09-07
3. uk.org.publicwhip/member/4 Albert Adermann 1984-12-01 1990-02-19
4. uk.org.publicwhip/member/5 Albert Adermann 1972-12-02 1984-12-01
5. uk.org.publicwhip/member/6 Anthony Albanese 1996-03-02 9999-12-31
---------------------------------------------------------------------------------------
6. uk.org.publicwhip/member/7 Kenneth Aldred 1983-05-28 1990-03-24
7. uk.org.publicwhip/member/8 Kenneth Aldred 1990-03-24 1996-01-29
8. uk.org.publicwhip/member/9 Kenneth Aldred 1975-12-13 1980-10-18
9. uk.org.publicwhip/member/10 John Anderson 1989-04-15 2007-10-17
10. uk.org.publicwhip/member/11 Peter Andren 1996-03-02 2007-10-17
---------------------------------------------------------------------------------------


With a variable "_fromdate" indicating when each member entered the House of Representatives for the first time, and a second variable indicating when they left. What I would like to do is to reshape the data into a panel form, with an observation for each representative on each year they served in the house (eg. if they were still a member of the house the 31st of december). Something like:


+---------------------------------------------------------------------------------------+
_id _firstname _lastname year
---------------------------------------------------------------------------------------
1. uk.org.publicwhip/member/1 Tony Abbott 1994
2. uk.org.publicwhip/member/1 Tony Abbott 1995
3. uk.org.publicwhip/member/1 Tony Abbott 1996
4. uk.org.publicwhip/member/1 Tony Abbott 1997
5. uk.org.publicwhip/member/1 Tony Abbott 1998
6. uk.org.publicwhip/member/1 Tony Abbott 1999
7. uk.org.publicwhip/member/1 Tony Abbott 2000
8. uk.org.publicwhip/member/1 Tony Abbott 2001
9. uk.org.publicwhip/member/1 Tony Abbott 2002
---------------------------------------------------------------------------------------


I've tried to look for solutions on previous posts and other internet sources, but I am not even too sure of what to look for, as I don't know what the current form of my database is called.

Thank you very much for your help.
Kind regards,
Matteo Giuliani Pedraza