Split string into a list on whitespace, excluding single spaces when the next character is not a dash

Question

I'm scraping a website that has a table of satellite values (https://planet4589.org/space/gcat/data/cat/satcat.html).

Because every entry is only separated by whitespace, I need a way to split the string of data entries into an array.

However, the .split() function does not suit my needs, because some of the data entries have spaces (e.g. Able 3), I can't just split everything separated by whitespace.

It gets trickier, however. In some cases where no data is available, a dash ("-") is used. If two data entries are separated by only a space, and one of them is a dash, I don't want to include it as one entry.

e.g say we have the two entries "Able 3" and "-", separated only by a single space. In the file, they would appear as "Able 3 -". I want to split this string into the separate data entries, "Able 3" and "-" (as a list, this would be ["Able 3", "-"]).

Another example would be the need to split "data1 -" into ["data1", "-"]

Pretty much, I need to take a string and split it into a list or words separated by whitespace, except when there is a single space between words, and one of them is not a dash.

Also, as you can see the table is massive. I thought about looping through every character, but that would be too slow, and I need to run this thousands of times.

Here is a sample from the beginning of the file:

JCAT         Satcat Piece          Type         Name                         PLName                       LDate        Parent        SDate              Primary       DDate              Status Dest           Owner        State        Manufacturer       Bus              Motor        Mass          DryMass      TotMass       Length    Diamete  Span       Shape                            ODate              Perigee   Apogee    Inc     OpOrbitOQU AltNames
S00001       00001  1957 ALP 1     R2           8K71A M1-10                  8K71A M1-10 (M1-1PS)         1957 Oct  4  -             1957 Oct  4 1933   Earth         1957 Dec  1 1000?  R      -              OKB1         SU           OKB1               Blok-A           -                7790         7790          7800    ?   28.0      2.6       28.0    Cyl                              1957 Oct  4             214       938   65.10  LLEO/I -
S00002       00002  1957 ALP 2     P            1-y ISZ                      PS-1                         1957 Oct  4  S00001        1957 Oct  4 1933   Earth         1958 Jan  4?       R      -              OKB1         SU           OKB1               PS               -                  84           84            84         0.6      0.6        2.9    Sphere + Ant                     1957 Oct  4             214       938   65.10  LLEO/I -
S00003       00003  1957 BET 1     P A          2-y ISZ                      PS-2                         1957 Nov  3  A00002        1957 Nov  3 0235   Earth         1958 Apr 14 0200?  AR     -              OKB1         SU           OKB1               PS               -                 508          508          8308    ?    2.0      1.0        2.0    Cone                             1957 Nov  3             211      1659   65.33  LEO/I  -
S00004       00004  1958 ALP       P A          Explorer 1                   Explorer 1                   1958 Feb  1  A00004        1958 Feb  1 0355   Earth         1970 Mar 31 1045?  AR     -              ABMA/JPL     US           JPL                Explorer         -                   8            8            14         0.8      0.1        0.8    Cyl                              1958 Feb  1             359      2542   33.18  LEO/I  -
S00005       00005  1958 BET 2     P            Vanguard I                   Vanguard Test Satellite      1958 Mar 17  S00016        1958 Mar 17 1224   Earth         -                  O      -              NRL          US           NRL                NRL 6"           -                   2            2             2         0.1      0.1        0.1    Sphere                           1959 May 23             657      3935   34.25  MEO    -
S00006       00006  1958 GAM       P A          Explorer 3                   Explorer 3                   1958 Mar 26  A00005        1958 Mar 26 1745   Earth         1958 Jun 28        AR     -              ABMA/JPL     US           JPL                Explorer         -                   8            8            14         0.8      0.1        0.8    Cyl                              1958 Mar 26             195      2810   33.38  LEO/I  -
S00007       00007  1958 DEL 1     R2           8K74A                        8K74A                        1958 May 15  -             1958 May 15 0705   Earth         1958 Dec  3        R      -              OKB1         SU           OKB1               Blok-A           -                7790         7790          7820    ?   28.0      2.6       28.0    Cyl                              1958 May 15             214      1860   65.18  LEO/I  -
S00008       00008  1958 DEL 2     P            3-y Sovetskiy ISZ            D-1 No. 2                    1958 May 15  S00007        1958 May 15 0706   Earth         1960 Apr  6        R      -              OKB1         SU           OKB1               Object D         -                1327         1327          1327         3.6      1.7        3.6    Cone                             1959 May  7             207      1247   65.12  LEO/I  -
S00009       00009  1958 EPS       P A          Explorer 4                   Explorer 4                   1958 Jul 26  A00009        1958 Jul 26 1507   Earth         1959 Oct 23        AR     -              ABMA/JPL     US           JPL                Explorer         -                  12           12            17         0.8      0.1        0.8    Cyl                              1959 Apr 24             258      2233   50.40  LEO/I  -
S00010       00010  1958 ZET       P A          SCORE                        SCORE                        1958 Dec 18  A00015        1958 Dec 18 2306   Earth         1959 Jan 21        AR     -              ARPA/SRDL    US           SRDL               SCORE            -                  68           68          3718         2.5  ?   1.5  ?     2.5    Cone                             1958 Dec 30             159      1187   32.29  LEO/I  -
S00011       00011  1959 ALP 1     P            Vanguard II                  Cloud cover satellite        1959 Feb 17  S00012        1959 Feb 17 1605   Earth         -                  O      -              BSC          US           NRL                NRL 20"          -                  10           10            10         0.5      0.5        0.5    Sphere                           1959 May 15             564      3304   32.88  MEO    -
S00012       00012  1959 ALP 2     R3           GRC 33-KS-2800               GRC 33-KS-2800 175-15-21     1959 Feb 17  R02749        1959 Feb 17 1604   Earth         -                  O      -              BSC          US           GCR                33-KS-2800       -                 195           22            22         1.5      0.7        1.5    Cyl                              1959 Apr 28             564      3679   32.88  MEO    -
S00013       00013  1959 BET       P A          Discoverer 1                 CORONA Test Vehicle 2        1959 Feb 28  A00017        1959 Feb 28 2156   Earth         1959 Mar  5        AR     -              ARPA/CIA     US           LMSD               CORONA           -                  78    ?      78    ?      668    ?    2.0      1.5        2.0    Cone                             1959 Feb 28             163?      968?  89.70  LLEO/P -
S00014       00014  1959 GAM       P A          Discoverer 2                 CORONA BIO 1                 1959 Apr 13  A00021        1959 Apr 13 2126   Earth         1959 Apr 26        AR     -              ARPA/CIA     US           LMSD               CORONA           -                 110    ?     110    ?      788         1.3      1.5        1.3    Frust                            1959 Apr 13             239       346   89.90  LLEO/P -
S00015       00015  1959 DEL 1     P            Explorer 6                   NASA S-2                     1959 Aug  7  S00017        1959 Aug  7 1430   Earth         1961 Jul  1        R?     -              GSFC         US           TRW                Able Probe       ARC 420            40           40            42    ?    0.7      0.7        2.2    Sphere + 4 Pan                   1959 Sep  8             250     42327   46.95  HEO    -   Able 3
S00016       00016  1958 BET 1     R3           GRC 33-KS-2800               GRC 33-KS-2800  144-79-22    1958 Mar 17  R02064        1958 Mar 17 1223   Earth         -                  O      -              NRL          US           GCR                33-KS-2800       -                 195           22            22         1.5      0.7        1.5    Cyl                              1959 Sep 30             653      4324   34.28  MEO    -
S00017       00017  1959 DEL 2     R3           Altair                       Altair X-248                 1959 Aug  7  A00024        1959 Aug  7 1428   Earth         1961 Jun 30        R?     -              USAF         US           ABL                Altair           -                  24           24            24         1.5      0.5        1.5    Cyl                              1961 Jan  8             197     40214   47.10  GTO    -
S00018       00018  1959 EPS 1     P A          Discoverer 5                 CORONA C-2                   1959 Aug 13  A00028        1959 Aug 13 1906   Earth         1959 Sep 28        AR     -              ARPA/CIA     US           LMSD               CORONA           -                 140          140           730         1.3      1.5        1.3    Frust                            1959 Aug 14             215       732   80.00  LLEO/I -   NRO Mission 9002

