Challenge 4 - World Cup

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: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 couldn’t 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.