+ Reply to Thread
Results 1 to 6 of 6

Rearranging data to wide format

  1. #1
    Registered User
    Join Date
    10-21-2014
    Location
    California
    MS-Off Ver
    2010
    Posts
    3

    Rearranging data to wide format

    Hi, I'm using a statistical software package that outputs CSV data in a rather inconvenient way.

    What I want:
    Each row begins with a replication number and values follow wide-format

    Here's an example of what I get:

    1
    0.67370292E+01 0.48933600E+01 0.24938692E+01 0.64010965E+00 0.95554989E+00 0.64529389E+00 0.10288117E+01 -0.11998415E+01 -0.10217637E+01 -0.66061860E+00
    -0.10693768E+01 -0.13383297E+01 -0.90759166E+00 -0.10610346E+01 -0.16448403E+01 -0.43982558E+00 -0.49200500E+00 0.83501281E+00 0.11044625E+01 0.99924134E+00
    0.94811180E-01 0.28393248E+00 0.16723945E+00 0.12390488E+00 0.63398487E-01 0.41627670E+00 0.72413019E-01 0.37072348E+00 0.13660030E+00 0.11613150E+00
    0.14758513E+00 0.40413136E+00 0.83235696E-01 0.35414807E+00 0.14862666E+00
    0.31899012E+01 0.95504731E+01 0.17018054E+01 0.47755286E+00 0.46893785E+00 0.44247244E+00 0.45429804E+00 0.51167869E+00 0.30536656E+00 0.26159568E+00
    0.42470785E+00 0.10332376E+01 0.48143038E+00 0.34062220E+00 0.52706194E+00 0.14132764E+00 0.13707879E+00 0.17196387E+00 0.20127777E+00 0.18861005E+00
    0.27607894E-01 0.78456842E-01 0.65354303E-01 0.69868416E-01 0.23339490E+00 0.22177908E+00 0.50802303E-01 0.11640627E+00 0.11409105E+00 0.79970953E-01
    0.23228001E+00 0.20112897E+00 0.44560727E-01 0.70025968E-01 0.00000000E+00
    -0.67436399E+04 0.35000000E+02 0.13557280E+05 0.13729051E+05 0.13617889E+05 0.92043774E+03 0.98800000E+03 0.93832893E+00 0.10116934E+04 0.98800000E+03
    2.93E-01
    2
    0.64398409E+01 0.27339784E+01 0.26310382E+00 -0.24694094E+00 0.84552245E+00 0.92613581E+00 0.31157009E+00 -0.93167573E+00 -0.11823037E+01 -0.12364374E+01
    -0.38954436E+01 -0.91765451E+00 -0.83743680E+00 -0.39578498E+00 -0.48229605E+00 -0.50428860E-01 -0.38118238E+00 0.13455214E+01 0.11957601E+01 0.12587575E+01
    0.85785041E-01 0.25421170E+00 0.26564840E+00 0.18874432E-01 0.97200812E-01 0.34625444E+00 0.15492186E+00 0.42095495E+00 0.18227957E+00 0.13358662E+00
    0.26400418E+00 0.26245028E+00 0.28830143E-01 0.38009399E+00 0.18529190E+00
    0.24573739E+01 0.81442654E+01 0.44858150E+00 0.14665847E+01 0.19198151E+01 0.29955170E+01 0.89590477E+00 0.59144730E+00 0.93239068E+00 0.78042758E+00
    0.36906552E+02 0.18023579E+01 0.81862051E+00 0.51325403E+00 0.68374474E+00 0.31850202E+00 0.46801485E+00 0.41439867E+00 0.32713496E+00 0.22767300E+00
    0.55108380E-01 0.40542722E+00 0.43789443E+00 0.22408566E+00 0.30047294E+00 0.35060933E+00 0.18745351E+00 0.42570175E+00 0.47753864E+00 0.10545555E+00
    0.17082620E+00 0.45398698E+00 0.62821388E-01 0.49485330E+00 0.48282017E+00
    -0.67475870E+04 0.35000000E+02 0.13565174E+05 0.13736945E+05 0.13625783E+05 0.87608304E+03 0.98800000E+03 0.99539870E+00 0.98301634E+03 0.98800000E+03
    5.39E-01


    Each "chunk" or paragraph of data should be one row. Also note that replication numbers are not always consecutive. For example, if there are a total of 100 replications, not all converge and thus some replication numbers are missing.

    How can I easily import or rearrange this data in Excel in the format that I want? Thank you!

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Rearranging data to wide format

    Post an excerpt of your CSV, doesn't have to be the whole file.
    Also post an Excel worksheet showing how that small set of CSV data should look when you import it.


    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    10-21-2014
    Location
    California
    MS-Off Ver
    2010
    Posts
    3

    Re: Rearranging data to wide format

    Attached are the raw data (what the program outputs) and formatted data (what I want it to look like) files.
    Attached Files Attached Files

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP, 2007, 2024
    Posts
    16,369

    Re: Rearranging data to wide format

    What is the statistical package using as a delimiter? It looks to me like it should be a basic space delimited file. How are you opening the file into Excel? If you open using the file-open command, it should be easy enough to parse the data into columns using the "Text to Columns" command (space as the delimiter) on the data group. Would that work for you?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  5. #5
    Registered User
    Join Date
    10-21-2014
    Location
    California
    MS-Off Ver
    2010
    Posts
    3

    Re: Rearranging data to wide format

    It's using space as a delimiter. I wish the solution was as easy as using Text to Columns. It would work if each row of data was in fact one row of data. Instead, what should be one row of data is provided in the out as 10 or so lines in more of a "paragraph" than a row.

  6. #6
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Rearranging data to wide format

    I opened the csv data in Word. Then used Find and Replace in Word.

    Find What: ^p space
    Replace With: space Replace All

    Find What: ^p- had to copy the - and paste with Ctrl + V as it isn't an ordinary -.
    Replace With: space - paste with Ctrl + V

    Find What: ^w
    Replace With: ^t Replace All

    Select all and copy and paste into Excel.

    This is the result.
    Attached Files Attached Files
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Rearranging Datewise horizontally in a specified format from Raw data
    By paradise2sr in forum Excel Formulas & Functions
    Replies: 24
    Last Post: 10-26-2013, 03:28 AM
  2. transforming a long dataset into a wide format
    By dmunte in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-30-2013, 11:42 AM
  3. Chart data range is too wide
    By regularguy in forum Excel General
    Replies: 1
    Last Post: 11-05-2010, 05:41 AM
  4. Rearranging tables into six-column format
    By The_YongGrand in forum Excel - New Users/Basics
    Replies: 10
    Last Post: 11-24-2009, 10:05 AM
  5. Plotting On a Wide Format Roll Plotter
    By Chuck in forum Excel General
    Replies: 0
    Last Post: 04-26-2006, 08:35 PM

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