Challenge #116: A Symphony of Parsing Tools!

A solution for last week's challenge has been posted here!

The NY Philharmonic, as various ensembles, has been performing for audiences around the world for over 175 years! Wow! This week's Challenge asks you to parse the data for each of their programs (not for the past 175 years...that file was HUGE!) from 2011 - 2017. For each program, identify the concert information (Date, Location, Time, etc), as well as the pieces played during that program and the solo performers (if applicable). Note: the posted solution has removed records representing an intermission.

challenge_116_start_file.v2yxmd.yxmd

challenge_116_solution.yxmd

Developer

Parse

Join

Preparation

Data Analysis

Transform

Expert

Difficult

Accepted answers

All comments

mmongeon

I think there is an issue with the output provided, it doesn't match what I see in the XML.

See the Performance Date in the screenshot below:

issue with output.JPG

ChristineB

Hmmm...let me investigate! Thanks for letting me know @mmongeon

mmongeon

Here is my solution.

I'm actually getting more data than was provided in the given Output, but, from the spot checks I've performed, I think my additional records are valid.

Spoiler

There may be easier solutions to get the data out of the children levels... but it works.

workflow 116.JPG

challenge_116_mongeon_complete.yxmd

ChristineB

Another good catch @joe_yang! I've updated the start file again. Also, I think I found a point of discrepancy: I opted to remove the records containing "Intermission" in the column "interval". That means I removed records that @mmongeon's solution includes (and that are included in the original xml file). I'm loving all the intense data investigation this Challenge requires (especially on under-caffeinated Mondays....)!

ivoller

I didn't get exactly the same output. Saw that there were some changes and that Intermissions were to be excluded. Couldn't face going back again. My solution is messy and I should have summarized earlier in the process.

Spoiler

@mmongeon solution is prettier and almost certainly performs better.

challenge_116_start_file-ISV.yxmd

terry10

My XML parsing revealed that each program could have more that one concert, so my final counts were different from the challenge output.

(e.g. Program 11633 has 3 concert date&times and the 3 pieces were played at each concert, so I ended up with 9 records.)

3 concert dates for Program 11633

My output for program 11633Solution Output for program 11633

challenge_116_terry10.yxmd

Natasha

Here is my solution. I agree with @terry10 that there might be multiple instances of concertInfo, though the solution seems to keep only the first one so I did the same.

Spoiler

challenge_116_Natasha.yxmd

patrick_digan

Spoiler

I made use of @jdunkerley79's XMLInput which I previously wrapped into a generic macro so it could get the xml all parsed. I like working with it because it gets to the most granular level and breaks everything out. I split the needed fields into 3 different buckets based upon what level of the xml they're at and whether i need to take the first entry or concatenate them.

challenge_116_start_file.v2yxmd.yxzp

joe_yang

My End Goal was one pipeline that is simple and easy to understand without too many joins, prepping, and transforming since the input data is in a nice and clean flat file.

challenge_116_Final_JY.yxmd

AdityaSardeshpande

I noticed that in the expected output, only 1 concert info was read per program ID.

I however have read first 2 concert info and as such have more rows than expected.

*This is the first time , I am using XML parse and would appreciate your feedback.

challenge_116_start_file.yxmd

MMehdi

Challenge 116 done

Spoiler

Workflow - challenge #116

challenge_116_resolved_by_MM.yxzp

joe_yang

I think Aditya has a good point here because one Program can have many showings of the same concert which can land on different days and times. If the Times and Dates of these showings are important to the case, I've gone ahead and corrected my workflow to include the additional showings:

GalynaSkamrova

My Solution

Spoiler

challenge_116_solutionGS.yxzp

KevinW

Hi,

I got the data to match except for program ID 12104 piece 7955* - if you look at the interval information this is the second intermission - not sure why it has a piece ID in the XML. my data did not have this line as I stripped out the intermissions but the output does.

NicoleJohnson

My solution!

Spoiler

Got a little picky on this one with the summarization of composers, conductors, soloists, works & movements - I didn't want to see the same one listed twice in the details if that program had more than 1 of the same work/person. So my results probably don't match exactly, but a cursory review looks pretty decent!

Cheers!

challenge_116_NicoleJohnson.yxmd

ggruccio

