Organizing data



  • Hello, I have data that is in long rows, but I need to make different columns while keeping the rows in the certain order.

    For example, I have this:
    ospht421 4.52E-02 -1.20E-02 2.98E-03 3.19E-02 -1.93E-02 -6.87E-02 4.16E-02 1.71E-02 -2087 2.95E-02

    And I need it to look like this:
    ospht421
    4.52E-02 -1.20E-02 2.98E-03
    3.19E-02 -1.93E-02 -6.87E-02
    4.16E-02 1.71E-02 -2082.95E-02

    I have a lot of data and it takes forever to do it by hand. Any advice would be appreciated!

    Thanks,
    Kim



  • Hello Kimberly Plomp,

    Once more, this kind of modification of text can be easily achieved with regular expressions !

    At first sight, your text seems to contain a list of numbers, in the classical E scientific notation ! Am I right ?

    I supposed so. Therefore, I, first, tried to build a regex, in order to match such numbers. Taking in account, all the different syntaxes for these numbers, with an example below :

    +9.9999998E+03
    +1.23e-07
    -5.4625e+04
    -6.258E-00
    5.789E+02
    1.008E-06
    
    +9.E+03
    +1.E-07
    -5.e+04
    -6.E-00
    5.E+02
    1.e-06
    
    +9E+03
    +1e-07
    -5E+04
    -6e-00
    5E+02
    1E-06
    

    I got this regex : [+-]?\d+(\.(\d+)?)?[Ee][+-]\d\d

    • The part [+-]? matches an optional sign ( remain that the regex quantifier ? means 0 or 1 time )

    • The part \d+ matches the mantissa part, before the dot ( The regex quantifier + means 1 or more time(s) )

    • The part (\.(\d+)?)? represents the optional fractional part of a number, itself composed of a dot \. ( which must be escaped as it’s a meta character in regexes ) and an optional digits (\d+)?, after the dot

    • The part [Ee] matches the E notation letter, whatever the case

    • The part [+-] matches the mandatory sign

    • The part \d\d represents the two digits of the exponent

    Right. Now, from your text, below :

    ospht421 4.52E-02 -1.20E-02 2.98E-03 3.19E-02 -1.93E-02 -6.87E-02 4.16E-02 1.71E-02 -2082.95E-02
    

    We notice that any E notation number is preceded by a space. So, as you expect to split on several lines, every 3 numbers, we can build the final search regex ( [+-]?\d+(\.(\d+)?)?[Ee][+-]\d\d){3}, which will matches 3 numbers

    • Enclosed in round brackets, it begins with a space and ends with the {3} quantifier

    • The replacement part \r\n$0 will replace this range of numbers by an Windows End of Line characters \r\n, followed by the entire regex matched $0

    • Don’t forget, in the Replace dialog, to set the Regular expression search mode !

    So , from the test example, below

    ospht421 4.52E-02 -1.20E-02 2.98E-03 3.19E-02 -1.93E-02 -6.87E-02 4.16E-02 1.71E-02 -2082.95E-02
    
    ospht000 +9.9999998E+03 +1.23e-07 -5.4625e+04 -6.258E-00 5.789E+02 1.008E-06 +9.E+03 +1.E-07 -5.e+04
    
    ospht999 -6.E-00 5.E+02 1.e-06 +9E+03 +1e-07 -5E+04 -6e-00 5E+02 1E-06
    

    After clicking on the Replace All button, we get the new text :

    ospht421
     4.52E-02 -1.20E-02 2.98E-03
     3.19E-02 -1.93E-02 -6.87E-02
     4.16E-02 1.71E-02 -2082.95E-02
    
    ospht000
     +9.9999998E+03 +1.23e-07 -5.4625e+04
     -6.258E-00 5.789E+02 1.008E-06
     +9.E+03 +1.E-07 -5.e+04
    
    ospht999
     -6.E-00 5.E+02 1.e-06
     +9E+03 +1e-07 -5E+04
     -6e-00 5E+02 1E-06
    

    Possibly, to get rid of the leading space(s), just use the simple search regex ^ + and leave the replacement zone empty

    Et voilà !

    Best Regards,

    guy038



  • Thanks, guy038!

    I have never used Notepad++ before, so I am not familiar with how to use it. I have tried your suggestion in the Replace box but it say there are 0 replacements.

    Find what: ([±]?\d+(.(\d+)?)?[Ee][±]\d\d){3}
    Replace with: \r\n$0
    And the Regular Expression box is ticked.

    I am sure it is just a silly mistake on my part, but I can’t seem to get it working.

    Thanks!
    Kim



  • Hi Kim,

    From your reply, it happens that one SPACE should be present, in the search regex, between the opening round bracket and the block [+-] !

    Well, I just realized that your numbers could be separated by more than one space characters ! Moreover, your file may, also, contain some tabulation characters. In that case, prefer the search regex below ( The replacement regex is unchanged ):

    (\h+[+-]?\d+(\.(\d+)?)?[Ee][+-]\d\d){3}

    Notes :

    • The \h regex syntax matches one horizontal blank character, that is to say :

      • The normal space character ( \x20 )

      • The tabulation character ( \x09 )

      • The No-Break space ( abbreviated NBSP ) ( \xA0 )

    • As usual, the + quantifier means one or more times the previous character


    So given the test example below, where I put, between numbers, several spaces, in the first line, several tabulations in the second line and a mix of these two, in the third line :

        ospht421 4.52E-02     -1.20E-02 2.98E-03          3.19E-02             -1.93E-02 -6.87E-02 4.16E-02 1.71E-02 -2082.95E-02
    
    ospht000	+9.9999998E+03	+1.23e-07	-5.4625e+04	-6.258E-00	5.789E+02	1.008E-06	+9.E+03	+1.E-07	-5.e+04
    
            ospht999         		-6.E-00 5.E+02			        1.e-06 +9E+03 +1e-07 -5E+04    		    	-6e-00 5E+02 1E-06
    

    After clicking on the Replace All button, we get the following text :

            ospht421
     4.52E-02     -1.20E-02 2.98E-03
              3.19E-02             -1.93E-02 -6.87E-02
     4.16E-02 1.71E-02 -2082.95E-02
        
        ospht000
    	+9.9999998E+03	+1.23e-07	-5.4625e+04
    	-6.258E-00	5.789E+02	1.008E-06
    	+9.E+03	+1.E-07	-5.e+04
        
                ospht999
             		-6.E-00 5.E+02			        1.e-06
     +9E+03 +1e-07 -5E+04
        		    	-6e-00 5E+02 1E-06
    

    Now, we have to get rid of all the possible blanks characters, at the beginning of the lines and just keep one space character between the different numbers. The following S/R will achieve it, nicely :

    Find what : ^\h+|(\h+)

    Replace with : (?1 ) , with a space after the digit 1

    Notes :

    • Due to the alternative regex symbol |, this search regex looks :

      • for any non null range of blank characters, that begins the line ( the ^ symbol represents the zero length assertion beginning of line )

      • for any subsequent range of these blank characters, between the numbers

    • The second part (\h+), ( the subsequent blanks ), is enclosed in round brackets, and represents the group 1

    • In replacement, we use the conditional form (?n....) :

      • which replace the searched regex with the string located after digit n , till the ending round bracket, if the group n exists

      • which does NO replacement if the group n does NOT exist

    So :

    • When the first part, of the search regex, matches possible blank characters, at beginning of a line, group 1, does not exist. Therefore, there’s NO replacement

    • When the second part, of the search regex, matches blank characters, before the numbers, group 1 exists. Then they are replaced with the single space, located between the digit 1 and the ending round bracket, of the replacement regex

    This time, after performing this second S/R, we obtain the final text below :

    ospht421
    4.52E-02 -1.20E-02 2.98E-03
    3.19E-02 -1.93E-02 -6.87E-02
    4.16E-02 1.71E-02 -2082.95E-02
    
    ospht000
    +9.9999998E+03 +1.23e-07 -5.4625e+04
    -6.258E-00 5.789E+02 1.008E-06
    +9.E+03 +1.E-07 -5.e+04
    
    ospht999
    -6.E-00 5.E+02 1.e-06
    +9E+03 +1e-07 -5E+04
    -6e-00 5E+02 1E-06
    

    Cheers,

    guy038


Log in to reply