Clean data and extraction

Dear expert-

I am trying to fill a column and extract some specific rows from a data, I cannot figure out how to do it.

What I want to do is:

1) If row from column H = 0, replace 0 with its corresponding row value from column G.

2) If column G = column S, extract only that row + row before and row after.

Your will be very appriciated.

Thank you

[Dyuman Joshi]

Use logical indexing for the first part - Find Array Elements That Meet a Condition, Access Data in Tables

(For the 2nd part) It's not clear to me what extract means in this context. Though the approach is similar, compare and get the indices satisfying the comparison via find and get the corresponding indices for the rows before and after by adding and subtracting 1. You will need to check if the indices are within the range of dimensions or not.

Use the indices to extract the data as required.

[Sanley Guerrier]

Thanks, Dyuman

What I mean for example if value in row 5 is the same for column G and S, extract rows 4, 5, and 6.

[Dyuman Joshi]

Yes, I understood that and provided suggestions accordingly.

I specified a flow chart to go through what you want to do, whereas @Voss was kind enough to provide you a full working solution.

[Sanley Guerrier]

Appriciate it!

Answer 1

[Voss]

T = readtable('data.xlsx');
T.Properties.VariableNames = num2cell(char(64+(1:size(T,2))));

% set H = G where H == 0
idx = T.H == 0;
T.H(idx) = T.G(idx);

% extract rows +/- 1 where G == S
idx = T.G == T.S;
idx = any([idx [false; idx(1:end-1)] [idx(2:end); false]],2);
extracted_rows = T(idx,:)

extracted_rows = 12×28 table
     A       B           C              D                   E                  F        G       H        I       J        K          L       M          N           O         P         Q       R      S            T             U         V       W      X    Y     Z     [        \    
    ___    _____    ___________    ___________    ______________________    _______    ____    ____    _____    ___    ________    _____    ___    ___________    _____    _______    _____    ___    ____    _____________    _______    _____    ____    _    __    __    __    ________

    107    {'U'}    {'0+0.000'}    {'0+0.151'}    {'2010 RECON BIT'    }    {'BAB'}    2005    2013     4500    2.9    {'TRAN'}      166    107    {'MN107-U'}    {'U'}    {'BAB'}     4500    9.4    2004    {0×0 char   }    {'BAB'}    138.5    0.11    0     6    24    26    {'TRAN'}
    107    {'U'}    {'0+0.000'}    {'0+0.151'}    {'2010 RECON BIT'    }    {'BAB'}    2005    2013     4500    2.9    {'TRAN'}      166    107    {'MN107-U'}    {'U'}    {'BAB'}     4500    9.4    2005    {'BAB Rural'}    {'BAB'}       55    0.05    0     0     0     0    {'TRAN'}
    107    {'U'}    {'0+0.000'}    {'0+0.151'}    {'2010 RECON BIT'    }    {'BAB'}    2005    2013     4500    2.9    {'TRAN'}      166    107    {'MN107-U'}    {'U'}    {'BAB'}     4500    9.4    2006    {0×0 char   }    {'BAB'}       47    0.11    0     0     0     0    {'TRAN'}
    238    {'U'}    {'0+0.000'}    {'0+0.302'}    {'2014 BAB'          }    {'BAB'}    2014    2014     1400    3.4    {'TRAN'}     82.5    238    {'MN238-U'}    {'U'}    {'BAB'}     1400    9.3    2013    {0×0 char   }    {'BAB'}    293.5    0.17    0    10    20    12    {'TRAN'}
    238    {'U'}    {'0+0.000'}    {'0+0.302'}    {'2014 BAB'          }    {'BAB'}    2014    2014     1400    3.4    {'TRAN'}     82.5    238    {'MN238-U'}    {'U'}    {'BAB'}     1400    9.3    2014    {'BAB Urban'}    {'BAB'}     70.5    0.09    0     0     0     0    {'TRAN'}
    238    {'U'}    {'0+0.000'}    {'0+0.302'}    {'2014 BAB'          }    {'BAB'}    2014    2014     1400    3.4    {'TRAN'}     82.5    238    {'MN238-U'}    {'U'}    {'BAB'}     1400    9.3    2015    {0×0 char   }    {'BAB'}     69.5    0.07    0     0     0     0    {'TRAN'}
     84    {'U'}    {'0+0.000'}    {'0+0.353'}    {'2010 MED MILL & OL'}    {'BOB'}    2010    2013     4500    2.2    {'TRAN'}    200.5     84    {'MN84-U' }    {'U'}    {'BOB'}     4500    9.4    2009    {0×0 char   }    {'BAB'}    211.5    0.29    0     0     0     0    {'TRAN'}
     84    {'U'}    {'0+0.000'}    {'0+0.353'}    {'2010 MED MILL & OL'}    {'BOB'}    2010    2013     4500    2.2    {'TRAN'}    200.5     84    {'MN84-U' }    {'U'}    {'BOB'}     4500    9.4    2010    {'Med M&OL' }    {'BAB'}      135    0.15    0     0     0     0    {'TRAN'}
     84    {'U'}    {'0+0.000'}    {'0+0.353'}    {'2010 MED MILL & OL'}    {'BOB'}    2010    2013     4500    2.2    {'TRAN'}    200.5     84    {'MN84-U' }    {'U'}    {'BOB'}     4500    9.4    2011    {0×0 char   }    {'BAB'}    119.5     0.1    0    22     0     0    {'TRAN'}
    371    {'D'}    {'0+0.000'}    {'0+0.375'}    {'96 SURF/01 R&S'    }    {'BOB'}    2011    2017    53000      3    {'TRAN'}    102.5    371    {'MN371-D'}    {'D'}    {'BOB'}    53000    7.9    2010    {0×0 char   }    {'BAB'}    196.5    0.23    0    24    14     4    {'TRAN'}
    371    {'D'}    {'0+0.000'}    {'0+0.375'}    {'96 SURF/01 R&S'    }    {'BOB'}    2011    2017    53000      3    {'TRAN'}    102.5    371    {'MN371-D'}    {'D'}    {'BOB'}    53000    7.9    2011    {'Med M&OL' }    {'BAB'}     39.5       0    0     0     0     0    {'TRAN'}
    371    {'D'}    {'0+0.000'}    {'0+0.375'}    {'96 SURF/01 R&S'    }    {'BOB'}    2011    2017    53000      3    {'TRAN'}    102.5    371    {'MN371-D'}    {'D'}    {'BOB'}    53000    7.9    2012    {0×0 char   }    {'BAB'}       73       0    0     0     0     0    {'TRAN'}

[Sanley Guerrier]

Thank you, Voss.

This is exactly what I wanted.

[Voss]

You're welcome!