Databases
StackOverflow2013
Postsdbo
POHow to merge multiple data sets and append comments to a new variable
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to merge multiple data sets and append comments to a new variable
    primarykey
    data
    text
    <p>I have multiple data sets (hundreds) with time series data like this:</p> <pre><code>"File name";"18%MC001.TXT";"V 1.24" "Title comment";"231020124070" "Trigger Time";"'13-04-05 13:53:51" "Ch";"A 1- 1";"A 1- 2";"A 1- 3";"A 1- 4";"A 1- 5";"A 1- 6";"A 1- 7";"A 1- 8";"A 1- 9";"A 1-10";"A 1-11";"A 1-12";"A 1-13";"A 1-14";"A 1-15";"A 2- 1";"A 2- 2";"A 2- 4"; "Mode";"Voltage";"Voltage";"Voltage";"Voltage";"Voltage";"Voltage";"Voltage";"Voltage";"Voltage";"Voltage";"Voltage";"Voltage";"Voltage";"Voltage";"Voltage";"Voltage";"Voltage";"Voltage"; "Range";"10V";"10V";"10V";"10V";"10V";"10V";"10V";"10V";"10V";"10V";"10V";"10V";"10V";"10V";"10V";"10V";"10V";"10V"; "Comment";"Prove1";"Prove1";"Prove2";"Prove2";"Prove3";"Prove3";"Prove4";"Prove4";"Prove5";"Prove5";"Prove6";"Prove6";"Prove7";"Prove7";"Prove8";"Prove8";"Prove9";"Prove9"; "Scaling";"Off";"Off";"Off";"Off";"Off";"Off";"Off";"Off";"Off";"Off";"Off";"Off";"Off";"Off";"Off";"Off";"Off";"Off"; "Ratio";" 1.00000E+00";" 1.00000E+00";" 1.00000E+00";" 1.00000E+00";" 1.00000E+00";" 1.00000E+00";" 1.00000E+00";" 1.00000E+00";" 1.00000E+00";" 1.00000E+00";" 1.00000E+00";" 1.00000E+00";" 1.00000E+00";" 1.00000E+00";" 1.00000E+00";" 1.00000E+00";" 1.00000E+00";" 1.00000E+00"; "Offset";" 0.00000E+00";" 0.00000E+00";" 0.00000E+00";" 0.00000E+00";" 0.00000E+00";" 0.00000E+00";" 0.00000E+00";" 0.00000E+00";" 0.00000E+00";" 0.00000E+00";" 0.00000E+00";" 0.00000E+00";" 0.00000E+00";" 0.00000E+00";" 0.00000E+00";" 0.00000E+00";"-3.00000E+00";"-3.00000E+00"; "Time";"1-1[V]";"1-2[V]";"1-3[V]";"1-4[V]";"1-5[V]";"1-6[V]";"1-7[V]";"1-8[V]";"1-9[V]";"1-10[V]";"1-11[V]";"1-12[V]";"1-13[V]";"1-14[V]";"1-15[V]";"2-1[V]";"2-2[V]";"2-4[V]";"Event"; 0,000000000E+00; 8,69500E-01; 4,80350E+00; 3,76000E-01; 7,34950E+00; 5,60750E+00; 4,66450E+00; 8,31600E+00; 8,13950E+00; 6,66050E+00; 9,69700E+00; 1,81750E+00; 1,10900E+00; 6,82400E+00; 4,04900E+00; 9,82150E+00; 6,98000E+00; 2,94750E+00; 4,08750E+00;0; 1,000000000E+01; 8,69500E-01; 4,80350E+00; 3,76000E-01; 7,34950E+00; 5,60750E+00; 4,66500E+00; 8,31600E+00; 8,13950E+00; 6,66050E+00; 9,69700E+00; 1,81700E+00; 1,10900E+00; 6,82400E+00; 4,04900E+00; 9,82150E+00; 6,98000E+00; 2,94750E+00; 4,08800E+00;0; </code></pre> <p>Each data set has a unique datetime value (<code>Trigger Time</code>) that is treated as a comment. Each data set also has a <code>Time</code> variable indicating the time that has passed since the datetime in <code>Trigger Time</code>. What I want to do is calculate the datetime for each observation so that I can graph the data as a time series using <strong>R</strong> Statistics. Is there a way to accomplish this? Merging the datasets and appending the comments does not necessarily have to be done in <strong>R</strong>. </p> <p>I have imported the data from all files in <strong>R</strong> Statistics using the <code>list.files</code> and <code>llply</code> functions, <a href="http://econometricsense.blogspot.no/2011/01/merging-multiple-data-frames-in-r.html" rel="nofollow noreferrer">as suggested by Matt Bogard in this blog post</a>. Here is a <a href="https://dl.dropboxusercontent.com/u/616262/data-test.zip" rel="nofollow noreferrer">link</a> to the data sets I use in the example below. </p> <pre><code>require(plyr) filenames &lt;- list.files(path = "C:/Users/bys/Desktop/log-trykk-vinkelrett/data-test/", full.names = TRUE) import.list &lt;- llply(filenames, read.csv) </code></pre> <p>Here is a <a href="https://dl.dropboxusercontent.com/u/616262/importlist.txt" rel="nofollow noreferrer">link the output from <code>import.list</code></a> using <code>dput</code>.</p> <p>I think I then need to do something similar to what <a href="https://stackoverflow.com/questions/1562124/merge-many-data-frames-from-csv-files">learnr is suggesting here</a>, but so far my attempts at extracting <code>Trigger Time</code> and adding a new variable with <code>Trigger Time</code> for each observation have been unsuccessful.</p> <p>Solving the problem with Open Refine causes the program to crash every time I try to load all the data sets. <strong>R</strong> may not be the best tool for processing text files, but I don't have any experience with Python, Ruby or similar languages. </p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USkribys
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. POHow to merge multiple data sets and append comments to a new variable
      1. USJack Ryan
      1. VTFavorite
    1. COIf you've successfully imported the data, then it would be much better to give the structure of your data frames as opposed to that of the raw text.
      singulars
      1. POHow to merge multiple data sets and append comments to a new variable
      1. USHong Ooi
    2. COI agree with @HongOoi provide us with a dataset by using the dput command or some other way for us to recreate the data.
      singulars
      1. POHow to merge multiple data sets and append comments to a new variable
      1. USDr. Mike
    3. COUsing `dput`I added the output from import.list (see code I added in edit) to the file [importlist.txt](https://dl.dropboxusercontent.com/u/616262/importlist.txt). I also added a [link](https://dl.dropboxusercontent.com/u/616262/data-test.zip) to the data sets I use in the R code above.
      singulars
      1. POHow to merge multiple data sets and append comments to a new variable
      1. USkribys
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload