40 kotlin-dataframe puzzles¶

Importing kotlin-dataframe¶

Getting started¶

Difficulty: easy

1. Import kotlin-dataframe

In [1]:

%useLatestDescriptors
%use dataframe

DataFrame Basics¶

A few of the fundamental routines for selecting, sorting, adding and aggregating data in DataFrames¶

Difficulty: easy

Consider the following columns:

[kotlin]
val animal by columnOf("cat", "cat", "snake", "dog", "dog", "cat", "snake", "cat", "dog", "dog")
val age by columnOf(2.5, 3.0, 0.5, Double.NaN, 5.0, 2.0, 4.5, Double.NaN, 7, 3)
val visits by columnOf(1, 3, 2, 3, 2, 3, 1, 1, 2, 1)
val priority by columnOf("yes", "yes", "no", "yes", "no", "no", "no", "yes", "no", "no")

2. Create a DataFrame df from this columns.

In [2]:

val animal by columnOf("cat", "cat", "snake", "dog", "dog", "cat", "snake", "cat", "dog", "dog")
val age by columnOf(2.5, 3.0, 0.5, Double.NaN, 5.0, 2.0, 4.5, Double.NaN, 7.0, 3.0)
val visits by columnOf(1, 3, 2, 3, 2, 3, 1, 1, 2, 1)
val priority by columnOf("yes", "yes", "no", "yes", "no", "no", "no", "yes", "no", "no")

val df = dataFrameOf(animal, age, visits, priority)
df

Out[2]:

animal	age	visits	priority
cat	2.500000	1	yes
cat	3.000000	3	yes
snake	0.500000	2	no
dog	NaN	3	yes
dog	5.000000	2	no
cat	2.000000	3	no
snake	4.500000	1	no
cat	NaN	1	yes
dog	7.000000	2	no
dog	3.000000	1	no

3. Display a summary of the basic information about this DataFrame and its data.

In [3]:

df.schema()

Out[3]:

animal: String
age: Double
visits: Int
priority: String

In [4]:

df.describe()

Out[4]:

name	type	count	unique	top	freq	mean	std	min	median	max
animal	String	10	3	cat	4	null	null	cat	dog	snake
age	Double	10	8	3.000000	2	NaN	NaN	0.500000	3.750000	NaN
visits	Int	10	3	1	4	1.900000	0.875595	1	2	3
priority	String	10	2	no	6	null	null	no	no	yes

4. Return the first 3 rows of the DataFrame df.

In [5]:

df[0 ..< 3] // df[0..2]

// or equivalently

df.head(3)

// or

df.take(3)

Out[5]:

animal	age	visits	priority
cat	2.500000	1	yes
cat	3.000000	3	yes
snake	0.500000	2	no

5. Select "animal" and "age" columns from the DataFrame df.

In [6]:

df[animal, age]

Out[6]:

animal	age
cat	2.500000
cat	3.000000
snake	0.500000
dog	NaN
dog	5.000000
cat	2.000000
snake	4.500000
cat	NaN
dog	7.000000
dog	3.000000

6. Select the data in rows [3, 4, 8] and in columns ["animal", "age"].

In [7]:

df[3, 4, 8][animal, age]

Out[7]:

animal	age
dog	NaN
dog	5.000000
dog	7.000000

7. Select only the rows where the number of visits is grater than 2.

In [8]:

df.filter { visits > 2 }

Out[8]:

animal	age	visits	priority
cat	3.000000	3	yes
dog	NaN	3	yes
cat	2.000000	3	no

8. Select the rows where the age is missing, i.e. it is NaN.

In [9]:

df.filter { age.isNaN() }

Out[9]:

animal	age	visits	priority
dog	NaN	3	yes
cat	NaN	1	yes

N9. Select the rows where the animal is a cat and the age is less than 3.

In [10]:

df.filter { animal == "cat" && age < 3 }

Out[10]:

animal	age	visits	priority
cat	2.500000	1	yes
cat	2.000000	3	no

10. Select the rows where age is between 2 and 4 (inclusive).

In [11]:

df.filter { age in 2.0..4.0 }

Out[11]:

animal	age	visits	priority
cat	2.500000	1	yes
cat	3.000000	3	yes
cat	2.000000	3	no
dog	3.000000	1	no

11. Change tha age in row 5 to 1.5

In [12]:

df.update { age }.at(5).with { 1.5 }

Out[12]:

animal	age	visits	priority
cat	2.500000	1	yes
cat	3.000000	3	yes
snake	0.500000	2	no
dog	NaN	3	yes
dog	5.000000	2	no
cat	1.500000	3	no
snake	4.500000	1	no
cat	NaN	1	yes
dog	7.000000	2	no
dog	3.000000	1	no

12. Calculate the sum of all visits in df (i.e. the total number of visits).

In [13]:

df.visits.sum()

Out[13]:

13. Calculate the mean age for each different animal in df.

In [14]:

df.groupBy { animal }.mean { age }

Out[14]:

animal	age
cat	NaN
snake	2.500000
dog	NaN

14. Append a new row to df with your choice of values for each column. Then delete that row to return the original DataFrame.

In [15]:

val modifiedDf = df.append("dog", 5.5, 2, "no")
modifiedDf.dropLast()

Out[15]:

animal	age	visits	priority
cat	2.500000	1	yes
cat	3.000000	3	yes
snake	0.500000	2	no
dog	NaN	3	yes
dog	5.000000	2	no
cat	2.000000	3	no
snake	4.500000	1	no
cat	NaN	1	yes
dog	7.000000	2	no
dog	3.000000	1	no

15. Count the number of each type of animal in df.

In [16]:

df.groupBy { animal }.count()

Out[16]:

animal	count
cat	4
snake	2
dog	4

16. Sort df first by the values in the 'age' in descending order, then by the value in the 'visits' column in ascending order.

In [17]:

df.sortBy { age.desc() and visits }

Out[17]:

animal	age	visits	priority
cat	NaN	1	yes
dog	NaN	3	yes
dog	7.000000	2	no
dog	5.000000	2	no
snake	4.500000	1	no
dog	3.000000	1	no
cat	3.000000	3	yes
cat	2.500000	1	yes
cat	2.000000	3	no
snake	0.500000	2	no

17. The 'priority' column contains the values 'yes' and 'no'. Replace this column with a column of boolean values: 'yes' should be True and 'no' should be False.

In [18]:

df.convert { priority }.with { it == "yes" }

Out[18]:

animal	age	visits	priority
cat	2.500000	1	true
cat	3.000000	3	true
snake	0.500000	2	false
dog	NaN	3	true
dog	5.000000	2	false
cat	2.000000	3	false
snake	4.500000	1	false
cat	NaN	1	true
dog	7.000000	2	false
dog	3.000000	1	false

18. In the 'animal' column, change the 'dog' entries to 'corgi'.

In [19]:

df.update { animal }.where { it == "dog" }.with { "corgi" }

Out[19]:

animal	age	visits	priority
cat	2.500000	1	yes
cat	3.000000	3	yes
snake	0.500000	2	no
corgi	NaN	3	yes
corgi	5.000000	2	no
cat	2.000000	3	no
snake	4.500000	1	no
cat	NaN	1	yes
corgi	7.000000	2	no
corgi	3.000000	1	no

19. For each animal type and each number of visits, find the mean age. In other words, each row is an animal, each column is a number of visits and the values are the mean ages.

In [20]:

df.pivot { visits }.groupBy { animal }.mean(skipNA = true) { age }

Out[20]:

animal	visits
	1	3	2
cat	2.500000	2.500000	null
snake	4.500000	null	0.500000
dog	3.000000	NaN	6.000000

DataFrame: beyond the basics¶

Slightly trickier: you may need to combine two or more methods to get the right answer¶

Difficulty: medium

The previous section was tour through some basic but essential DataFrame operations. Below are some ways that you might need to cut your data, but for which there is no single "out of the box" method.

20. You have a DataFrame df with a column 'A' of integers. For example:

val df = dataFrameOf("A")(1, 2, 2, 3, 4, 5, 5, 5, 6, 7, 7)

How do you filter out rows which contain the same integer as the row immediately above?

You should be left with a column containing the following values:

1, 2, 3, 4, 5, 6, 7

In [21]:

val df = dataFrameOf("A")(1, 2, 2, 3, 4, 5, 5, 5, 6, 7, 7)
df

Out[21]:

A
1
2
2
3
4
5
5
5
6
7
7

In [22]:

df.filter { prev()?.A != A }

Out[22]:

A
1
2
3
4
5
6
7

In [23]:

df.filter { diffOrNull { A } != 0 }

Out[23]:

A
1
2
3
4
5
6
7

We could use distinct() here but it won't work as desired if A is [1, 1, 2, 2, 1, 1] for example.

In [24]:

df.distinct()

Out[24]:

A
1
2
3
4
5
6
7

21. Given a DataFrame of random numetic values:

val df = dataFrameOf("a", "b", "c").randomDouble(5) // this is a 5x3 DataFrame of double values

how do you subtract the row mean from each element in the row?

In [25]:

val df = dataFrameOf("a", "b", "c").randomDouble(5)
df

Out[25]:

a	b	c
0.521123	0.918268	0.565521
0.809771	0.543012	0.597897
0.927176	0.888330	0.874393
0.058951	0.728707	0.034816
0.804097	0.762586	0.937529

In [26]:

df.update { colsOf<Double>() }
    .with { it - rowMean() }

Out[26]:

a	b	c
-0.147181	0.249964	-0.102783
0.159544	-0.107215	-0.052330
0.030543	-0.008303	-0.022240
-0.215207	0.454549	-0.239342
-0.030640	-0.072151	0.102791

22. Suppose you have DataFrame with 10 columns of real numbers, for example:

val names = ('a'..'j').map { it.toString() }
val df = dataFrameOf(names).randomDouble(5)

Which column of number has the smallest sum? Return that column's label.

In [27]:

val names = ('a'..'j').map { it.toString() }
val df = dataFrameOf(names).randomDouble(5)
df

Out[27]:

a	b	c	d	e	f	g	h	i	j
0.612169	0.108263	0.185536	0.430257	0.959346	0.399817	0.498650	0.417456	0.404601	0.847830
0.901659	0.040860	0.003512	0.870346	0.182568	0.532544	0.287011	0.339791	0.622038	0.822349
0.531465	0.423177	0.887166	0.351157	0.430013	0.179277	0.953403	0.222690	0.281130	0.657052
0.203136	0.526475	0.518940	0.409763	0.121049	0.888950	0.438593	0.773491	0.669085	0.436394
0.531437	0.142654	0.976179	0.982954	0.079222	0.669538	0.932285	0.987870	0.487531	0.246299

In [28]:

df.sum().transpose().minBy("value")["name"]

Out[28]:

23. How do you count how many unique rows a DataFrame has (i.e. ignore all rows that are duplicates)?

In [29]:

val df = dataFrameOf("a", "b", "c").randomInt(30, 0..2)
df.distinct().count()

Out[29]:

24. In the cell below, you have a DataFrame df that consists of 10 columns of floating-point numbers. Exactly 5 entries in each row are NaN values.

For each row of the DataFrame, find the column which contains the third NaN value.

You should return a column of column labels: e, c, d, h, d

In [30]:

val nan = Double.NaN
val names = ('a'..'j').map { it.toString() }
val data = listOf(
    0.04, nan, nan,  0.25, nan,  0.43, 0.71, 0.51, nan,  nan,
    nan,  nan, nan,  0.04, 0.76, nan,  nan,  0.67, 0.76, 0.16,
    nan,  nan, 0.5,  nan,  0.31, 0.4,  nan,  nan,  0.24, 0.01,
    0.49, nan, nan,  0.62, 0.73, 0.26, 0.85, nan,  nan,  nan,
    nan,  nan, 0.41, nan,  0.05, nan,  0.61, nan,  0.48, 0.68,
)
val df = dataFrameOf(names)(*data.toTypedArray())
df

Out[30]:

a	b	c	d	e	f	g	h	i	j
0.040000	NaN	NaN	0.250000	NaN	0.430000	0.710000	0.510000	NaN	NaN
NaN	NaN	NaN	0.040000	0.760000	NaN	NaN	0.670000	0.760000	0.160000
NaN	NaN	0.500000	NaN	0.310000	0.400000	NaN	NaN	0.240000	0.010000
0.490000	NaN	NaN	0.620000	0.730000	0.260000	0.850000	NaN	NaN	NaN
NaN	NaN	0.410000	NaN	0.050000	NaN	0.610000	NaN	0.480000	0.680000

