I have noticed that in certain categories (e.g. $k$-vector spaces, pointed sets, ...), given an indexed family $\{x_i\}_i$ of objects, we always have a canonical map $$\bigsqcup_ix_i\longrightarrow\prod_ix_i.$$ I was wondering what conditions do we have to require on an arbitrary category to make it so that this always happens.
My thoughts so far: I think that this is somehow linked to the fact that in these categories the initial object is the same as the terminal object. So assume $\mathcal{C}$ is a category with an object $*$ that is both initial and terminal. Then for an index $j$ we can define an object $\pi_j^{-1}(*)$ as the pullback $$\require{AMScd} \begin{CD} \pi_j^{-1}(*) @>>> \prod_ix_i\\ @VVV @VV{\pi_j := \prod_{i\neq j}p_i}V\\ * @>>> \prod_{i\neq j}x_i \end{CD}$$ where $p_i$ are the projections. Dually, using the coproduct we can define an object $\bigsqcup_ix_i\big/\bigsqcup_{i\neq j}x_i$. My idea is that if we can show that any of these two objects is canonically isomorphic to $p_j$ (and intuitively it is what happens in vector spaces and sets), then we'd obtain our morphism by universal property of either the product or the coproduct.
Does anybody know if this is correct? And if it is, how can it be done?
You have the right idea. The fact that $*$ is both initial and terminal (it's then called a zero object) implies that between any two objects $x$ and $y$ there is a unique zero morphism $0 \colon x \to y$, which is the composite of the morphisms $x \to * \to y$.
So if you have a coproduct $\epsilon_i \colon x_i \to \coprod x$ and a product $\pi_i \colon \prod x \to x_i$, you can define a family of maps $\tau_{ij} \colon x_j \to x_i$ by $$ \tau_{ij} = \begin{cases} 1_{x_i} & i = j, \\ 0 & i \ne j. \end{cases} $$ Then for each $i$, the coproduct gives you a unique map $\rho_i \colon \coprod x \to x_i$ satisfying $\rho_i \epsilon_j = \tau_{ij}$. But then the product gives you a unique map $\varphi \colon \coprod x \to \prod x$ satisfying $\pi_i \varphi = \rho_i$.
Similarly, for each $j$, the product gives you a unique map $\sigma_j \colon x_j \to \prod x$ satisfying $\pi_i \sigma_j = \tau_{ij}$. But then the coproduct gives you a unique map $\varphi' \colon \coprod x \to \prod x$ satisfying $\varphi' \epsilon_j = \sigma_j$.
I'll leave it to you to prove that $\varphi = \varphi'$ here. :-)
In the case of a preadditive category, finite coproducts and finite products are equivalent, and in the above construction, $\rho_i = \pi_i$ and $\sigma_j = \epsilon_j$, and $\varphi$ is the identity. See my other answer for more on that.