tripleee · Accepted Answer · 2020-12-16T07:34:24.867

A less haphazard approach would be to interpret the headers on the first line as column indicators, and split on those widths.

import sys
import re

def col_widths(s):
    # Shamelessly adapted from https://stackoverflow.com/a/33090071/874188
    cols = re.findall(r'\S+\s+', s)
    return [len(col) for col in cols]

widths = col_widths(next(sys.stdin))

for line in sys.stdin:
    line = line.rstrip('\n')
    fields = []
    for col_max in widths[:-1]:
        fields.append(line[0:col_max].strip())
        line = line[col_max:]
    fields.append(line)
    print(fields)

Demo: https://ideone.com/ASANjn

This seems to provide a better interpretation of e,g. the LDate column, where the dates are sometimes padded with more than one space. The penultimate column preserves the final dash as part of the column value; this seems more consistent with the apparent intent of the author of the original table, though perhaps separately split that off from that specific column if that's not to your liking.

If you don't want to read sys.stdin, just wrap this in with open(filename) as handle: and replace sys.stdin with handle everywhere.

Thanks, I can't believe I didn't think of that... also, I think `for col in widths:` should be changed to `for col_max in widths:`, based on what you have written on ASANjn — jack.py, Dec 16 '20 at 06:56
What if you didn't want to use sys.stdin? (Edit: sorry, I realise I could just get the string and split(\n")) — jack.py, Dec 16 '20 at 07:26
You can `open()` any other file and pass the handle to that instead of `sys.stdin`. Updated the answer to explain this in a bit more detail. — tripleee, Dec 16 '20 at 07:33

