Convert time series to panel data

15 views (last 30 days)
alphabetagamma
alphabetagamma on 30 Jul 2022
Answered: Santosh Fatale on 4 Aug 2022
Here’s an overview of the dataset. The excel spreadsheet consists of several sheets. Each sheet has yearly time series data on one variable for several states across time from 1945 till 1978. For example, sheet1 has data on var1. The columns indicate the states and the rows correspond to the time periods. I’ve attached a hypothetical data for your reference which has 3 variables – var1, var2, var3
How can I rearrange (or stack) the dataset that is compatible to perform panel regression analysis? Before estimating a fixed effects model, I have to group the states in 3 categories
Group1: states MA, CA, NY
Group2: states, TX, WY
Group3: states, WA, TN, RI
Since there are three sheets in the excel spradsheet, perhaps a for loop might work?
So far, I've been manually rearranging the columns in excel, but that is very tedious especially if there are a large number of columns. Is there a way we can implement this via code by writing a function or something else? If you wouldn’t mind providing a sample code, I would really appreciate it. Thanks a ton.

Answers (1)

Santosh Fatale
Santosh Fatale on 4 Aug 2022
Hi Sudiksha,
I understand that you want to preprocess entries in table for Panel Regression Analysis. You can do so by using inbuilt MATLAB function and storing the processed table using "writetable" function.
For performing pre-processing on columns, please check below link about "Add, Delete, and Rearrange Table Variables".
Please refere the documentation on readtable and writetable for more details about reading "xlsx" file and storing table as "xlsx" file.
Santosh
MathWorks technical Support

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by