In [31]:

df.mapToColumn("res") { 
    namedValuesOf<Double>()
        .filter { it.value.isNaN() }.drop(2)
        .firstOrNull()?.name 
}

Out[31]:

res
e
c
d
h
d

25. A DataFrame has a column of groups 'grps' and and column of integer values 'vals':

val grps by column("a", "a", "a", "b", "b", "c", "a", "a", "b", "c", "c", "c", "b", "b", "c")
val vals by column(12, 345, 3, 1, 45, 14, 4, 52, 54, 23, 235, 21, 57, 3, 87)

val df = dataFrameOf(grps, vals)

For each group, find the sum of the three greatest values. You should end up with the answer as follows:

grps
a    409
b    156
c    345

In [32]:

val grps by columnOf("a", "a", "a", "b", "b", "c", "a", "a", "b", "c", "c", "c", "b", "b", "c")
val vals by columnOf(12, 345, 3, 1, 45, 14, 4, 52, 54, 23, 235, 21, 57, 3, 87)

val df = dataFrameOf(grps, vals)
df

Out[32]:

grps	vals
a	12
a	345
a	3
b	1
b	45
c	14
a	4
a	52
b	54
c	23
c	235
c	21
b	57
b	3
c	87

In [33]:

df.groupBy { grps }.aggregate { 
    vals.sortDesc().take(3).sum() into "res"
}

Out[33]:

grps	res
a	409
b	156
c	345

26. The DataFrame df constructed below has two integer columns 'A' and 'B'. The values in 'A' are between 1 and 100 (inclusive).

For each group of 10 consecutive integers in 'A' (i.e. (0, 10], (10, 20], ...), calculate the sum of the corresponding values in column 'B'.

The answer as follows:

A
(0, 10]      635
(10, 20]     360
(20, 30]     315
(30, 40]     306
(40, 50]     750
(50, 60]     284
(60, 70]     424
(70, 80]     526
(80, 90]     835
(90, 100]    852

In [34]:

import kotlin.random.Random

val random = Random(42)
val list = List(200) { random.nextInt(1, 101) }
val df = dataFrameOf("A", "B")(*list.toTypedArray())
df

Out[34]:

A	B
34	41
42	3
42	33
22	41
70	88
53	68
80	4
59	59
45	1
27	14
70	8
11	52
51	60
46	43
17	17
17	42
56	29
58	49
48	7
73	52

In [35]:

df.groupBy { A.map { (it - 1) / 10 } }.sum { B }
    .sortBy { A }
    .convert { A }.with { "(${it * 10}, ${it * 10 + 10}]" }

Out[35]:

A	B
(0, 10]	353
(10, 20]	873
(20, 30]	321
(30, 40]	322
(40, 50]	432
(50, 60]	754
(60, 70]	405
(70, 80]	561
(80, 90]	657
(90, 100]	527

DataFrames: harder problems¶

These might require a bit of thinking outside the box...¶

Difficulty: hard

27. Consider a DataFrame df where there is an integer column 'X':

val df = dataFrameOf("X")(7, 2, 0, 3, 4, 2, 5, 0, 3 , 4)

For each value, count the difference back to the previous zero (or the start of the column, whichever is closer). These values should therefore be

[1, 2, 0, 1, 2, 3, 4, 0, 1, 2]

Make this a new column 'Y'.

In [36]:

val df = dataFrameOf("X")(7, 2, 0, 3, 4, 2, 5, 0, 3, 4)
df

Out[36]:

X
7
2
0
3
4
2
5
0
3
4

In [37]:

df.mapToColumn("Y") {
    if (it.X == 0) 0 else (prev()?.newValue() ?: 0) + 1
}

Out[37]:

Y
1
2
0
1
2
3
4
0
1
2

28. Consider the DataFrame constructed below which contains rows and columns of numerical data.

Create a list of the column-row index locations of the 3 largest values in this DataFrame. In thi case, the answer should be:

[(0, d), (2, c), (3, f)]

In [38]:

val names = ('a'..'h').map { it.toString() } // val names = (0..7).map { it.toString() }
val random = Random(30)
val list = List(64) { random.nextInt(1, 101) }
val df = dataFrameOf(names)(*list.toTypedArray())
df

Out[38]:

a	b	c	d	e	f	g	h
43	88	66	100	9	59	74	23
6	63	43	58	4	85	9	25
49	59	100	52	28	1	19	81
92	41	13	57	28	97	63	39
4	59	72	65	50	35	14	31
55	74	33	66	17	39	80	38
18	64	91	39	80	55	65	2
19	76	75	18	32	97	1	32

In [39]:

df.add("index") { index() }
    .gather { dropLast() }.into("name", "vals")
    .sortByDesc("vals").take(3)["index", "name"]

Out[39]:

index	name
0	d
2	c
3	f

29. You are given the DataFrame below with a column of group IDs, 'grps', and a column of corresponding integer values, 'vals'.

val random = Random(31)
val lab =  listOf("A", "B")

val vals by columnOf(List(15) { random.nextInt(-30, 30) })
val grps by columnOf(List(15) { lab[random.nextInt(0, 2)] })

val df = dataFrameOf(vals, grps)

Create a new column 'patched_values' which contains the same values as the 'vals' any negative values in 'vals' with the group mean:

vals    grps    patched_vals
 -17      B         21.0
  -7      B         21.0
  28      B         28.0
  16      B         16.0
 -21      B         21.0
  19      B         19.0
  -2      B         21.0
 -19      B         21.0
  16      A         16.0
   9      A         9.0
 -14      A         16.0
 -19      A         16.0
 -22      A         16.0
  -1      A         16.0
  23      A         23.0

In [40]:

val random = Random(31)
val lab = listOf("A", "B")

val vals by columnOf(*Array(15) { random.nextInt(-30, 30) })
val grps by columnOf(*Array(15) { lab[random.nextInt(0, 2)] })

val df = dataFrameOf(vals, grps)
df

Out[40]:

vals	grps
-17	B
-7	B
16	A
28	B
9	A
16	B
-21	B
-14	A
-19	A
-22	A
19	B
-2	B
-1	A
-19	B
23	A

In [41]:

val means = df.filter { vals >= 0 }
    .groupBy { grps }.mean()
    .pivot { grps }.values { vals }

df.add("patched_values") {
    if (vals < 0) means[grps] else vals.toDouble()
}

Out[41]:

vals	grps	patched_values
-17	B	21.000000
-7	B	21.000000
16	A	16.000000
28	B	28.000000
9	A	9.000000
16	B	16.000000
-21	B	21.000000
-14	A	16.000000
-19	A	16.000000
-22	A	16.000000
19	B	19.000000
-2	B	21.000000
-1	A	16.000000
-19	B	21.000000
23	A	23.000000

30. Implement a rolling mean over groups with window size 3, which ignores NaN value. For example consider the following DataFrame:

val group by columnOf("a", "a", "b", "b", "a", "b", "b", "b", "a", "b", "a", "b")
val value by columnOf(1.0, 2.0, 3.0, Double.NaN, 2.0, 3.0, Double.NaN, 1.0, 7.0, 3.0, Double.NaN, 8.0)

val df = dataFrameOf(group, value)
df

group   value
a       1.0
a       2.0
b       3.0
b       NaN
a       2.0
b       3.0
b       NaN
b       1.0
a       7.0
b       3.0
a       NaN
b       8.0

The goal is:

E.g. the first window of size three for group 'b' has values 3.0, NaN and 3.0 and occurs at row index 5. Instead of being NaN the value in the new column at this row index should be 3.0 (just the two non-NaN values are used to compute the mean (3+3)/2)

In [42]:

val groups by columnOf("a", "a", "b", "b", "a", "b", "b", "b", "a", "b", "a", "b")
val value by columnOf(1.0, 2.0, 3.0, Double.NaN, 2.0, 3.0, Double.NaN, 1.0, 7.0, 3.0, Double.NaN, 8.0)

val df = dataFrameOf(groups, value)
df

Out[42]:

groups	value
a	1.000000
a	2.000000
b	3.000000
b	NaN
a	2.000000
b	3.000000
b	NaN
b	1.000000
a	7.000000
b	3.000000
a	NaN
b	8.000000

In [43]:

df.add("id") { index() }
    .groupBy { groups }.add("res") {
        relative(-2..0).value.filter { !it.isNaN() }.mean()
    }.concat()
    .sortBy("id")
    .remove("id")

Out[43]:

groups	value	res
a	1.000000	1.000000
a	2.000000	1.500000
b	3.000000	3.000000
b	NaN	3.000000
a	2.000000	1.666667
b	3.000000	3.000000
b	NaN	3.000000
b	1.000000	2.000000
a	7.000000	3.666667
b	3.000000	2.000000
a	NaN	4.500000
b	8.000000	4.000000

Date¶

Difficulty: easy/medium

31. Create a column Of LocalDate that contains each day of 2015 and column of random numbers.

In [44]:

import kotlinx.datetime.*

In [45]:

class DateRangeIterator(first: LocalDate, last: LocalDate, val step: Int) : Iterator<LocalDate> {
    private val finalElement: LocalDate = last
    private var hasNext: Boolean = if (step > 0) first <= last else first >= last
    private var next: LocalDate = if (hasNext) first else finalElement

    override fun hasNext(): Boolean = hasNext

    override fun next(): LocalDate {
        val value = next
        if (value == finalElement) {
            if (!hasNext) throw kotlin.NoSuchElementException()
            hasNext = false
        } else {
            next = next.plus(step, DateTimeUnit.DayBased(1))
        }
        return value
    }
}

operator fun ClosedRange<LocalDate>.iterator() = DateRangeIterator(this.start, this.endInclusive, 1)

fun ClosedRange<LocalDate>.toList(): List<LocalDate> {
    return when (val size = this.start.daysUntil(this.endInclusive)) {
        0 -> emptyList()
        1 -> listOf(iterator().next())
        else -> {
            val dest = ArrayList<LocalDate>(size)
            for (item in this) {
                dest.add(item)
            }
            dest
        }
    }
}

In [46]:

val start = LocalDate(2015, 1, 1)
val end = LocalDate(2016, 1, 1)

val days = (start..end).toList()

val dti = days.toColumn("dti")
val s = List(dti.size()) { Random.nextDouble() }.toColumn("s")
val df = dataFrameOf(dti, s)
df.head()

Out[46]:

dti	s
2015-01-01	0.396569
2015-01-02	0.449204
2015-01-03	0.130729
2015-01-04	0.997274
2015-01-05	0.415122

32. Find the sum of the values in s for every Wednesday.

In [47]:

df.filter { dti.dayOfWeek == DayOfWeek.TUESDAY }.sum { s }

Out[47]:

26.523862856482456

33. For each calendar month in s, find the mean of values.

In [48]:

df.groupBy { dti.map { it.month } named "month" }.mean()

Out[48]:

month	s
JANUARY	0.546164
FEBRUARY	0.477099
MARCH	0.512146
APRIL	0.385120
MAY	0.516046
JUNE	0.489589
JULY	0.551648
AUGUST	0.534199
SEPTEMBER	0.501065
OCTOBER	0.488557
NOVEMBER	0.450117
DECEMBER	0.532484

34. For each group of four consecutive calendar months in s, find the date on which the highest value occurred.

In [49]:

df.add("month4") {
    when (dti.monthNumber) {
        in 1..4 -> 1
        in 5..8 -> 2
        else -> 3
    }
}.groupBy("month4").aggregate { maxBy(s) into "max" }

Out[49]:

month4	max
	dti	s	month4
1	2015-01-04	0.997274	1
2	2015-05-14	0.998469	2
3	2015-12-06	0.994667	3

35. Create a column consisting of the third Thursday in each month for the years 2015 and 2016.

In [50]:

import java.time.temporal.WeekFields
import java.util.*

In [51]:

val start = LocalDate(2015, 1, 1)
val end = LocalDate(2016, 12, 31)

(start..end).toList().toColumn("3thu").filter {
    it.toJavaLocalDate()[WeekFields.of(Locale.ENGLISH).weekOfMonth()] == 3
            && it.dayOfWeek.value == 4
}

Out[51]:

3thu
2015-01-15
2015-02-19
2015-03-19
2015-04-16
2015-05-14
2015-06-18
2015-07-16
2015-08-13
2015-09-17
2015-10-15
2015-11-19
2015-12-17
2016-01-14
2016-02-18
2016-03-17
2016-04-14
2016-05-19
2016-06-16
2016-07-14
2016-08-18

Cleaning Data¶

Making a DataFrame easier to work with¶

Difficulty: easy/medium

It happens all the time: someone gives you data containing malformed strings, lists and missing data. How do you tidy it up so you can get on with the analysis?

Take this monstrosity as the DataFrame to use in the following puzzles:

val fromTo = listOf("LoNDon_paris", "MAdrid_miLAN", "londON_StockhOlm", "Budapest_PaRis", "Brussels_londOn").toColumn("From_To")
val flightNumber = listOf(10045.0, Double.NaN, 10065.0, Double.NaN, 10085.0).toColumn("FlightNumber")
val recentDelays = listOf(listOf(23, 47), listOf(), listOf(24, 43, 87), listOf(13), listOf(67, 32)).toColumn("RecentDelays")
val airline = listOf("KLM(!)", "<Air France> (12)", "(British Airways. )", "12. Air France", "'Swiss Air'").toColumn("Airline")

val df = dataFrameOf(fromTo, flightNumber, recentDelays, airline)

It looks like this:

From_To             FlightNumber    RecentDelays    Airline
LoNDon_paris        10045.000000    [23, 47]        KLM(!)
MAdrid_miLAN        NaN             []              {Air France} (12)
londON_StockhOlm    10065.000000    [24, 43, 87]    (British Airways. )
Budapest_PaRis      NaN             [13]            12. Air France
Brussels_londOn     10085.000000    [67, 32]        'Swiss Air'

In [52]:

val fromTo = listOf("LoNDon_paris", "MAdrid_miLAN", "londON_StockhOlm", "Budapest_PaRis", "Brussels_londOn").toColumn("From_To")
val flightNumber = listOf(10045.0, Double.NaN, 10065.0, Double.NaN, 10085.0).toColumn("FlightNumber")
val recentDelays = listOf(listOf(23, 47), listOf(), listOf(24, 43, 87), listOf(13), listOf(67, 32)).toColumn("RecentDelays")
val airline = listOf("KLM(!)", "{Air France} (12)", "(British Airways. )", "12. Air France", "'Swiss Air'").toColumn("Airline")

var df = dataFrameOf(fromTo, flightNumber, recentDelays, airline)
df

Out[52]:

From_To	FlightNumber	RecentDelays	Airline
LoNDon_paris	10045.000000	[23, 47]	KLM(!)
MAdrid_miLAN	NaN	[ ]	{Air France} (12)
londON_StockhOlm	10065.000000	[24, 43, 87]	(British Airways. )
Budapest_PaRis	NaN	[13]	12. Air France
Brussels_londOn	10085.000000	[67, 32]	'Swiss Air'

36. Some values in the FlightNumber column are missing (they are NaN). These numbers are meant to increase by 10 with each row, so 10055 and 10075 need to be put in place. Modify df to fill in these missing numbers and make the column an integer column (instead of a float column).

In [53]:

df = df.fillNaNs { FlightNumber }
    .with { prev()!!.FlightNumber + (next()!!.FlightNumber - prev()!!.FlightNumber) / 2 }
    .convert { FlightNumber }.toInt()
df

Out[53]:

From_To	FlightNumber	RecentDelays	Airline
LoNDon_paris	10045	[23, 47]	KLM(!)
MAdrid_miLAN	10055	[ ]	{Air France} (12)
londON_StockhOlm	10065	[24, 43, 87]	(British Airways. )
Budapest_PaRis	10075	[13]	12. Air France
Brussels_londOn	10085	[67, 32]	'Swiss Air'

37. The From_To column would be better as two separate columns! Split each string on the underscore delimiter _ to give a new two columns. Assign the correct names 'From' and 'To' to this columns.

In [54]:

var df2 = df.split { From_To }.by("_").into("From", "To")
df2

Out[54]:

