+ Reply to Thread
Results 1 to 7 of 7

create new structure of data/info

  1. #1
    Registered User
    Join Date
    07-08-2010
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    4

    create new structure of data/info

    OK - I have csv files that are raw data for a research study. We no longer have the original software to read the data so we have to find a way to do it in excel.

    It is in this format when opened:

    159A
    11/7/2001
    {start}
    0.00 No Obj
    11.96 E:SimMan
    13.06 E:Indisc
    28.16 No Obj
    30.96 E:Indisc
    52.80 No Obj
    62.60 E:Indisc
    78.83 No Obj
    116.76 E:Indisc
    132.20 No Obj
    137.16 E:Indisc
    141.46 R:PrsGen
    145.50 No Obj
    147.76 E:Indisc
    153.90 R:PrsGen
    157.60 No Obj
    169.03 E:Indisc
    86399.9 {end}
    {notes}


    each of those are on one row, and all info is in one cell.

    I use find and replace to remove the space in No Obj to make it NoObj, then I use "text to columns" to separate the numbers from the text which pulls the two into different cells - that all works perfect.

    Here is what I need next: these data are for an childhood autism study - and each line is an amount of time (in seconds). I need to create a formula that will pull out the data and time in such a way that I can then calculate how many seconds each behavior group had, what the duration of each (and total) were and how much time between each. I have it calculating the difference between the time in the previous cell and creating a new cell value called difference, but I need it to be able to also calculate the grouped (based on behavior type - NoObj (No Object), SimMan (Simple Manipulation), PrsGen, etc)).

    Is there a way to write a script that can automate this process? We have 1000 files like this to go through.

    Any help... I am not an excel programmer, but figured out how to get half of what I need done done, any thoughts on the last half?

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: create new structure of data/info

    Hello jbulluck,

    Welcome to the Forum!

    It would help to see a worksheet with how the data will be arranged once the file is read. Does "No Obj" mark the start of each record?

  3. #3
    Registered User
    Join Date
    07-08-2010
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: create new structure of data/info

    the No Obj is not always the start of the data - it just indicates a period where the child is not manipulating any object. the beginning will always be after the {start}

    Best case scenario, the data would look like this:

    Subjecy 159A
    Behavioral Elements Freq TotDur TotDur% Mean MinDur MaxDur
    =============================================================================================
    No Obj 10 193.8 64.60 19.38 0.9 98.4
    E:Indisc 10 83.6 27.87 8.36 0.8 32.2
    E:SimMan 1 22.6 7.53 22.60 22.6 22.6
    ---------------------------------------------------------------------------------------------
    Total 21 300.0 100.00 14.29 0.8 98.4

    for each file imported - the 159A is in the file and identifies the subject.

    Is this what you mean? or do you want an xls file?

  4. #4
    Registered User
    Join Date
    07-08-2010
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: create new structure of data/info

    text didnt upload well - here is a screenshot of desired output
    Attached Images Attached Images

  5. #5
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: create new structure of data/info

    Hello jbulluck,

    That's a big help. So, Total then will signal the end of a single subjects data, correct? Do you want to separate the records with a single blank row?

  6. #6
    Registered User
    Join Date
    07-08-2010
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: create new structure of data/info

    Total will indicate the end, yes.

    Separate cells would be great, we will use this output to go into SAS for analysis.

  7. #7
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: create new structure of data/info

    Hello jbulluck,

    After looking over the sample data you posted, I have some questions. Rather than post back and forth, you could answer my questions faster by posting the raw data file. There may be some formatting that effects extracting it properly. Or you post a worksheet that has the raw data already downloaded to it.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1