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.