An embedded and charset-unspecified text was scrubbed... Name: not available URL: <https://stat.ethz.ch/pipermail/r-help/attachments/20130212/230cb508/attachment.pl>
reorganize data
4 messages · Niklas Larson, David Winsemius, arun
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.