In what sense is the connection enabling one to compare the vector field at two different points on the manifold [...], when the mapping is from the (Cartesian product of) the set of tangent vector fields to itself? I thought that the connection ∇ "connected" two neighbouring tangent spaces through the notion of parallel transport [...]
To see a connection only as a mapping $\nabla: \mathcal{X}(M)\times\mathcal{X}(M)\rightarrow\mathcal{X}(M)$ is too restrictive. Often a connection is also seen as a map $Y\mapsto\nabla Y\in\Gamma(TM\otimes TM^*)$, which highlights the derivative aspect. However, the important point is that $\nabla$ is $C^\infty(M)$-linear in the first argument which results in the fact that the value $\nabla_X Y|_p$ only depends on $X_p$ in the sense that
$$
X_p=Z_p \Rightarrow \nabla_X Y|_p = \nabla_Z Y|_p.
$$
Hence, for every $v\in TM_p$, $\nabla_vY$ is well-defined. This leads directly to the definition of parallel vector fields and parallel transport (as I think you already know).
Vice versa, given parallel transport maps $\Gamma(\gamma)^t_s: TM_{\gamma(s)}\rightarrow TM_{\gamma(t)}$, one can recover the connection via
$$
\nabla_X Y|p = \frac{d}{dt}\bigg|_{t=0}\Gamma(\gamma)_t^0Y_{\gamma(t)} \quad(\gamma \text{ is a integral curve of }X).
$$
This is exactly the generalisation of directional derivatives in the sense that we vary $Y$ in direction of $X_p$ in a parallel manner.
In Euclidean space this indeed reduces to the directional derivative: Using the identity chart every vector field can be written as $Y_p=(p,V(p))$ for $V:\mathbb R^n\rightarrow \mathbb R^n$ and the parallel transport is just given by
$$
\Gamma(\gamma)_s^t (\gamma(s),v)=(\gamma(t),v).
$$
Hence, we find in Euclidean space:
$$
\frac{d}{dt}\bigg|_{t=0}\Gamma(\gamma)_t^0Y_{\gamma(t)} = \frac{d}{dt}\bigg|_{t=0}(p,V(\gamma(t))) = (p,DV\cdot\gamma'(0)),
$$
which is exactly the directional derivative of $V$ in direction $v=\gamma'(0)$.
Back to the original question: I think it is hard to see how a connection "connects neighbouring tangent spaces" only from the axioms. You should keep in mind, however, that the contemporary formalism has passed many abstraction layers since the beginning and is reduced to its core, the axioms (for a survey see also Wikipedia). To get the whole picture, it is essential that one explores all possible interpretations and consequences of the definition, since often they led to the definition in the first place. In my opinion, the connection is defined as it is with the image in mind that it is an infinitesimal version of parallel transport. Starting from this point, properties as the Leibniz rule are a consequence. However, having such a differential operator $\nabla$ fulfilling linearity, Leibniz rule and so on, is fully equivalent to having parallel transport in the first place. In modern mathematics, these properties are thus taken as the defining properties/axioms of a connection, mainly because they are easier to handle and easier to generalise to arbitrary vector bundles.
Given this, what does the quantity $\nabla_{e_\mu}e_\nu=\Gamma^\lambda_{\mu\nu}e_\lambda$ represent? [...]
As you wrote, the connection coefficients / Christoffel symbols $\Gamma^\lambda_{\mu\nu}$ are the components of the connection in a local frame and are needed for explicit computations. I think on this level you can't get much meaning out these coefficients. However, they reappear in a nicer way if you restate everything in the Cartan formalism and study Cartan and/or principal connections. The Wikipedia article on connection forms tries to give an introduction to this approach.
Nahakara also gives an introduction to connections on principal bundles and the relation to gauge theory later on in his book. In my opinion, this chapter is a bit short and could be more detailed, especially to the end. But it is a good start.
Note that $A$ is the matrix whose row vectors are the vectors $E_i$ written in terms of the standard basis $U_j$. So
$$\omega_{ij}= \langle\nabla E_i,E_j\rangle = \langle dE_i,E_j\rangle = (dA\,A^\top)_{ij},$$
as required.
Best Answer
Using some rather compact notation, we have that if $\mathfrak{e}=\mathfrak{v}R_\phi$, then $$\omega = R_{-\phi}\theta R_\phi + R_{-\phi} {\rm d}(R_\phi),$$because this is the general transformation law for local connection $1$-forms relative to two different frames, in terms of the change-of-basis matrix. Now, this becomes $$\begin{pmatrix} 0 & \omega_{12} \\ -\omega_{12} & 0\end{pmatrix} = \begin{pmatrix} \cos\phi & \sin\phi \\ -\sin\phi & \cos\phi\end{pmatrix}\begin{pmatrix} 0 & \theta_{12} \\ -\theta_{12} & 0\end{pmatrix}\begin{pmatrix} \cos\phi & -\sin\phi \\ \sin\phi & \cos\phi\end{pmatrix}+ \begin{pmatrix} \cos\phi & \sin\phi \\ -\sin\phi & \cos\phi\end{pmatrix}\begin{pmatrix} -\sin\phi & -\cos\phi \\ \cos\phi & -\sin\phi\end{pmatrix}\,{\rm d}\phi. $$Looking only at the $(1,2)$-entry, the desired formula follows after applying $\cos^2\phi+\sin^2\phi=1$.
Proof of transformation law. If $(M,\nabla)$ is any affine manifold (i.e., $M$ is equipped with an arbitrary affine connection $\nabla$; it may have torsion, $M$ doesn't need to have a metric, we're doing it freestyle), and $\mathfrak{e}$ and $\mathfrak{v}$ are local frames with connection $1$-form matrices $\theta$ and $\omega$, and related via $\mathfrak{e} = \mathfrak{v}A$ for some non-singular matrix of functions $A$, we'll show that $$\theta = A^{-1}\omega A + A^{-1}{\rm d}A.$$
Namely, our assumptions fully written are that $$e_j = \sum_i a^i_{\,j}v_i,\quad \nabla e_j = \sum_i\theta^i_{\,j}\otimes e_i,\quad \nabla v_j = \sum_i \omega^i_{\,j}\otimes v_i.$$We'll write the latter two simply as $$\nabla\mathfrak{e} = \mathfrak{e}\theta\quad\mbox{and}\quad \nabla\mathfrak{v} = \mathfrak{v}\omega.$$Thus, we compute $$\mathfrak{v}A\theta = \mathfrak{e}\theta = \nabla\mathfrak{e} = \nabla(\mathfrak{v}A) = (\nabla\mathfrak{v})A+\mathfrak{v}\,{\rm d}A = \mathfrak{v}\omega A +\mathfrak{v}\,{\rm d}A = \mathfrak{v}(\omega A + {\rm d}A), $$so linear independence of $\mathfrak{v}$ gives $$A\theta = \omega A + {\rm d}A \implies \theta = A^{-1}\omega A + A^{-1}\,{\rm d}A.$$Same proof gives same transformation law for connection $1$-forms in an arbitrary vector bundle with connection, and two local trivializations.
You can try to read my short notes on Cartan computations, pages 11 through 14 of my notes on principal bundles, and the book by Loring Tu (Amazon link).