Plot data from a table that has years in the rows and months in the columns

I have a dataset in an xlsx file that contains values of Oceanic Nino Index for each month between 1950 and present. The way it's arranged in this file is the years (1950-2021) are in the rows, and the 12 months are in the columns (treat DJF as January, JFM as February, etc.). So each cell of the table has a value for a given month in a given year. See the attachment.

I am trying to make a plot in MATLAB Online where the Oceanic Nino Index is on the y axis and the date (each month of each year) is on the x axis (something like this), but I'm not sure how to plot this when my table is formatted the way it is. Is there a method for making plots when your data is formatted like this, or alternatively is there a method to easily reformat the table to make it easier to plot (i.e. having only two columns, one with year & month and the other with the Oceanic Nino Index value)? Thank you so much.

Answer 1

[Scott MacKenzie]

Here's an alternative approach that gives a flattened 2D view plus red and blue fills for + and - portions of the graph, as per the example:

T = readtable('https://www.mathworks.com/matlabcentral/answers/uploaded_files/608535/NOAA_CPC_OceanicNinoIndex.xlsx');

yr = T.Year;
y = table2array(T(:,2:end));
y = mean(y,2);
x = 1:size(y);

n = 1000; % interpolate to get smoother +/- transitions
x9 = linspace(1,length(x),n);
y9 = interp1(x,y,x9);

indexPos = y9 .* (y9 >= 0);
indexNeg = y9 .* (y9 <= 0);
area(x9, indexPos, 'facecolor', 'r');
hold on;
area(x9, indexNeg, 'facecolor', 'b');

ax = gca;
ax.XLim = [1 size(yr,1)];
ax.XTick = 1:5:size(yr);
ax.XTickLabels = yr([1:5:size(yr)]);

ax.XLabel.String = 'Year';
ax.XLabel.FontSize = 12;

ax.YLabel.String = 'Oceanic Nino Index';
ax.YLabel.FontSize = 12;

f = gcf;
f.Color = 'w';
f.Units = 'normalized';
f.Position = [.1 .2 .8 .6];

[Maren Hale]

Thank you so much Scott!

[Scott MacKenzie]

You're welcome. Very interesting research. Good luck.

[Scott MacKenzie]

Maren: Just to let you know, I made a slight change to the code in my solution. I'm now using the area function instead of fill. This approach is simpler. The output is exactly the same, however.

[Maren Hale]

Got it, thanks for the update.

Answer 2

[Eric Sofen]

Here's another way to tackle this, sticking with using table to contain your data.

t = readtable('NOAA_CPC_OceanicNinoIndex.xlsx');

% rename the month varnames. just remember that the month represents the
% middle of the 3-month period
t.Properties.VariableNames(2:end) = string(1:12);

% Use stack to go from a "wide" table to a tall time series.
t = stack(t,2:width(t),"NewDataVariableName","Index","IndexVariableName","Month");
t.Time = datetime(t.Year,double(string(t.Month)),ones(size(t.Year)));
plot(t.Time,t.Index)
ylabel("ENSO Index")

% If you wanted to shift to the start of the 3 month period, do
t.Time = t.Time-calmonths(1);
plot(t.Time,t.Index)

I do like Scott's flying carpet plot as well.

[Maren Hale]

Thank you for this alternate approach Eric!

[Gabriel]

excelente tu alternativa

Answer 3

[Scott MacKenzie]

This might be a reasonable start:

T = readtable('https://www.mathworks.com/matlabcentral/answers/uploaded_files/608535/NOAA_CPC_OceanicNinoIndex.xlsx');

yr = T.Year;
z = table2array(T(:,2:end));

y = 1:size(z,1); 
x = 1:size(z,2);

yr = flip(yr); % oldest to newest
z = flip(z,1);

surf(x,y,z);
ax = gca;
ax.ZLim = [-3 3];

ax.XLabel.String = 'Month';
ax.XLabel.FontSize = 12;

ax.YLabel.String = 'Year';
ax.YLabel.FontSize = 12;

ax.ZLabel.String = 'Oceanic Nino Index';

ax.XTick = 1:12;
ax.XTickLabels = { 'Jan' 'Feb' 'Mar' 'Apr' 'May' 'Jun' 'Jul' 'Aug', 'Sep', 'Oct' 'Nov' 'Dec'};

ax.YTick = 1:5:size(yr);
ax.YTickLabels = yr([1:5:size(yr)]);

ax.ZTick = -3:3;

f = gcf;
f.Color = 'w';
f.Units = 'normalized'
f.Position = [.1 .5 .8 .4];
grid on;

Rotated showing a view like in your example: