CODEWITHSUNDEEP
rstatadata-manipulation
M.Smith
M.Smith

Reputation: 3

Converting long string of comma delimited integers into x and y columns

My data is a long, single line of values separated by commas where every other value is an x or a y coordinate

Data looks like this: 2622731,1387660,2621628,1444522,2619235,1681640

But I want it to look like this:

2622731,1387660

2621628,1444522

2619235,1681640

Short of going through the entire file and deleting the comma and hitting enter as I did for the example above, how might I automate this in R (or Stata)?

Upvotes: 0

Views: 67

Answers (3)

G. Grothendieck
G. Grothendieck

Reputation: 269451

Use scan and reshape with matrix:

s <- "2622731,1387660,2621628,1444522,2619235,1681640" # test data

matrix(scan(text = s, sep = ",", quiet = TRUE), ncol = 2, byrow = TRUE)
##         [,1]    [,2]
## [1,] 2622731 1387660
## [2,] 2621628 1444522
## [3,] 2619235 1681640

Upvotes: 0

Nick Cox
Nick Cox

Reputation: 37208

In Stata the analogue of the accepted R solution might involve split and reshape long. Here is another approach:

* data example 
clear
set obs 1
gen strL data = "2622731,1387660,2621628,1444522,2619235,1681640"

* code for data example 
replace data = subinstr(data, ",", " ", .)
set obs `=wordcount(data)/2' 
gen x = real(word(data[1], 2 * _n - 1))
gen y = real(word(data[1], 2 * _n))

list 

     +---------------------------------------------------------------------+
     |                                            data         x         y |
     |---------------------------------------------------------------------|
  1. | 2622731 1387660 2621628 1444522 2619235 1681640   2622731   1387660 |
  2. |                                                   2621628   1444522 |
  3. |                                                   2619235   1681640 |
     +---------------------------------------------------------------------+

Upvotes: 1

Gregor Thomas
Gregor Thomas

Reputation: 145755

In R:

## Read in your data
## data = readLines("path/to/your_file.txt")
## Should get you something like this (using the example in your Q)
data = "2622731,1387660,2621628,1444522,2619235,1681640"
data = unlist(strsplit(data, ","))
data = matrix(as.numeric(data), ncol = 2, byrow = TRUE)
data
#         [,1]    [,2]
# [1,] 2622731 1387660
# [2,] 2621628 1444522
# [3,] 2619235 1681640

At that point, perhaps

data = as.data.frame(data)
names(data) = c("x", "y")
#         x       y
# 1 2622731 1387660
# 2 2621628 1444522
# 3 2619235 1681640

Upvotes: 1

Related Questions