R read specific items from unformatted text with condition

user_14757224 0
Viewed 47

I have been using SAS for many years but now I have to transition into R. I really tried to search the net for this specific need but was unsuccessful.

I have the following huge file of which I will display the first two rows only (note each line contains up to 201 characters)

3 0B  1031001J13JUN2219JUN22   4    OTP07000700+0300  MAD10001000+02000 737IBCDPOMAYU                                           0B                0 S            M                              00000001
4 0B  1031001J              AB010OTPMADW2 9322                                                                                                                                                    000002

First, I need to import specific data from this file but only if the first position of each row starts with "3".

If the first position is a 3, I need to import the following data:

Company code (position 3-5), flight_number (position 6-9) formatted as a character, Origin (position 37-39), Destination (position 55-57), Days_of_operation (position 29-35) formatted as character.

Would appreciate your advice on how to do this.

import r text
1 Answers

As you have the full record specification available to you (your Company code (position 3-5) & etc above) it will be easier for you to define things, but using your data as presented:

fwf_record <- c('3 0B  1031001J13JUN2219JUN22   4    OTP07000700+0300  MAD10001000+02000 737IBCDPOMAYU                                           0B                0 S            M                              00000001','4 0B  1031001J              AB010OTPMADW2 9322                                                                                                                                                    000002')

note there are no \n line returns in this particular data, though likely in your file... This is important for playing around purposes as readr::read_fwf expects \n, but in its absence wrapping in I() suffices... We have two records, what does readr::read_fwf make of our 1st record, without any other info as to field start/ends & etc

readr::read_fwf(I(fwf_record[1]))
                                                                                Rows: 1 Columns: 12                
── Column specification ────────────────────────────────────────────────────────

chr (9): X2, X3, X5, X6, X7, X8, X10, X11, X12
dbl (3): X1, X4, X9

ℹ Use `spec()` to retrieve the full column specification for this data.
ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.
# A tibble: 1 × 12
     X1 X2    X3              X4 X5    X6    X7    X8       X9 X10   X11   X12  
  <dbl> <chr> <chr>        <dbl> <chr> <chr> <chr> <chr> <dbl> <chr> <chr> <chr>
1     3 0B    1031001J13J…     4 OTP0… MAD1… 737I… 0B        0 S     M     0000…

As you provide more info from your record specification relative to start/end positions. If your read both records `readr::read_fwf(I(fwf_record)), you see that column specs are effectively reduced to what is most generalizable across all records and is likely not what you want.

readr::read_fwf(I(fwf_record))
                                                                                Rows: 2 Columns: 10                
── Column specification ────────────────────────────────────────────────────────

chr (8): X2, X3, X4, X5, X6, X8, X9, X10
dbl (2): X1, X7

ℹ Use `spec()` to retrieve the full column specification for this data.
ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.
# A tibble: 2 × 10
     X1 X2    X3                       X4    X5    X6       X7 X8    X9    X10  
  <dbl> <chr> <chr>                    <chr> <chr> <chr> <dbl> <chr> <chr> <chr>
1     3 0B    1031001J13JUN2219JUN22 … MAD1… 737I… 0B        0 S     M     0000…
2     4 0B    1031001J              A… NA    NA    NA       NA NA    NA    0000…

In your position, people want you to work effectively with the available data, it is also likely that the full record specification is also available. From ?readr::read_fwf examples note:

3. Paired vectors of start and end positions

 read_fwf(fwf_sample, fwf_positions(c(1, 30), c(20, 42), c("name", "ssn")))

So I would wrestle the 'how to read this thing' to the ground first, then address the next steps of selecting only useful records & etc, to translate your prior SAS workflow to R. As separate questions as need be...

user_7944500
Related