Hi everyone! This week I wanted to try something a little more challenging but Im 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:flipplayercountryminute!("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 couldnt wrap my head around how to loop not only through the 3 items but then onto the rest of the list.
Any help / advice would be greatly appreciated !!
Megan
You can do it through qsql syntax:
exec (string[player],‘" from ",/:string[country],’" scored a goal in the ",/:string[minute],:“th minute”) from data
Useful information for this can be found here:
https://subhabratachoudhury.com/p/2016/05/concatenating-kdb-columns/
If you want to display to console each of these you can put a 0N! each in front of the above exec state.
This is exactly the output I wanted thanks ! Plus that article you tagged is very useful.
q) data:flipplayercountryminute!("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:flipplayercountryminute!("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
Hi Megan,
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.