Convert time series to panel data

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.

Answer 1

[Santosh Fatale]

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".

https://www.mathworks.com/help/matlab/matlab_prog/add-and-delete-table-variables.html

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