TrebledJ · Answer 2 · 2020-12-16T06:57:38.243

One approach is to use pandas.read_fwf(), which reads text files in fixed-width format. The function returns Pandas DataFrames, which are useful for handling large data sets.

As a quick taste, here's what this simple bit of code does:

import pandas as pd

data = pd.read_fwf("data.txt")
print(data.columns)  # Prints an index of all columns.
print()
print(data.head(5))  # Prints the top 5 rows.

# Index(['JCAT', 'Satcat', 'Piece', 'Type', 'Name', 'PLName', 'LDate',
#        'Unnamed: 7', 'Parent', 'SDate', 'Unnamed: 10', 'Unnamed: 11',
#        'Primary', 'DDate', 'Unnamed: 14', 'Status', 'Dest', 'Owner', 'State',
#        'Manufacturer', 'Bus', 'Motor', 'Mass', 'Unnamed: 23', 'DryMass',
#        'Unnamed: 25', 'TotMass', 'Unnamed: 27', 'Length', 'Unnamed: 29',
#        'Diamete', 'Span', 'Unnamed: 32', 'Shape', 'ODate', 'Unnamed: 35',
#        'Perigee', 'Apogee', 'Inc', 'OpOrbitOQU', 'AltNames'],
#       dtype='object')
# 
#      JCAT  Satcat       Piece Type  ... Apogee    Inc OpOrbitOQU  AltNames
# 0  S00001       1  1957 ALP 1   R2  ...    938  65.10   LLEO/I -       NaN
# 1  S00002       2  1957 ALP 2    P  ...    938  65.10   LLEO/I -       NaN
# 2  S00003       3  1957 BET 1  P A  ...   1659  65.33   LEO/I  -       NaN
# 3  S00004       4    1958 ALP  P A  ...   2542  33.18   LEO/I  -       NaN
# 4  S00005       5  1958 BET 2    P  ...   3935  34.25   MEO    -       NaN

You'll note that some of the columns are unnamed. We can solve this by determining the field widths of the file, guiding the read_fwf()'s parsing. We'll achieve this by reading the first line of the file and iterating over it.

field_widths = []  # We'll append column widths into this list.
last_i = 0
new_field = False
for i, x in enumerate(first_line):
    if x != ' ' and new_field:
        # Register a new field.
        new_field = False
        field_widths.append(i - last_i)  # Get the field width by subtracting
                                         #   the index from previous field's index.
        last_i = i  # Set the new field index.

    elif not new_field and x == ' ':
        # We've encountered a space.
        new_field = True # Set true so that the next
                         #   non-space encountered is
                         #   recognised as a new field
else:
    field_widths.append(64)  # Append last field. Set to a high number, 
                             #   so that all strings are eventually read.

Just a simple for-loop. Nothing fancy.

All that's left is passing the field_widths list through the widths= keyword arg:

data = pd.read_fwf("data.txt", widths=field_widths)
print(data.columns)

# Index(['JCAT', 'Satcat', 'Piece', 'Type', 'Name', 'PLName', 'LDate', 'Parent',
#        'SDate', 'Primary', 'DDate', 'Status', 'Dest', 'Owner', 'State',
#        'Manufacturer', 'Bus', 'Motor', 'Mass', 'DryMass', 'TotMass', 'Length',
#        'Diamete', 'Span', 'Shape', 'ODate', 'Perigee', 'Apogee', 'Inc',
#        'OpOrbitOQU'],
#       dtype='object')

data is a dataframe, but with some work, you can change it to a list of lists or a list of dicts. Or you could also work with the dataframe directly.

So say, you want the first row. Then you could do

datalist = data.values.tolist()
print(datalist[0])

# ['S00001', 1, '1957 ALP 1', 'R2', '8K71A M1-10', '8K71A M1-10 (M1-1PS)', '1957 Oct  4', '-', '1957 Oct  4 1933', 'Earth', '1957 Dec  1 1000?', 'R', '-', 'OKB1', 'SU', 'OKB1', 'Blok-A', '-', '7790', '7790', '7800    ?', '28.0', '2.6', '28.0', 'Cyl', '1957 Oct  4', '214', '938', '65.10', 'LLEO/I -']

E.g. `'1957 Dec 1 1000?` should apparently be two separate columns. — tripleee, Dec 16 '20 at 06:27
@tripleee Hmm I thought it was a hhmm timestamp or something. — TrebledJ, Dec 16 '20 at 06:33

Split string into a list on whitespace, excluding single spaces when the next character is not a dash

2 Answers2

Linked

Related