From	To	FlightNumber	RecentDelays	Airline
LoNDon	paris	10045	[23, 47]	KLM(!)
MAdrid	miLAN	10055	[ ]	{Air France} (12)
londON	StockhOlm	10065	[24, 43, 87]	(British Airways. )
Budapest	PaRis	10075	[13]	12. Air France
Brussels	londOn	10085	[67, 32]	'Swiss Air'

38. Notice how the capitalisation of the city names is all mixed up in this temporary DataFrame 'temp'. Standardise the strings so that only the first letter is uppercase (e.g. "londON" should become "London".)

In [55]:

df2 = df2.update { From and To }.with { it.lowercase().replaceFirstChar(Char::uppercase) }
df2

Out[55]:

From	To	FlightNumber	RecentDelays	Airline
London	Paris	10045	[23, 47]	KLM(!)
Madrid	Milan	10055	[ ]	{Air France} (12)
London	Stockholm	10065	[24, 43, 87]	(British Airways. )
Budapest	Paris	10075	[13]	12. Air France
Brussels	London	10085	[67, 32]	'Swiss Air'

39. In the Airline column, you can see some extra punctuation and symbols have appeared around the airline names. Pull out just the airline name. E.g. '(British Airways. )' should become 'British Airways'.

In [56]:

df2 = df2.update { Airline }.with {
    "([a-zA-Z\\s]+)".toRegex().find(it)?.value ?: ""
}
df2

Out[56]:

From	To	FlightNumber	RecentDelays	Airline
London	Paris	10045	[23, 47]	KLM
Madrid	Milan	10055	[ ]	Air France
London	Stockholm	10065	[24, 43, 87]	British Airways
Budapest	Paris	10075	[13]	Air France
Brussels	London	10085	[67, 32]	Swiss Air

40. In the RecentDelays column, the values have been entered into the DataFrame as a list. We would like each first value in its own column, each second value in its own column, and so on. If there isn't an Nth value, the value should be null.

Expand the column of lists into columns named 'delays_' and replace the unwanted RecentDelays column in df with 'delays'.

In [57]:

val prep_df = df2.split { RecentDelays }.into { "delay_$it" }
prep_df

Out[57]:

From	To	FlightNumber	delay_1	delay_2	delay_3	Airline
London	Paris	10045	23	47	null	KLM
Madrid	Milan	10055	null	null	null	Air France
London	Stockholm	10065	24	43	87	British Airways
Budapest	Paris	10075	13	null	null	Air France
Brussels	London	10085	67	32	null	Swiss Air

The DataFrame looks much better now!

A	B
34	41
42	3
42	33
22	41
70	88
53	68
80	4
59	59
45	1
27	14
70	8
11	52
51	60
46	43
17	17
17	42
56	29
58	49
48	7
73	52

a	b	c	d	e	f	g	h
43	88	66	100	9	59	74	23
6	63	43	58	4	85	9	25
49	59	100	52	28	1	19	81
92	41	13	57	28	97	63	39
4	59	72	65	50	35	14	31
55	74	33	66	17	39	80	38
18	64	91	39	80	55	65	2
19	76	75	18	32	97	1	32

A	B
34	41
42	3
42	33
22	41
70	88
53	68
80	4
59	59
45	1
27	14
70	8
11	52
51	60
46	43
17	17
17	42
56	29
58	49
48	7
73	52

a	b	c	d	e	f	g	h
43	88	66	100	9	59	74	23
6	63	43	58	4	85	9	25
49	59	100	52	28	1	19	81
92	41	13	57	28	97	63	39
4	59	72	65	50	35	14	31
55	74	33	66	17	39	80	38
18	64	91	39	80	55	65	2
19	76	75	18	32	97	1	32

A	B
34	41
42	3
42	33
22	41
70	88
53	68
80	4
59	59
45	1
27	14
70	8
11	52
51	60
46	43
17	17
17	42
56	29
58	49
48	7
73	52

a	b	c	d	e	f	g	h
43	88	66	100	9	59	74	23
6	63	43	58	4	85	9	25
49	59	100	52	28	1	19	81
92	41	13	57	28	97	63	39
4	59	72	65	50	35	14	31
55	74	33	66	17	39	80	38
18	64	91	39	80	55	65	2
19	76	75	18	32	97	1	32