Skip navigation

Re-arrange a list of lists in AppleScript

455 Views 14 Replies Latest reply: Apr 12, 2012 5:20 PM by Frank Caggiano RSS
waggy-d Level 1 Level 1 (0 points)
Currently Being Moderated
Apr 12, 2012 12:32 PM

Ok, so I've got a task that I can't seem to figure out. Here is an example of the problem; please note that I don't need a solution to this specific data, but a solution to the type of data as the list may grow and change over time.

 

I have a list of lists:

{{"Company 1", "Email 1", "First 1", "Last 1", "Title 1", "Artist 1"}, {"Company 1", "Email 1", "First 1", "Last 1", "Title 2", "Artist 2"}, {"Company 2", "Email 2", "First 2", "Last 2", "Title 3", "Artist 3"}, {"Company 3", "Email 3", "First 3", "Last 3", "Title 4", "Artist 4"}, {"Company 4", "Email 4", "First 4", "Last 4", "Title 5", "Artist 5"}, {"Company 4", "Email 4", "First 4", "Last 4", "Title 6", "Artist 6"}, {"Company 4", "Email 4", "First 4", "Last 4", "Title 7", "Artist 7"}}

 

 

Here is the same list in grid format as it may make it easier to read: Each row represents a list within the list.

 

Company 1Email 1First 1Last 1Title 1Artist 1
Company 1Email 1First 1Last 1Title 2Artist 2
Company 2Email 2First 2Last 2Title 3Artist 3
Company 3Email 3First 3Last 3Title 4Artist 4
Company 4Email 4First 4Last 4Title 5Artist 5
Company 4Email 4First 4Last 4Title 6Artist 6
Company 4Email 4First 4Last 4Title 7Artist 7

 

 

 

What I need is for it to be re-worked into a list of lists as such:

