Hi everyone! This week I wanted to try something a little more challenging but I’m going to need a bit of help completing it. Below I have attached a text file containing the goals scored during the World Cup. The data is formatted as;
Player;country;time;
Time represents the minute in the game the goal was scored.
With this data we can produce a lot of different statistics for example;
Total number of goals scored by a given country
Total number of goals scored by a given player
List the name of all the players who scored for a given country
Total number of goals by all countries
Total number of goals scored during the first half (45 minutes)
Total number of goals scored during the second half (45 minutes to 90 minutes)
Total number of goals scored during extra time (after 90 minutes of play)
For now I just want to focus on the first bullet point.
First I thought it would be ideal to transform the data to table format, then simply count the rows of the country selected;
q)data:flip`player`country`minute!("SSS";";")0:`:goals.txt
q)count select from data where country=`Spain
7
I also wanted to loop through the text file and print (player + from + country + scored a goal at the + minute + th minute) for each player. Which seemed easy at first but really had difficulty.
I reckoned a function was most definitely needed but couldn’t wrap my head around how to loop not only through the 3 items but then onto the rest of the list.
This is exactly the output I wanted thanks ! Plus that article you tagged is very useful.
q) data:flip`player`country`minute!("SSS";";")0:`:goals.txt
q) exec (string[player],'" from ",/:string[country],'" scored a goal in the ",/:string[minute],:"th minute") from data
"Gazinsky from Russia scored a goal in the 12th minute"
"Cheryshev from Russia scored a goal in the 43th minute"
"Cheryshev from Russia scored a goal in the 91th minute"
"Dzyuba from Russia scored a goal in the 71th minute"
"Golovin from Russia scored a goal in the 94th minute"
"Gimenez from Uruguay scored a goal in the 89th minute"
"Cheryshev from Russia scored a goal in the 59th minute"
"Dzyuba from Russia scored a goal in the 62th minute"
"OG from Russia scored a goal in the 47th minute"
"Salah from Egypt scored a goal in the 73th minute"
"Suarez from Uruguay scored a goal in the 23th minute"
"Suarez from Uruguay scored a goal in the 10th minute"...
However when I try putting 0N! each in front of the statement I get a parse error?
q) data:flip`player`country`minute!("SSS";";")0:`:goals.txt
q) 0N! each exec (string[player],'" from ",/:string[country],'" scored a goal in the ",/:string[minute],:"th minute") from data
parse error
If all you want is to print these statements, you don’t necessarily need to cast the columns to symbols or save in a table. Try the following one-liner:
{x," from “,y,” scored a goal in the “,z,“th minute”}./: flip(”***“;”;") 0:`:goals.txt
By default this prints to the console, so I don’t see why you would need to include the “0N!” .
The reason you are getting a parse error on the "0N! each " is because “0N!” is an infix function followed by a postfix iterator. You will have to wrap "0N! in parenthesis (see https://code.kx.com/q/basics/syntax/#prefix-infix-postfix). Also worth noting that “0N!/:” will work here as well. However, I think even this is unnecessary as “0N!” is unary and natively handles a list of strings.