Using two vectors to get the third one

Please I have these three columns where the first line is hour but not all the hours in a day, the second line is data of the corresponding first hours. I have the third line as hours complete.

I want to use the third line, any hour thats is not represented in the first line should be represented with nan in the second line, e.g

0 34 0

1 23 1

2 34 2

4 12 3

5 13 4

7 4.6 5

8 0.4 6

9 -3.8 7

10 -8 8

12 -16.4 9

13 -20.6 10

14 -24.8 11

16 -33.2 12

17 -37.4 13

18 -41.6 14

20 -50 15

21 -54.2 16

23 -62.6 17

18

19

20

21

22

23

[the cyclist]

How are the data currently stored? When you say "these three columns", do you mean you have three different vectors?

[TTA]

Yes There are vectors

Answer 1

[Joe Vinciguerra]

x = [0,1,2,4,5,7,8,9,10,12,13,14,16,17,18,20,21,23]; % here's your first column
y = x*rand()+rand(); % here's a vague representation of your second column
z = [0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23]; % here's your third column
index = (ismember(z,x)); % find which values in z are in x

yPrime = zeros(length(index),1); % preallocate a new variable
skip = 0; % We need a counter to count every time we skip a value to make the array sizes work out
for i = 1:length(index)
    if index(i) % if the number exists...
        yPrime(i) = y(i-skip); % stuff it in a new variable
    else % if it doesn't exist
        yPrime(i) = NaN; % set it to NaN
        skip = skip + 1; % and count it
    end
end

[TTA]

Hi, please this solved the question I asked but unfortunately I dont know how to apply it to my problem.

What i want to do is this.....looking at the attached file, column 5 is the hour and 6 is the minutes, and 12 is the data i want to use. But it is not every hour of the day (0-23) that has data represented. Therefore I want to make every hour where the data is not represented to be NAN.

I some other files I don't these hours in particular.

here is the code I'm using

%% Initialize variables.
filename = 'C:\Users\INPE\Documents\Incomplete24\Data_Tropopause_08-Sep-2006.txt';
formatSpec = '%4f%5C%4f%5f%4f%4f%7f%8f%15f%15f%15f%f%[^\n\r]';
%% Open the text file.
fileID = fopen(filename,'r');
dataArray = textscan(fileID, formatSpec, 'Delimiter', '', 'WhiteSpace', '', 'TextType', 'string',  'ReturnOnError', false);
%% Close the text file.
fclose(fileID);
%% Create output variable
Data = table(dataArray{1:end-1}, 'VariableNames', {'Year','Month','Date','DayofYear','Hour','Minute','Latitude','Longitude','TropTempRaw','TropHeightRaw','TropTempTb','TropHeightTb'});
%% Clear temporary variables
clearvars filename formatSpec fileID dataArray ans;
x = table2array(Data(:,12));
ss1 = num2str(table2array(Data(:,5)));
ss2 = num2str(table2array(Data(:,6)));
SS1 = table2array(cell2table(strcat(ss1, {'.'}, ss2)));
SS2 = str2double(regexprep(SS1,' ',''));
%x = [0,1,2,4,5,7,8,9,10,12,13,14,16,17,18,20,21,23]; % here's your first column
%y = Data(:,12); % here's a vague representation of your second column
%z = 0:1:23; % here's your third column
z= (0:1439)./60;
index = (ismember(z,x)); % find which values in z are in x
yPrime = zeros(length(index),1); % preallocate a new variable
skip = 0; % We need a counter to count every time we skip a value to make the array sizes work out
for i = 1:length(index)
    if index(i) % if the number exists...
        yPrime(i) = SS2(i-skip); % stuff it in a new variable
    else % if it doesn't exist
        yPrime(i) = NaN; % set it to NaN
        skip = skip + 1; % and count it
    end
end

[Joe Vinciguerra]

It looks like you are confusing your variables. Line 13 should be what I was calling "y", not "x". Similarly, SS2 on line 27 is not the correct variable.

I see that you are combining the Hours and Minutes to get a decimal number of hours. That's fine, but it makes the code I provided invalid.

Run the code I provided first on the Hours column before concatenation. And while it's building a new yPrime variable do the same for a new HoursPrime variable and a new MinutesPrime variable: whenever it inserts a NaN value into yPrime you should be adding a new hour value into HoursPrime and a 0 (zero) into the MinutesPrime, all with the same "skip" technique.

Then at the very end you could concatenate HoursPrime and MinutesPrime like you were before.

[TTA]

Hi Joe,

I have been trying to it the same you wrote since you answered, but please I don't want to hide.

Please can you help me with a code example as you did earlier?

Thanks