{{{"Company 1", "Email 1", "First 1", "Last 1"}, {{"Title 1", "Artist 1"}, {"Title 2", "Artist 2"}}}, {{"Company 2", "Email 2", "First 2", "Last 2"}, {{"Title 3", "Artist 3"}}}, {{"Company 3", 'Email 3", "First 3", "Last 3"}, {{"Title 4", "Artist 4"}}}, {{"Company 4", "Email 4", "First 4", "Last 4"}, {{"Title 5", "Artist 5"}, {"Title 6", "Artist 6"}, {"Title 7", "Artist 6"}}}

 

Here is somewhat of a graphical explination of how the list of lists would end up:

Header 1Header 2Header 3Header 4Header 5Header 6
Company 1Email 1First 1Last 1Title 1Artist 1




Title 2Artist 2
Company 2

Email 2

First 2Last 2Title 3Artist 3
Company 3Email 3First 3Last 3Title 4Artist 4
Company 4Email 4First 4Last 4Title 5Artist 5




Title 6Artist 6




Title 7Artist 7

 

So each unique "Company" along with it's contact info would only be once and then along with it's associated song titles/artists.

 

Any help would really be appreciated as I just haven't been able to nail it down.

 

Thanks

MacBook Pro, Mac OS X (10.7.2)
  • red_menace Level 6 Level 6 (14,275 points)
    Currently Being Moderated
    Apr 12, 2012 1:40 PM (in response to waggy-d)

    Sounds like you need a list of records, then you can rearrange the properties as needed - for example:

     

    set theList to {¬
       {company:"Company 1", email:"Email 1", firstName:"First 1", lastName:"Last 1", songs:{{"Title 1", "Artist 1"}, {"Title 2", "Artist 2"}}}, ¬
       {company:"Company 2", email:"Email 2", firstName:"First 2", lastName:"Last 2", songs:{{"Title 3", "Artist 3"}, {"Title 4", "Artist 4"}}}}

    get songs of second item of theList
  • Frank Caggiano Level 7 Level 7 (22,770 points)
    Currently Being Moderated
    Apr 12, 2012 2:08 PM (in response to waggy-d)

    Just had to try this! With a lot of help from Learn AppleScript by Apress

     

    set companyList to {{"Company 1", "Email 1", "First 1", "Last 1", "Title 1", "Artist 1"}, {"Company 1", "Email 1", "First 1", "Last 1", "Title 2", "Artist 2"}, {"Company 2", "Email 2", "First 2", "Last 2", "Title 3", "Artist 3"}, {"Company 3", "Email 3", "First 3", "Last 3", "Title 4", "Artist 4"}, {"Company 4", "Email 4", "First 4", "Last 4", "Title 5", "Artist 5"}, {"Company 4", "Email 4", "First 4", "Last 4", "Title 6", "Artist 6"}, {"Company 4", "Email 4", "First 4", "Last 4", "Title 7", "Artist 7"}}
    
    -- {company:<text>, firstName: <text>, lastName: <text>,email:<text>,extra:{list of records}}
    set companyInfoRecordList to {}
    set extraList to {}
    
    
    repeat with eachCompany in companyList
              set compInfo to missing value
              set extraList to {}
              set companyName to item 1 of eachCompany
    
              repeat with eachCompanyInfoRecord in companyInfoRecordList
                        if the company of eachCompanyInfoRecord = companyName then
                                  set compInfo to eachCompanyInfoRecord
                                  exit repeat
                        end if
              end repeat
    
              set title to item 5 of eachCompany
              set artist to item 6 of eachCompany
              set ta to {title:title, artist:artist}
    
              if compInfo = missing value then
                        set end of extraList to ta
                        set fn to item 3 of eachCompany
                        set ln to item 4 of eachCompany
                        set email to item 2 of eachCompany
                        set compInfo to {company:companyName, firstname:fn, lastname:ln, email:email, extra:extraList}
                        set end of companyInfoRecordList to compInfo
      --display dialog compInfo
              else
                        get extra of compInfo
                        set end of result to ta
      --set extra of compInfo to ex
              end if
    
    end repeat
    
    companyInfoRecordList
    

     

    MAke sure you beat on this if you use it (or it's concepts) It seemst o work ok for the data you supplied but there is no error checking.

    iMac 3.06ghz 8gb 1 TB, Mac OS X (10.7.3), Aperture 3.2.3
  • Camelot Level 8 Level 8 (45,670 points)
    Currently Being Moderated
    Apr 12, 2012 2:23 PM (in response to waggy-d)

    Transposing your data from Excel to AppleScript records is far from trivial. In either case it wouldn't facilitate outputting the data in the way you describe, although it might make querying the data easier... depends on what your actual goal is. Let me explain that.

     

    If your goal is to output the data in the way you show, with blank fields for the repetitive data, then records aren't going to help since each entry ("row") would either still have a Company field containing data, or would have a Company field that's blank and you'd have to perform additional lookups to find the preceeding, non-blank company. E.g. what's the 'company' for item 7 in your list? that requires a lookup to see the Company in rows 6 and row 5. ick.

    However, if your goal is to query the data then records would help since you could ask for 'Titles of theData whose company = "XYZ Inc".

     

    If you want the blank fields then you're going to need to iterate through the data making changes as needed - checking each row against the preceeding on. This will pretty much demand the lists be sorted otherwise it's going to get unwieldly (e.g. if rows 2 and rows 10 contain the same 'Comany', but rows 3 through 9 are different, do you reorder the list, moving record 10 to record 3? or list the same Company twice?

     

    Ultimately, though, this is probably better done in Excel. A little Excel function magic could easily sort this data to suppress duplicate data and give you clean data going into your script. Sometimes AppleScript isn't the right tool for the job.

  • Frank Caggiano Level 7 Level 7 (22,770 points)
    Currently Being Moderated
    Apr 12, 2012 2:32 PM (in response to Frank Caggiano)

    Result:

    {{company:"Company 1", firstname:"First 1", lastname:"Last 1", email:"Email 1", extra:{{title:"Title 1", artist:"Artist 1"}, {title:"Title 2", artist:"Artist 2"}}}, {company:"Company 2", firstname:"First 2", lastname:"Last 2", email:"Email 2", extra:{{title:"Title 3", artist:"Artist 3"}}}, {company:"Company 3", firstname:"First 3", lastname:"Last 3", email:"Email 3", extra:{{title:"Title 4", artist:"Artist 4"}}}, {company:"Company 4", firstname:"First 4", lastname:"Last 4", email:"Email 4", extra:{{title:"Title 5", artist:"Artist 5"}, {title:"Title 6", artist:"Artist 6"}, {title:"Title 7", artist:"Artist 7"}}}}

     

    Forgot to add the what the list of records will look like.

  • red_menace Level 6 Level 6 (14,275 points)
    Currently Being Moderated
    Apr 12, 2012 2:48 PM (in response to waggy-d)

    Regular AppleScript is a bit clumsy when dealing with record properties, but once you get the information into the record you can search and rearrange as needed - as mentioned, it depends on what you are trying to do.  .  Note that by using a Cocoa-AppleScript application you can use additional methods for accessing the various keys and values.

     

    My script to put the original list into a record is a little bit shorter than Frank Caggiano's:

     

              set originalList to {{"Company 1", "Email 1", "First 1", "Last 1", "Title 1", "Artist 1"}, {"Company 1", "Email 1", "First 1", "Last 1", "Title 2", "Artist 2"}, {"Company 2", "Email 2", "First 2", "Last 2", "Title 3", "Artist 3"}, {"Company 3", "Email 3", "First 3", "Last 3", "Title 4", "Artist 4"}, {"Company 4", "Email 4", "First 4", "Last 4", "Title 5", "Artist 5"}, {"Company 4", "Email 4", "First 4", "Last 4", "Title 6", "Artist 6"}, {"Company 4", "Email 4", "First 4", "Last 4", "Title 7", "Artist 7"}}

    set recordList to {} -- this will be a list of records

    repeat with aListItem in originalList
       set existing to false
       tell aListItem to set newRecord to {company:(item 1), email:(item 2), firstName:(item 3), lastName:(item 4), songs:{items 5 thru 6}}
       repeat with recordItem in recordList -- check for existing record
          if newRecord's company is equal to recordItem's company then
             set end of recordItem's songs to first item of newRecord's songs -- just add the songs
             set existing to true
             exit repeat
          end if
       end repeat
       if not existing then set end of recordList to newRecord
    end repeat

    recordList
  • red_menace Level 6 Level 6 (14,275 points)
    Currently Being Moderated
    Apr 12, 2012 2:52 PM (in response to waggy-d)

    You can't use a filter reference form with lists and records, it can only be used with application objects.  Using regular AppleScript you are more or less limited to stepping through your list (more options are available using Cocoa).

  • Frank Caggiano Level 7 Level 7 (22,770 points)
    Currently Being Moderated
    Apr 12, 2012 2:57 PM (in response to waggy-d)

    This would return the titles for Company 4

     

    repeat with eachCompany in companyInfoRecordList
              if company of eachCompany is "Company 4" then
                        set foo to extra of eachCompany
                        exit repeat
              end if
    end repeat
    
    
    foo
    
    iMac 3.06ghz 8gb 1 TB, Mac OS X (10.7.3), Aperture 3.2.3
  • Frank Caggiano Level 7 Level 7 (22,770 points)
    Currently Being Moderated
    Apr 12, 2012 3:07 PM (in response to red_menace)

    sweet. Of course if you;re gonna use all that fancy Applescript stuff of course it will be smaller!

     

    One thing has me perplexed:

     

    set end of recordItem's songs to first item of newRecord's songs -- just add the songs

     

     

    Why the first item of songs and not just songs?

  • red_menace Level 6 Level 6 (14,275 points)
    Currently Being Moderated
    Apr 12, 2012 3:23 PM (in response to Frank Caggiano)

    The songs property is a list of lists, e.g. {{"Title 1", "Artist 1"}, {"Title 2", "Artist 2"}}, so just getting the first item prevents the list from getting an extra layer, e.g.

    {{{"Title 1", "Artist 1"}}, {{"Title 2", "Artist 2"}}}.

     

     

    Sorry about using the AppleScript...

  • Frank Caggiano Level 7 Level 7 (22,770 points)
    Currently Being Moderated
    Apr 12, 2012 3:31 PM (in response to red_menace)

    Thanks much,

     

    I'm having strange Lisp flashbacks! Where's CAR and CDR when you need them.

  • Frank Caggiano Level 7 Level 7 (22,770 points)
    Currently Being Moderated
    Apr 12, 2012 5:20 PM (in response to waggy-d)

    red_menace is the wizard, I'm only the sorcerer's apprentice but thanks for the solve anyway.

     

     

    BTW if you're looking to get into Applescript there are a lot of good resources on the web and in print. The book I mentioned in my first post Learn Applescript by Apress has been for me anyway a great resource. Just the right mix of theory and practical information. I highly recommend it.

     

    regards

Actions

More Like This

  • Retrieving data ...

Bookmarked By (0)

Legend

  • This solved my question - 10 points
  • This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.