That was a challenge! There are probably ways to do it with less tools than I used....I'll have to examine some of the other submissions!

Spoiler

My real challenge here was determining how to work with multiple dates and performances. The results to compare to seemed to imply only one date and time per program ID, however I had multiple dates and times. I divided the data stream - choosing only the first date - and then merged that back with the data on the work (soloists, etc.)

challenge_116_gg_finish.yxmd

SeanAdams

This was more fiddly than difficult - spent a lot of time aligning fields, changing types etc.

Spoiler

challenge_116_SeanAdamsSolution.yxzp

estherb47

Great practice with the XML parsing tool!

My original workflow treated each instance of the program (e.g., same program, different days), as a record. Simple Unique tool at the end of the workflow took care of that.

Fun fact. I could verify this list with one of my dear friend's father -- he's been the librarian of the NY Phil for many many years!

challenge_116_EHB_solution.yxmd

LordNeilLord

I HATE XML PARSING

Spoiler

challenge_116_LNL.yxmd

DawnR

I had to copy the xml file first, since our network blocked the download tool use, but got it done. Good practice for xml parsing.

challenge_116_DawnR_Completed.yxmd

jasperlch

Solution attached.

challenge_116_JL.yxmd

JosephSerpis

Challenge Completed

challenge_116_Joe_Serpis.yxmd

kat

When this challenge first came out I had no idea how to solve it, but now I'm slowly starting to feel comfortable with XML parse could probably do with a few less tools!

I took into account all concerts, so I have a few more rows (at least that's what I think is the reason!)

Spoiler

Challenge #116.yxmd

DavidP

Great challenge!

I also initially got one less record than the published results, relating to Program 12104 Piece 7955* which is a second intermission.

My filter condition Isnull([Interval]) excluded this, but when I change the filter condition to [Interval] != "Intermission", I got the same as the official results.

It really helps to open the xml file in a browser before you start to just see which headings contain the data that you want and how many levels you have to parse to get to them.

challenge_116_DavidP.yxmd

danilang

Ah! The joys of running on an under powered (8GB) machine. I had originally built my workflow linearly, parsing each layer in-line with the previous one. By the time I was parsing the work info, it taking upwards of 3 minutes and generating 10GB data sets. And this was before had even started on the soloists. I changed tactics and parsed the major node types in parallel. Now, complete run time is under 10 seconds, including the download and the largest data set is only 30MB

Divide and conquer saves the day

Spoiler

Dan

challenge_116_solution_DL.yxmd

philipmannering

This was very tough. Not an xml parsing fan.

Spoiler

Workflow

Challenge 116 - A Symphony of Parsing Tools.yxmd

pjdit

First time using the XML Parse tool. had fun figuring out what settings to use for each section, but ended up pretty streamline compared to other solutions I'm seeing.

Spoiler

challenge_116_PD_Solution.yxmd

JoBen

Cheers!

challenge_116_Joben_Solution.yxmd

pasccout

Here is my solution

challenge_116_my_solution.yxmd

jssandom

My solution

challenge_116_solution_JSS.yxmd

jamielaird

Here's my solution. Cache came in handy here.

Spoiler

challenge_116_JL.yxmd

justynam

This was tough but very useful exercise.

My solution attached.

challenge_116_solution_JM.yxmd

JoshuaGostick

My solution

Spoiler

challenge_116_JG.yxmd

ipeng

My solution. I don't think it is a beautiful one...

IP_challenge_116_start_file.yxmd

kelly_gilbert

Whew, this one was a workout! Good practice configuring parsing tools!

Spoiler

The solution retains only the first performance of each program (for example, the solution contains the 9/7/2011 performance of program 11640, but not the 9/8/2011 performance).
I parsed all concerts, but filtered out all but the first.

The given solution removes first intermissions, but not all intermissions (for example, Program ID = 12104 and Piece ID = 7955*.
I removed all intermissions.

The given solution converts the soloist list to V_String (rather than V_WString), so some special characters are lost (example: Program ID = 13872, Piece ID = 2663*).
I used a V_WString field type to retain the special characters.

Spoiler

challenge_116_solution_klg.yxmd

RichoBsJ

Hi! Here my solution

challenge_116_richobsj.yxmd

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15879

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703