This is my understanding of this yoga. It may not be exactly what you seek and may differ from another person's point of view. Also I apologize for my bad english.
For Grothendieck, many things should have a relative version. So instead of considering just a space $X_0$, consider a morphism $f:X\rightarrow S$ thought as a family of spaces $s\mapsto X_s:=f^{-1}(s)$.
Here is an easy example of relative thinking : Say you want to attach numerical invariants to your first space $X_0$, for example its dimension, you should instead replace it by a function on $S$, and you get the relative dimension. Under some assumptions, this function behave quite nicely.
If now you want to attach invariants that are sets, or abelian groups, to your first space $X_0$. Well there is a very nice tool that do exactly what you want for the relative version : presheaves on $S$. And under some assumptions, these are closely related to sheaves.
The six operations arise this way if you want to study cohomology.
There are many cohomology theories out there, and many of them have their 6 operations. But they do not behave exactly of the same way, so let's say we work with ordinary cohomology of (nice) topological spaces.
It is also better to work with derived categories. Indeed, the cohomology of space is in fact a complex up to quasi-isomorphism, more than just the groups $H^i$. Also, for example, the functor $f^!$ exists only at the level of derived categories. If $X$ is a space, let $D(X)$ be the derived category of sheaves on $X$ and let $\mathbb{Z}_X$ be the constant sheaf with value $\mathbb{Z}$ on $X$.
Let start with the first functor $f_*$ or rather $Rf_*:D(X)\rightarrow D(S)$. Well, this is exactly the functor that computes cohomology. Indeed, if $f$ is proper, $(R^if_*\mathbb{Z}_X)_s=H^i(X_s,\mathbb{Z})$ (this is the so-called proper base change theorem). So this is exactly the relative version of cohomology. In fact, if $f$ is proper and smooth (=submersion), then $R^if_*\mathbb{Z}$ are local systems on $S$.
There is also $f_!$ or rather $Rf_!:D(X)\rightarrow D(S)$. It does the same thing, but with cohomology with compact support : $(R^if_!\mathbb{Z}_X)_s=H^i_c(X_s,\mathbb{Z})$. This times, one does not need to assume $f$ proper.
And of course the tensor product $\otimes$ gives the multiplicative structure on cohomology. One can then speak of derived analogue of the Kunneth formula...
Now, if $g:T\rightarrow S$, there is the functor $g^*:D(S)\rightarrow D(T)$. In some sense this is the one that justify the whole thing : this is the functor which correspond to changing the base from $S$ to $T$. For example, if $j:U\rightarrow S$ is the inclusion of an open subset, one can form $X_U=f^{-1}(U)$ and $j^*Rf_*\mathbb{Z}_X=R(f_{|X_U})_*\mathbb{Z}_{X_U}$. This is a (very easy) special case of a very deep one : the smooth base-change theorem. Changing bases is VERY useful. For example, base change to the universal cover, so that local systems become constant. Or in algebraic geometry, base change to the algebraic closure. And of course, taking stalks are already special cases of base change...
And finally, the functor $f^!:D(S)\rightarrow D(X)$ and the internal Hom are there to deal with duality. (Note that $f^!$ is right adjoint to $f_!$, not left). Instead of a global duality between $H^i_c$ and $H_{d-i}$, we now have local versions, allowing local computations and so on... Just for completeness, if $S$ is a point, $f_*f^!\mathbb{Z}$ is the Borel-Moore homology and $f_!f^!\mathbb{Z}$ is the ordinary homology (at least if $X$ is nice enough).
Executive summary: the isomorphism is not particularly mysterious. It arises from the obvious choice of a trace from the group algebra of $G$ to the group algebra of $H$, given by the formula
$$\sum_{g \in G} c_g g \mapsto \sum_{h \in H} c_h h,$$ which induces an isomorphism from $RG$ onto $\mathrm{Hom}_{RH}(RG,RH)$ (here $R$ is an arbitrary commutative ring), and hence an isomorphism of the functors from induction (tensor product with $RG$) to co-induction (tensor product with $\mathrm{Hom}_{RH}(RG,RH)$).
Now for the details. First, it may help contextualize things to ask a somewhat more general question: given a homomorphism $R \to S$ of rings, when are the induction $S \otimes_R \bullet$ and co-indunction $\mathrm{Hom}_R(S,\bullet)$ functors isomorphic, and when this is the case, how can one describe the set of all such isomorphisms?
There are two relatively obvious conditions that $S$ must satisfy if this is the case. First, since $S \otimes_R \bullet$ is right-exact, if there is such an isomorphism then $\mathrm{Hom}_R(S,\bullet)$ must be right-exact as well, implying that $S$ is projective as a left $R$-module. Second, since $S \otimes_R \bullet$ commutes with arbitrary direct sums, so must $\mathrm{Hom}_R(S,\bullet)$, implying that $S$ is finitely generated as a left $R$-module.
Suppose now that $S$ is projective and finitely generated as a left $R$-module. Then, just as for vector spaces over a field, the map $\mathrm{Hom}_R(S,R) \otimes_R M \to \mathrm{Hom}_R(S,M)$ given by $\phi \otimes m \mapsto [s \mapsto \phi(s) m]$ is a functorial isomorphism: this is true if $S=R$, hence for finitely generated free modules, hence for their summands, the finitely generated projective $R$-modules.
Summing up: an isomorphism from induction to co-induction can exist only if $S$ is finitely generated and projective as an $R$-module, and in this case it is the same thing as an isomorphism of $(S,R)$-bimodules
$$S \longrightarrow \mathrm{Hom}_R(S,R).$$ In fact, there is an isomorphism
$$\mathrm{Hom}_{R,R}(S,R) \to \mathrm{Hom}_{S,R}(S, \mathrm{Hom}_R(S,R))$$ given by sending a "trace" $t: S \to R$ to the map $s \mapsto [s' \mapsto t(s' s)].$ So what we are looking for is a trace from $S$ to $R$ (that is, a morphism of $R,R$-bimodules) such that the corresponding map from $S$ to $\mathrm{Hom}_R(S,R)$ is an isomorphism (necessarily of $S,R$-bimodules).
Now we specialize all this to group algebras. Thus let $H \leq G$ be a subgroup of the finite group $G$, and let $R$ be a commutative ring (we could more generally work with a map from one group $H$ to another $G$, but there is a simple obstruction: we need $RG$ to be a projective $RH$-module, which fails e.g. if the map is trivial and the order of $H$ is not invertible in $R$). Thus $RG$ is a finitely generated free, and hence projective, $RH$ module. An obvious candidate for a trace from $RG$ to $RH$ is the projection map
$$\sum_{g \in G} c_g g \mapsto \sum_{h \in H} c_h h.$$ One checks that this induces an isomorphism of $RG$ onto $\mathrm{Hom}_{RH}(RG,RH)$, mapping $g \in G$ to the function $$g' \mapsto \begin{cases} g' g \quad \hbox{if $g' g \in H$, and} \\ 0 \quad \hbox{ else.} \end{cases}$$ and hence an isomorphism from induction onto co-induction. Given an $RH$-module $M$ this map may be written explicitly as follows: it is given on $g \otimes m \in RG \otimes M$ by
$$g \otimes m \mapsto \left[ g' \mapsto \begin{cases} g' g m \quad \hbox{if $g' g \in H$, and} \\ 0 \quad \hbox{else.} \end{cases} \right]$$ In the case in which the order of $H$ is invertible in $R$, the inverse of this map is recorded in the first answer you linked to in your question.
This answer is long enough as it is, but I would like to mention that although the unit-counit compositions you mention in your question are not necessary for understanding the induction-coinduction isomorphism, they are useful for analyzing relative projectivity of modules over group algebras, and in particular for the theory of vertices and sources and the Green correspondence, as indicated in the next paragraph.
The composition of the unit for the co-induction and the co-unit for the induction produces an endomorphism of the identity functor on $RG$-mod, or in other words a central element of $RG$. This central element can be computed explicitly: it is simply the index $[G:H]$. As a consequence, if this index is invertible in $R$ then every $RG$-module is a summand of its induction-restriction to $H$, and in particular is relatively $H$-projective. So in case $R$ is a field of characteristic $p$, every $RG$-module is relatively $H$-projective provided $H$ contains a Sylow $p$-subgroup of $G$.
Best Answer
Suppose $\mathcal{C}$ is monoidal closed, with tensor product $- \otimes -$ and internal hom $[-,-]$. Suppose we have adjoint functors $L,R \colon \mathcal{C} \to \mathcal{C}$ that 'internalize', by which I mean there's a natural isomorphism
$$ [LX, Y] \cong [X, RY] .$$
Then up to natural isomorphism $L$ must be given by tensoring by some object $A$, and $R$ must be $[A , -]$. That is, there must exist natural isomorphisms
$$ LX \cong X \otimes A $$
and
$$ RX \cong [A, X]. $$
Conversely, any functor of the form $LX \cong X \otimes A$ does internalize in this way.
To see these facts, first remember the proof that any functor of the form $LX \cong X \otimes A$ does internalize:
$$ [X \otimes A, Y] \cong [X, [A, Y]]. $$
By the Yoneda lemma, this is equivalent to
$$ \mathrm{hom}(Z, [X \otimes A, Y]) \cong \mathrm{hom}(Z, [X, [A, Y]]) .$$
where the isomorphism is natural in all four arguments. Using the hom-tensor adjunction once on each side, this is equivalent to
$$ \mathrm{hom}(Z \otimes X \otimes A, Y) \cong \mathrm{hom}(Z \otimes X, [A, Y]]) $$
and using it again on the right side, this is equivalent to
$$ \mathrm{hom}(Z \otimes X \otimes A, Y) \cong \mathrm{hom}(Z \otimes X \otimes A, Y) $$
so we're done!
Now let's try to copy this argument starting from an arbitrary pair of adjoint functors $L, R \colon \mathcal{C} \to \mathcal{C}$. When do we have a natural isomorphism
$$ [LX, Y] \cong [X, RY] ? $$
By the Yoneda lemma, this is equivalent to
$$ \mathrm{hom}(Z, [LX, Y]) \cong \mathrm{hom}(Z, [X, RY]) .$$
where the isomorphism is natural in all four arguments. Using the hom-tensor adjunction once on each side, this is equivalent to
$$ \mathrm{hom}(Z \otimes LX, Y) \cong \mathrm{hom}(Z \otimes X, RY) $$
and using the adjunction between $L$ and $R$, this is equivalent to
$$ \mathrm{hom}(Z \otimes LX, Y) \cong \mathrm{hom}(L(Z \otimes X), Y) $$
Using the Yoneda lemma again, this is equivalent to
$$ Z \otimes LX \cong L(Z \otimes X) \qquad (\star) $$
In short, the existence of a natural isomorphism $[LX, Y] \cong [X, RY]$ is equivalent to $(\star)$.
But if $(\star)$ holds, we can take $X$ to be the unit of the tensor product and see
$$ Z \otimes LI \cong L(Z \otimes I) \cong LZ $$
It follows that up to natural isomorphism, $L$ must be given by tensoring with the object $A = LI$:
$$ LZ \cong Z \otimes A$$
Note that we never needed $\mathcal{C}$ to be symmetric.