match same column and row in matlab

I have one excel file containing rainfall data

85 82 82.5

22 22.5 23

0 2 0

2 0 1

1 0 2

0 1 0

I have another excel file where thre are lat lon values like:

82 22.5

82.5 23

I have to extract rainfall for lat lon of second excel file from first one.

If there is any way to extract it in matlab

[the cyclist]

Yes, of course you can extract data from Excel files using MATLAB.

Can you upload the data, or a small representative sample? Details like whether or not the file has headers matter. You can use the paper clip icon in the INSERT section of the toolbar to upload.

Also, it would be helpful if you told us exactly how you want the output of the algorithm to look like.

Answer 1

[Shlok]

Hi,

Based on the data and context you provided above; I assume you have the Excel files with the following headers:

File 1: Contains “Latitude”, “Longitude”, “RainDat1”, “RainDat2”, “RainDat3”, “RainDat4” in a row-wise format, where “RainDat(i)” is assumed to be four rain data values for specific coordinates.

File 2: Contains “Latitude”, “Longitude” in a column-wise format.

The goal is to extract the values of “RainDat1”, “RainDat2”, “RainDat3”, and “RainDat4” from File 1 based on the “Latitude” and “Longitude” values provided in File 2.

Follow the following steps to achieve the same:

• Read both the Excel files into tables using “readtable” function. For example:

        table = readtable("filename.xlsx"); 

• Convert them to arrays using "table2array” function for easier manipulation.

        tableData = table2array(table); 

• Transpose the second matrix to align with the header direction of the first matrix.

        tableData = tableData'; 

• Filter out columns where the first two cells (“Latitude” and “Longitude”) in the first matrix match the first two cells of any column in the second matrix. This can be achieved either by looping through the matrices or by using logical indexing.

This will help you achieve the desired result.

To delve deeper into the terminologies used, you can refer to the following documentation links:

• Using Excel files with MATLAB: https://www.mathworks.com/discovery/matlab-excel.html
• “readtable” function: https://www.mathworks.com/help/matlab/ref/readtable.html
• Logical indexing in arrays: https://www.mathworks.com/company/technical-articles/matrix-indexing-in-matlab.html