Summing over observations in unbalanced panel data
40 views (last 30 days)
Show older comments
Hello, I have an unbalanced panel data set. For each id, I'd like to sum all values of x up to the latest time I observe that id, and record the summation to a new variable. How can I do this? Ideally, I'd like to avoid looping as I have a large dataset and I try to speed up the process.
Thank you in advance!
Selcen
2 Comments
the cyclist
on 16 Aug 2024 at 23:01
Can you upload the data, or a small representative sample? You can use the paper clip icon in the INSERT section of the toolbar.
dpb
on 17 Aug 2024 at 15:05
As @the cyclist notes, w/o a sample dataset we're pretty-much without recourse to a direct reponse, but look at rowfun and/or splitapply
Answers (1)
Shishir Reddy
on 20 Aug 2024 at 7:56
Edited: Shishir Reddy
on 20 Aug 2024 at 7:56
Hi Selcen
As per my understanding you would like to sum all the values of a specific variable till the latest occurrence of that variable in an unbalanced panel data set and record the summation to a new variable.
Assuming that the data is in a MATLAB table format with at least 3 columns ‘id’, ‘time’, and ‘x’, the following is a sample MATLAB code to achieve this.
% Sample unsorted data
data = table([2; 1; 3; 2; 1; 3; 1], [1; 3; 2; 2; 1; 1; 2], [5; 20; 30; 15; 10; 25; 35], 'VariableNames', {'id', 'time', 'x'});
data = sortrows(data, {'id', 'time'}); %Sort the table by 'id' and 'time'
[~, idx] = unique(data.id, 'last'); %Find the maximum time for each 'id'
data.cumSumX = cumsum(data.x); %Calculate the cumulative sum of 'x' for each 'id'
latestCumulativeSum = data.cumSumX(idx); %Extract the cumulative sum at the latest time for each 'id'
result = table(data.id(idx), latestCumulativeSum, 'VariableNames', {'id', 'LatestCumSumX'});
% Display the result
disp(result);
For more information regarding the ‘cumSum’ function kindly refer the following documentation https://www.mathworks.com/help/matlab/ref/cumsum.html
I hope this helps.
0 Comments
See Also
Categories
Find more on Axis Labels in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!