How can I split strings at a backslash "\" in R?

Question

I am trying to process a CSV output file that uses the "\" in almost all of the column titles. An example of column titles would be:

"RiverName 0 \ site 1", "RiverName 1 \ site 2", "RiverName 2 \ site 3"

I'm trying to separate the river locations from the site numbers and put those values into a table, following this format:

river          site
RiverName 0    site 1
RiverName 1    site 2
RiverName 2    site 3

I found a couple of related responses for splitting on a "\" that haven't solved my problem, but one solution, or the other might not work because I'm trying to split the string in each column into two strings / two columns.

The ultimate goal is to be able to identify which "RiverName #"s have more than one "site #", and to use that list to select the columns of data from the full data table. Each set of "site #"s that belong to the same "RiverName # \ ..." will be have their values across all rows. The number of maximum number of "site #"s for a single "RiverName #" should be 2, but I'm processing 1000+ columns of data, with some variation in which sites report depending on the model run.

A small chunk of the actual data looks like this:

Streamflow (below node or reach listed)                                         
Scenario: test_1986  All Rivers (75)     All months (12)                                    
$Unit = Cubic Meter                                         
$ListSeparator = ,                                          
$DecimalSymbol = .                                          
$Columns = Year  Timestep    "AmericanFork   0 \ Headflow[Cubic Meter]"  "AmericanFork   1 \ Catchment Inflow Node 96[Cubic Meter]"  "AmericanFork   2 \ Reach[Cubic Meter]"     "AmericanFork   3 \ AmericanFk AB UpperPower 10164500 (gauge)[Cubic Meter]"     "AmericanFork   3 \ Catchment Inflow Node 97[Cubic Meter]"  "AmericanFork   4 \ Reach[Cubic Meter]"     "AmericanFork   5 \ Catchment Inflow Node 391[Cubic Meter]"     "AmericanFork   6 \ Reach[Cubic Meter]"     "AmericanFork   7 \ Catchment Inflow Node 23[Cubic Meter]"  "AmericanFork   8 \ Reach[Cubic Meter]"
1985    1   0   233162  233162  59529600    396115  396115  466798  466798  822034  822034
1985    2   0   224064  224064  46915200    380629  380629  448538  448538  789499  789499
1985    3   0   215528  215528  68860800    366083  366083  460029  460029  3082519 3082519
1985    4   0   479387  479387  240883200   1186144 1186144 1732742 1732742 9214172 9214172
1985    5   0   1129770 1129770 568857600   2394140 2394140 2573311 2573311 3957801 3957801
1985    6   0   560258  560258  457142400   1046736 1046736 1225671 1225671 2936521 2936521
1985    7   0   403568  403568  205804800   742211  742211  844912  844912  2336802 2336802
1985    8   0   286687  286687  118886400   509035  509035  593644  593644  1204795 1204795
1985    9   0   294099  294099  79228800    543535  543535  643345  643345  1489512 1489512
1985    10  0   456219  456219  88646400    1057770 1057770 1287924 1287924 4032124 4032124
1985    11  0   264292  264292  68256000    509662  509662  592545  592545  2725935 2725935
1985    12  0   251136  251136  66873600    472174  472174  550178  550178  934797  934797

Reading the row of column names after reading the entire CSV file into R resulted with a data frame of one row, but each column has 252 levels (the total # of rows in the original data frame from which the single row was extracted.) Instead, I used the following code to read only the row of containing the column names from the CSV.

> ras <- read.table(filename, header = FALSE, sep = ",", skip = 5, nrow = 1)

The output (ras) looks like this:

 V1        V2                                         V3
1 $Columns = Year  Timestep  AmericanFork   0 \\ Headflow[Cubic Meter]
                                                          V4
1  AmericanFork   1 \\ Catchment Inflow Node 96[Cubic Meter]
                                       V5
1  AmericanFork   2 \\ Reach[Cubic Meter]
                                                                           V6
1  AmericanFork   3 \\ AmericanFk AB UpperPower 10164500 (gauge)[Cubic Meter]
                                                          V7
1  AmericanFork   3 \\ Catchment Inflow Node 97[Cubic Meter]
                                       V8
1  AmericanFork   4 \\ Reach[Cubic Meter]
                                                           V9
1  AmericanFork   5 \\ Catchment Inflow Node 391[Cubic Meter]
                                      V10
1  AmericanFork   6 \\ Reach[Cubic Meter]
                                                         V11
1  AmericanFork   7 \\ Catchment Inflow Node 23[Cubic Meter]
                                      V12
1  AmericanFork   8 \\ Reach[Cubic Meter]

I removed the first two values (row labels from the original CSV) and then the names of the values in an attempt to simplify the data I want to split.

ras2 <- ras[ ,-c(1,2)] 
ras3 <- unlist(c(wnr2), use.names = FALSE)

The output (ras3) now looks like this:

[1]  AmericanFork   0 \\ Headflow[Cubic Meter]                                 
 [2]  AmericanFork   1 \\ Catchment Inflow Node 96[Cubic Meter]                 
 [3]  AmericanFork   2 \\ Reach[Cubic Meter]                                    
 [4]  AmericanFork   3 \\ AmericanFk AB UpperPower 10164500 (gauge)[Cubic Meter]
 [5]  AmericanFork   3 \\ Catchment Inflow Node 97[Cubic Meter]                 
 [6]  AmericanFork   4 \\ Reach[Cubic Meter]                                    
 [7]  AmericanFork   5 \\ Catchment Inflow Node 391[Cubic Meter]                
 [8]  AmericanFork   6 \\ Reach[Cubic Meter]                                    
 [9]  AmericanFork   7 \\ Catchment Inflow Node 23[Cubic Meter]                 
[10]  AmericanFork   8 \\ Reach[Cubic Meter]                                    
10 Levels:  AmericanFork   0 \\ Headflow[Cubic Meter] ...

"AmericanFork 3" is an example a "RiverName #" that has a pair of sites ("AmericanFk AB UpperPower 10164500 (gauge)[Cubic Meter]" and "Catchment Inflow Node 97[Cubic Meter]") that I want to extract from the full CSV for comparisons.

You can split your first example with `x <- data.frame(do.call(rbind, strsplit(s, '\\s*[\\]\\s*'))); names(x) <- c('river', 'site')` where `s` is a string vector of the three pairs. — alistaire, Mar 08 '16 at 22:18
It seems to work on the bigger data, too. You can tally overlapping sites with `table(x$river)`; if you only want rivers with repeats, you can subset the table: `table(x$river)[table(x$river) > 1]` — alistaire, Mar 08 '16 at 22:35

How can I split strings at a backslash "\" in R?

0 Answers0