Hi R users, Wonder if somebody could give me help on how to reshape this type of data: ----------------------------------------------------------------------------------------------------------------------- Date:10.09.19 Time:21:39:05 Lat:N62.37.18 Long:E018.07.32 0000-0010 | 28| 28 0010-0020| 302| 302 0020-0030| 42| 42 0030-0040| 2| 2 0040-0050| 1| 1 0060-0070| 1| 1 _ Date:10.09.19 Time:21:44:52 Lat:N62.38.00 Long:E018.09.07 0000-0010| 32| 32 0010-0020| 334| 334 0020-0030| 27| 27 0030-0040| 2| 2 0070-0080| 1| 1 0080-0090| 1| 1 0090-0100| 1| 1 0100-0110| 1| 1 _ Date:10.09.19 Time:21:50:35 Lat:N62.38.42 Long:E018.10.40 0000-0010| 63| 63 0010-0020| 303| 303 0020-0030| 85| 85 0030-0040| 12| 12 0040-0050| 4| 4 0050-0060| 1| 1 0060-0070| 4| 4 0070-0080| 1| 1 ---------------------------------------------------------------------------------------------- into this: ------------------------------------------------------------------------------------------------- Date:10.09.19 Time:21:39:05 Lat:N62.37.18 Long:E018.07.32 0000-0010| 28| 28 Date:10.09.19 Time:21:39:05 Lat:N62.37.18 Long:E018.07.32 0010-0020| 302| 302 Date:10.09.19 Time:21:39:05 Lat:N62.37.18 Long:E018.07.32 0020-0030| 42| 42 Date:10.09.19 Time:21:39:05 Lat:N62.37.18 Long:E018.07.32 0030-0040| 2| 2 Date:10.09.19 Time:21:39:05 Lat:N62.37.18 Long:E018.07.32 0040-0050| 1| 1 Date:10.09.19 Time:21:39:05 Lat:N62.37.18 Long:E018.07.32 0060-0070| 1| 1 _ Date:10.09.19 Time:21:44:52 Lat:N62.38.00 Long:E018.09.07 0000-0010| 32| 32 Date:10.09.19 Time:21:44:52 Lat:N62.38.00 Long:E018.09.07 0010-0020| 334| 334 Date:10.09.19 Time:21:44:52 Lat:N62.38.00 Long:E018.09.07 0020-0030| 27| 27 Date:10.09.19 Time:21:44:52 Lat:N62.38.00 Long:E018.09.07 0030-0040| 2| 2 Date:10.09.19 Time:21:44:52 Lat:N62.38.00 Long:E018.09.07 0070-0080| 1| 1 Date:10.09.19 Time:21:44:52 Lat:N62.38.00 Long:E018.09.07 0080-0090| 1| 1 Date:10.09.19 Time:21:44:52 Lat:N62.38.00 Long:E018.09.07 0090-0100| 1| 1 Date:10.09.19 Time:21:44:52 Lat:N62.38.00 Long:E018.09.07 0100-0110| 1| 1 _ Date:10.09.19 Time:21:50:35 Lat:N62.38.42 Long:E018.10.40 0000-0010| 63| 63 Date:10.09.19 Time:21:50:35 Lat:N62.38.42 Long:E018.10.40 0010-0020| 303| 303 Date:10.09.19 Time:21:50:35 Lat:N62.38.42 Long:E018.10.40 0020-0030| 85| 85 Date:10.09.19 Time:21:50:35 Lat:N62.38.42 Long:E018.10.40 0030-0040| 12| 12 Date:10.09.19 Time:21:50:35 Lat:N62.38.42 Long:E018.10.40 0040-0050| 4| 4 Date:10.09.19 Time:21:50:35 Lat:N62.38.42 Long:E018.10.40 0050-0060| 1| 1 Date:10.09.19 Time:21:50:35 Lat:N62.38.42 Long:E018.10.40 0060-0070| 4| 4 Date:10.09.19 Time:21:50:35 Lat:N62.38.42 Long:E018.10.40 0070-0080| 1| 1 --------------------------------------------------------------------------------------------------------------------------------- Thanx /Nic [[alternative HTML version deleted]]
On Feb 12, 2013, at 2:56 AM, Niklas Larson wrote:> Hi R users, > Wonder if somebody could give me help on how to reshape this type of data: >#Adding a bit of code to read this into R: Lines <- readLines(textConnection("Date:10.09.19 Time:21:39:05 Lat:N62.37.18 Long:E018.07.32 0000-0010 | 28| 28 0010-0020| 302| 302 0020-0030| 42| 42 0030-0040| 2| 2 0040-0050| 1| 1 0060-0070| 1| 1 _ Date:10.09.19 Time:21:44:52 Lat:N62.38.00 Long:E018.09.07 0000-0010| 32| 32 0010-0020| 334| 334 0020-0030| 27| 27 0030-0040| 2| 2 0070-0080| 1| 1 0080-0090| 1| 1 0090-0100| 1| 1 0100-0110| 1| 1 _ Date:10.09.19 Time:21:50:35 Lat:N62.38.42 Long:E018.10.40 0000-0010| 63| 63 0010-0020| 303| 303 0020-0030| 85| 85 0030-0040| 12| 12 0040-0050| 4| 4 0050-0060| 1| 1 0060-0070| 4| 4 0070-0080| 1| 1"))> > ---------------------------------------------------------------------------------------------- > > into this: > > ------------------------------------------------------------------------------------------------- > Date:10.09.19 Time:21:39:05 Lat:N62.37.18 Long:E018.07.32 0000-0010| 28| > 28 > Date:10.09.19 Time:21:39:05 Lat:N62.37.18 Long:E018.07.32 0010-0020| 302| > 302 > Date:10.09.19 Time:21:39:05 Lat:N62.37.18 Long:E018.07.32 0020-0030| 42| > 42 > > Date:10.09.19 Time:21:50:35 Lat:N62.38.42 Long:E018.10.40 0070-0080| 1| > 1 >Snipped This should get you started along one possible path. Still to be done would be removing the lines that only had dashes and perhaps parsing the headers. data.frame(dattimeLatlon = Lines[rep( grep("Date", Lines), each=diff(numdt))] , vals=Lines[ grep("^[^D]", Lines) ] ) #---------- dattimeLatlon vals 1 Date:10.09.19 Time:21:39:05 Lat:N62.37.18 Long:E018.07.32 0000-0010 | 28| 28 2 Date:10.09.19 Time:21:39:05 Lat:N62.37.18 Long:E018.07.32 0010-0020| 302| 302 3 Date:10.09.19 Time:21:39:05 Lat:N62.37.18 Long:E018.07.32 0020-0030| 42| 42 4 Date:10.09.19 Time:21:39:05 Lat:N62.37.18 Long:E018.07.32 0030-0040| 2| 2 5 Date:10.09.19 Time:21:39:05 Lat:N62.37.18 Long:E018.07.32 0040-0050| 1| 1 6 Date:10.09.19 Time:21:39:05 Lat:N62.37.18 Long:E018.07.32 0060-0070| 1| 1 7 Date:10.09.19 Time:21:39:05 Lat:N62.37.18 Long:E018.07.32 _ 8 Date:10.09.19 Time:21:39:05 Lat:N62.37.18 Long:E018.07.32 0000-0010| 32| 32 9 Date:10.09.19 Time:21:44:52 Lat:N62.38.00 Long:E018.09.07 0010-0020| 334| 334 10 Date:10.09.19 Time:21:44:52 Lat:N62.38.00 Long:E018.09.07 0020-0030| 27| 27 11 Date:10.09.19 Time:21:44:52 Lat:N62.38.00 Long:E018.09.07 0030-0040| 2| 2 12 Date:10.09.19 Time:21:44:52 Lat:N62.38.00 Long:E018.09.07 0070-0080| 1| 1 13 Date:10.09.19 Time:21:44:52 Lat:N62.38.00 Long:E018.09.07 0080-0090| 1| 1 14 Date:10.09.19 Time:21:44:52 Lat:N62.38.00 Long:E018.09.07 0090-0100| 1| 1 15 Date:10.09.19 Time:21:44:52 Lat:N62.38.00 Long:E018.09.07 0100-0110| 1| 1 16 Date:10.09.19 Time:21:44:52 Lat:N62.38.00 Long:E018.09.07 _ 17 Date:10.09.19 Time:21:50:35 Lat:N62.38.42 Long:E018.10.40 0000-0010| 63| 63 18 Date:10.09.19 Time:21:50:35 Lat:N62.38.42 Long:E018.10.40 0010-0020| 303| 303 19 Date:10.09.19 Time:21:50:35 Lat:N62.38.42 Long:E018.10.40 0020-0030| 85| 85 20 Date:10.09.19 Time:21:50:35 Lat:N62.38.42 Long:E018.10.40 0030-0040| 12| 12 21 Date:10.09.19 Time:21:50:35 Lat:N62.38.42 Long:E018.10.40 0040-0050| 4| 4 22 Date:10.09.19 Time:21:50:35 Lat:N62.38.42 Long:E018.10.40 0050-0060| 1| 1 23 Date:10.09.19 Time:21:50:35 Lat:N62.38.42 Long:E018.10.40 0060-0070| 4| 4 24 Date:10.09.19 Time:21:50:35 Lat:N62.38.42 Long:E018.10.40 0070-0080| 1| 1 David Winsemius Alameda, CA, USA
Hi,
Lines1 <- readLines(textConnection("Date:10.09.19 Time:21:39:05?
Lat:N62.37.18 Long:E018.07.32
0000-0010 |?? 28|?? 28
0010-0020|? 302|? 302
0020-0030|?? 42|?? 42
0030-0040|??? 2|??? 2
0040-0050|??? 1|??? 1
0060-0070|??? 1|??? 1
_
Date:10.09.19 Time:21:44:52 Lat:N62.38.00 Long:E018.09.07
0000-0010|?? 32|?? 32
0010-0020|? 334|? 334
0020-0030|?? 27|?? 27
0030-0040|??? 2|??? 2
0070-0080|??? 1|??? 1
0080-0090|??? 1|??? 1
0090-0100|??? 1|??? 1
0100-0110|??? 1|??? 1
_
Date:10.09.19 Time:21:50:35 Lat:N62.38.42 Long:E018.10.40
0000-0010|?? 63|?? 63
0010-0020|? 303|? 303
0020-0030|?? 85|?? 85
0030-0040|?? 12|?? 12
0040-0050|??? 4|??? 4
0050-0060|??? 1|??? 1
0060-0070|??? 4|??? 4
0070-0080|??? 1|??? 1"))
Lines2<- Lines1[Lines1!="_"]
indx<- grep("Date",Lines2)
?dat1<- data.frame(
Lines2[rep(indx,diff(c(indx,length(Lines2)+1))-1)],read.table(text=gsub("^\\s+
","",gsub("\\|","",gsub("^\\d+-\\d+|\\s+(.*)","\\1",Lines2[-indx]))),sep="",header=FALSE),stringsAsFactors=FALSE)
?names(dat1)[1]<- "Date_Lat_Long"
head(dat1)
?????????????????????????????????????????????? Date_Lat_Long? V1? V2
#1 Date:10.09.19 Time:21:39:05? Lat:N62.37.18 Long:E018.07.32? 28? 28
#2 Date:10.09.19 Time:21:39:05? Lat:N62.37.18 Long:E018.07.32 302 302
#3 Date:10.09.19 Time:21:39:05? Lat:N62.37.18 Long:E018.07.32? 42? 42
#4 Date:10.09.19 Time:21:39:05? Lat:N62.37.18 Long:E018.07.32?? 2?? 2
#5 Date:10.09.19 Time:21:39:05? Lat:N62.37.18 Long:E018.07.32?? 1?? 1
#6 Date:10.09.19 Time:21:39:05? Lat:N62.37.18 Long:E018.07.32?? 1?? 1
A.K.
----- Original Message -----
From: Niklas Larson <niklar at gmail.com>
To: r-help at r-project.org
Cc:
Sent: Tuesday, February 12, 2013 5:56 AM
Subject: [R] reorganize data
Hi R users,
Wonder if somebody could give me help on how to reshape this type of data:
-----------------------------------------------------------------------------------------------------------------------
Date:10.09.19 Time:21:39:05? Lat:N62.37.18 Long:E018.07.32
0000-0010 |? 28|? 28
0010-0020|? 302|? 302
0020-0030|? 42|? 42
0030-0040|? ? 2|? ? 2
0040-0050|? ? 1|? ? 1
0060-0070|? ? 1|? ? 1
_
Date:10.09.19 Time:21:44:52 Lat:N62.38.00 Long:E018.09.07
0000-0010|? 32|? 32
0010-0020|? 334|? 334
0020-0030|? 27|? 27
0030-0040|? ? 2|? ? 2
0070-0080|? ? 1|? ? 1
0080-0090|? ? 1|? ? 1
0090-0100|? ? 1|? ? 1
0100-0110|? ? 1|? ? 1
_
Date:10.09.19 Time:21:50:35 Lat:N62.38.42 Long:E018.10.40
0000-0010|? 63|? 63
0010-0020|? 303|? 303
0020-0030|? 85|? 85
0030-0040|? 12|? 12
0040-0050|? ? 4|? ? 4
0050-0060|? ? 1|? ? 1
0060-0070|? ? 4|? ? 4
0070-0080|? ? 1|? ? 1
----------------------------------------------------------------------------------------------
into this:
-------------------------------------------------------------------------------------------------
Date:10.09.19 Time:21:39:05 Lat:N62.37.18 Long:E018.07.32 0000-0010|? 28|
? 28
Date:10.09.19 Time:21:39:05 Lat:N62.37.18 Long:E018.07.32 0010-0020|? 302|
302
Date:10.09.19 Time:21:39:05 Lat:N62.37.18 Long:E018.07.32 0020-0030|? 42|
? 42
Date:10.09.19 Time:21:39:05 Lat:N62.37.18 Long:E018.07.32 0030-0040|? ? 2|
? 2
Date:10.09.19 Time:21:39:05 Lat:N62.37.18 Long:E018.07.32 0040-0050|? ? 1|
? 1
Date:10.09.19 Time:21:39:05 Lat:N62.37.18 Long:E018.07.32 0060-0070|? ? 1|
? 1
_
Date:10.09.19 Time:21:44:52 Lat:N62.38.00 Long:E018.09.07 0000-0010|? 32|
? 32
Date:10.09.19 Time:21:44:52 Lat:N62.38.00 Long:E018.09.07 0010-0020|? 334|
334
Date:10.09.19 Time:21:44:52 Lat:N62.38.00 Long:E018.09.07 0020-0030|? 27|
? 27
Date:10.09.19 Time:21:44:52 Lat:N62.38.00 Long:E018.09.07 0030-0040|? ? 2|
? 2
Date:10.09.19 Time:21:44:52 Lat:N62.38.00 Long:E018.09.07 0070-0080|? ? 1|
? 1
Date:10.09.19 Time:21:44:52 Lat:N62.38.00 Long:E018.09.07 0080-0090|? ? 1|
? 1
Date:10.09.19 Time:21:44:52 Lat:N62.38.00 Long:E018.09.07 0090-0100|? ? 1|
? 1
Date:10.09.19 Time:21:44:52 Lat:N62.38.00 Long:E018.09.07 0100-0110|? ? 1|
? 1
_
Date:10.09.19 Time:21:50:35 Lat:N62.38.42 Long:E018.10.40 0000-0010|? 63|
? 63
Date:10.09.19 Time:21:50:35 Lat:N62.38.42 Long:E018.10.40 0010-0020|? 303|
303
Date:10.09.19 Time:21:50:35 Lat:N62.38.42 Long:E018.10.40 0020-0030|? 85|
? 85
Date:10.09.19 Time:21:50:35 Lat:N62.38.42 Long:E018.10.40 0030-0040|? 12|
? 12
Date:10.09.19 Time:21:50:35 Lat:N62.38.42 Long:E018.10.40 0040-0050|? ? 4|
? 4
Date:10.09.19 Time:21:50:35 Lat:N62.38.42 Long:E018.10.40 0050-0060|? ? 1|
? 1
Date:10.09.19 Time:21:50:35 Lat:N62.38.42 Long:E018.10.40 0060-0070|? ? 4|
? 4
Date:10.09.19 Time:21:50:35 Lat:N62.38.42 Long:E018.10.40 0070-0080|? ? 1|
? 1
---------------------------------------------------------------------------------------------------------------------------------
Thanx
/Nic
??? [[alternative HTML version deleted]]
______________________________________________
R-help at r-project.org mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
Hi,
Sorry, in my previous reply, I omitted one of the columns.
Lines1 <- readLines(textConnection("Date:10.09.19 Time:21:39:05?
Lat:N62.37.18 Long:E018.07.32
0000-0010 |?? 28|?? 28
0010-0020|? 302|? 302
0020-0030|?? 42|?? 42
0030-0040|??? 2|??? 2
0040-0050|??? 1|??? 1
0060-0070|??? 1|??? 1
_
Date:10.09.19 Time:21:44:52 Lat:N62.38.00 Long:E018.09.07
0000-0010|?? 32|?? 32
0010-0020|? 334|? 334
0020-0030|?? 27|?? 27
0030-0040|??? 2|??? 2
0070-0080|??? 1|??? 1
0080-0090|??? 1|??? 1
0090-0100|??? 1|??? 1
0100-0110|??? 1|??? 1
_
Date:10.09.19 Time:21:50:35 Lat:N62.38.42 Long:E018.10.40
0000-0010|?? 63|?? 63
0010-0020|? 303|? 303
0020-0030|?? 85|?? 85
0030-0040|?? 12|?? 12
0040-0050|??? 4|??? 4
0050-0060|??? 1|??? 1
0060-0070|??? 4|??? 4
0070-0080|??? 1|??? 1"))
Lines2<- Lines1[Lines1!="_"]?
indx<- grep("Date",Lines2)
#If you need the `values` as separate columns
dat1<-data.frame(Lines2[rep(indx,diff(c(indx,length(Lines2)+1))-1)],read.table(text=Lines2[-indx],sep="|",stringsAsFactors=FALSE),stringsAsFactors=FALSE)
?names(dat1)[1]<- "Date_Lat_Long"
head(dat1,3)
#?????????????????????????????????????????????? Date_Lat_Long???????? V1? V2? V3
#1 Date:10.09.19 Time:21:39:05? Lat:N62.37.18 Long:E018.07.32 0000-0010?? 28? 28
#2 Date:10.09.19 Time:21:39:05? Lat:N62.37.18 Long:E018.07.32? 0010-0020 302 302
#3 Date:10.09.19 Time:21:39:05? Lat:N62.37.18 Long:E018.07.32? 0020-0030? 42? 42
#Or? need it in the format as specified by you:
dat2<-
data.frame(Date_Lat_Long=Lines2[rep(indx,diff(c(indx,length(Lines2)+1))-1)],val=Lines2[-indx],stringsAsFactors=FALSE)
head(dat2,3)
#?????????????????????????????????????????????? Date_Lat_Long
#1 Date:10.09.19 Time:21:39:05? Lat:N62.37.18 Long:E018.07.32
#2 Date:10.09.19 Time:21:39:05? Lat:N62.37.18 Long:E018.07.32
#3 Date:10.09.19 Time:21:39:05? Lat:N62.37.18 Long:E018.07.32
#???????????????????? val
#1 0000-0010 |?? 28|?? 28
#2? 0010-0020|? 302|? 302
#3? 0020-0030|?? 42|?? 42
A.K.
----- Original Message -----
From: Niklas Larson <niklar at gmail.com>
To: r-help at r-project.org
Cc:
Sent: Tuesday, February 12, 2013 5:56 AM
Subject: [R] reorganize data
Hi R users,
Wonder if somebody could give me help on how to reshape this type of data:
-----------------------------------------------------------------------------------------------------------------------
Date:10.09.19 Time:21:39:05? Lat:N62.37.18 Long:E018.07.32
0000-0010 |? 28|? 28
0010-0020|? 302|? 302
0020-0030|? 42|? 42
0030-0040|? ? 2|? ? 2
0040-0050|? ? 1|? ? 1
0060-0070|? ? 1|? ? 1
_
Date:10.09.19 Time:21:44:52 Lat:N62.38.00 Long:E018.09.07
0000-0010|? 32|? 32
0010-0020|? 334|? 334
0020-0030|? 27|? 27
0030-0040|? ? 2|? ? 2
0070-0080|? ? 1|? ? 1
0080-0090|? ? 1|? ? 1
0090-0100|? ? 1|? ? 1
0100-0110|? ? 1|? ? 1
_
Date:10.09.19 Time:21:50:35 Lat:N62.38.42 Long:E018.10.40
0000-0010|? 63|? 63
0010-0020|? 303|? 303
0020-0030|? 85|? 85
0030-0040|? 12|? 12
0040-0050|? ? 4|? ? 4
0050-0060|? ? 1|? ? 1
0060-0070|? ? 4|? ? 4
0070-0080|? ? 1|? ? 1
----------------------------------------------------------------------------------------------
into this:
-------------------------------------------------------------------------------------------------
Date:10.09.19 Time:21:39:05 Lat:N62.37.18 Long:E018.07.32 0000-0010|? 28|
? 28
Date:10.09.19 Time:21:39:05 Lat:N62.37.18 Long:E018.07.32 0010-0020|? 302|
302
Date:10.09.19 Time:21:39:05 Lat:N62.37.18 Long:E018.07.32 0020-0030|? 42|
? 42
Date:10.09.19 Time:21:39:05 Lat:N62.37.18 Long:E018.07.32 0030-0040|? ? 2|
? 2
Date:10.09.19 Time:21:39:05 Lat:N62.37.18 Long:E018.07.32 0040-0050|? ? 1|
? 1
Date:10.09.19 Time:21:39:05 Lat:N62.37.18 Long:E018.07.32 0060-0070|? ? 1|
? 1
_
Date:10.09.19 Time:21:44:52 Lat:N62.38.00 Long:E018.09.07 0000-0010|? 32|
? 32
Date:10.09.19 Time:21:44:52 Lat:N62.38.00 Long:E018.09.07 0010-0020|? 334|
334
Date:10.09.19 Time:21:44:52 Lat:N62.38.00 Long:E018.09.07 0020-0030|? 27|
? 27
Date:10.09.19 Time:21:44:52 Lat:N62.38.00 Long:E018.09.07 0030-0040|? ? 2|
? 2
Date:10.09.19 Time:21:44:52 Lat:N62.38.00 Long:E018.09.07 0070-0080|? ? 1|
? 1
Date:10.09.19 Time:21:44:52 Lat:N62.38.00 Long:E018.09.07 0080-0090|? ? 1|
? 1
Date:10.09.19 Time:21:44:52 Lat:N62.38.00 Long:E018.09.07 0090-0100|? ? 1|
? 1
Date:10.09.19 Time:21:44:52 Lat:N62.38.00 Long:E018.09.07 0100-0110|? ? 1|
? 1
_
Date:10.09.19 Time:21:50:35 Lat:N62.38.42 Long:E018.10.40 0000-0010|? 63|
? 63
Date:10.09.19 Time:21:50:35 Lat:N62.38.42 Long:E018.10.40 0010-0020|? 303|
303
Date:10.09.19 Time:21:50:35 Lat:N62.38.42 Long:E018.10.40 0020-0030|? 85|
? 85
Date:10.09.19 Time:21:50:35 Lat:N62.38.42 Long:E018.10.40 0030-0040|? 12|
? 12
Date:10.09.19 Time:21:50:35 Lat:N62.38.42 Long:E018.10.40 0040-0050|? ? 4|
? 4
Date:10.09.19 Time:21:50:35 Lat:N62.38.42 Long:E018.10.40 0050-0060|? ? 1|
? 1
Date:10.09.19 Time:21:50:35 Lat:N62.38.42 Long:E018.10.40 0060-0070|? ? 4|
? 4
Date:10.09.19 Time:21:50:35 Lat:N62.38.42 Long:E018.10.40 0070-0080|? ? 1|
? 1
---------------------------------------------------------------------------------------------------------------------------------
Thanx
/Nic
??? [[alternative HTML version deleted]]
______________________________________________
R-help at r-project.org mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.