[Joe Vinciguerra]

I'll take a look at it but it won't be until tomorrow.

[TTA]

Ok thank you

[Joe Vinciguerra]

Next time please ask the question in context to your proplem.

I did this a little different since your data is actually in a table (not a vector), there are duplicate Hour values, and there are more than just the columns you described that apparently need further processing.

vNames = {'Year','Month','Date','DayofYear','Hour','Minute','Latitude','Longitude','TropTempRaw','TropHeightRaw','TropTempTb','TropHeightTb'};
allHours = table([0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23]','VariableNames',{'Hour'}); % here's your third column
index = (ismember(allHours,unique(Data(:,'Hour'))));
missingData = array2table(NaN(height(allHours(~index,"Hour")),width(Data)),'VariableNames',vNames)
missingData.Hour = allHours.Hour(~index);
missingData.Month = num2cell(missingData.Month(:));
Data = [Data;missingData]

[TTA]

Thank you Joe ..................Please help me take a look at it again. It didnt work.

Grazie mille

[Walter Roberson]

Joe Vinciguerra comments to Toyese Ayorinde

This code works with file provided, and does what was requested. This question should be closed. Toyese, if you have addition issues you should ask a new question and provide details about what you are trying to accomplish and specifically what the issue you are having is. Thank you.

Answer 2

[Fangjun Jiang]

a=[0 1 4 7]
b=rand(size(a))
c=0:23
d=nan(size(c))
d(a+1)=b

[TTA]

Thanks but please what if I don't these hours that are missing?

[TTA]

Thank you very much for helping

Answer 3

[the cyclist]

This is virtually equivalent to Fangjun's solution. But it uses some intuitive variable names (and comments) to help you understand what it going on.

The first three lines are where you would have your actual vectors.

%%% This part is just setting up some input data that are like what you describe

incompleteHours = [0; 1; 2; 4; 5; 7]; % Your "first line" hours
data = rand(size(incompleteHours)); % Your "second line" data
completeHours = (0:23)'; % Your "third line" with the complete list of hours


%%% This part is the actual solution, using those inputs

% Preallocate the output with NaN. (We'll fill in the data later)
dataForCompleteHours = nan(size(completeHours));

% Identify the hours we have, and their index to the data
[~,idx] = ismember(incompleteHours,completeHours);

% Fill in the data
dataForCompleteHours(idx) = data;

[TTA]

Thank you very much for helping

[TTA]

Hi, please this solved the question I asked but unfortunately I dont know how to apply it to my problem.

What i want to do is this.....looking at the attached file, column 5 is the hour and 6 is the minutes, and 12 is the data i want to use. But it is not every hour of the day (0-23) that has data represented. Therefore I want to make every hour where the data is not represented to be NAN.

I some other files I don't these hours in particular.

here is the code I'm using

%% Initialize variables.

filename = 'C:\Users\INPE\Documents\Incomplete24\Data_Tropopause_08-Sep-2006.txt';

formatSpec = '%4f%5C%4f%5f%4f%4f%7f%8f%15f%15f%15f%f%[^\n\r]';

%% Open the text file.

fileID = fopen(filename,'r');

dataArray = textscan(fileID, formatSpec, 'Delimiter', '', 'WhiteSpace', '', 'TextType', 'string', 'ReturnOnError', false);

%% Close the text file.

fclose(fileID);

%% Create output variable

Data = table(dataArray{1:end-1}, 'VariableNames', {'Year','Month','Date','DayofYear','Hour','Minute','Latitude','Longitude','TropTempRaw','TropHeightRaw','TropTempTb','TropHeightTb'});

%% Clear temporary variables

clearvars filename formatSpec fileID dataArray ans;

x = table2array(Data(:,12));

ss1 = num2str(table2array(Data(:,5)));

ss2 = num2str(table2array(Data(:,6)));

SS1 = table2array(cell2table(strcat(ss1, {'.'}, ss2)));

SS2 = str2double(regexprep(SS1,' ',''));

%x = [0,1,2,4,5,7,8,9,10,12,13,14,16,17,18,20,21,23]; % here's your first column

%y = Data(:,12); % here's a vague representation of your second column

%z = 0:1:23; % here's your third column

z= (0:1439)./60;

index = (ismember(z,x)); % find which values in z are in x

yPrime = zeros(length(index),1); % preallocate a new variable

skip = 0; % We need a counter to count every time we skip a value to make the array sizes work out

for i = 1:length(index)

if index(i) % if the number exists...

yPrime(i) = SS2(i-skip); % stuff it in a new variable

else % if it doesn't exist

yPrime(i) = NaN; % set it to NaN

skip = skip + 1; % and count it

end

end