From the Wikipedia article on sound:
In physics, sound is a vibration that propagates as a typically
audible mechanical wave of pressure and displacement.
To fully understand how is air vibrating in an open pipe, you have to consider not only the acoustic pressure wave,
$$\frac{\partial^2 p}{\partial x^2}=\frac{1}{c^2}\frac{\partial^2 p}{\partial t^2}$$
(here $c$ is the speed of sound) but also the variation in the flow of air, that is, a particle displacement wave:
We consider the air in the tube to have a rest position, and the wave motion is expressed in terms of displacement from that position. If we denote by $ξ(x,t)$ the displacement of the air at position x at time t, then the wave equation for displacement is (I added a derivation at the end of this answer):
$$\frac{\partial^2 ξ}{\partial x^2}=\frac{1}{c^2}\frac{\partial^2 ξ}{\partial t^2}$$
As you cited, the pressure at the open end must be equal to the pressure of ambient air outside the pipe: this is just a boundary condition (I'll come back to this later, but a simple way to understand it is: if we didn't satisfy continuity of pressure, we'd have an infinite pressure gradient and so infinite force). However, for an open end, air can flow freely in and out: that is, the displacement wave does exit and propagate. This diagram shows what happens in an open (left) vs closed pipe (right) of the same length:
The red line is the amplitude of the variation in pressure, whereas the blue line is the amplitude of the variation in the flow of air. As you can see, the amplitude of the displacement wave is a maximum at the open end.
Now, to answer your questions, "why the pressure at the open end cannot vary from the atmospheric pressure?" and "how does the reflection of the wave at the open end occur?", I'll cite a passage from this wonderful explanation I've found (you should take a look at the animations if this is not clear enough, or alternatively at this video): Let's send a pulse of air down a cylindrical pipe open at the end. Then...
It reaches the end of the tube and its momentum carries it out into
the open air, where it spreads out in all directions. Now, because it
spreads out in all directions its pressure falls very quickly to
nearly atmospheric pressure (the air outside is at atmospheric
pressure). However, it still has the momentum to travel away from the
end of the pipe. Consequently, it creates a little suction: the air
following behind it in the tube is sucked out (a little like the air
that is sucked behind a speeding truck).
Now a suction at the end of the tube draws air from further up the
tube, and that in turn draws air from further up the tube and so on.
So the result is that a pulse of high pressure air travelling down the
tube is reflected as a pulse of low pressure air travelling up the
tube. We say that the pressure wave has been reflected at the open
end, with a change in phase of 180°.
For a more formal treatment of reflections at an open end, see this. Let me know if I can make myself more clear on any point.
Addendum: A derivation of the two wave equations.
The acoustic pressure is the local pressure deviation from the ambient, that is:
$$p(x,y)=P(x,t)- p_a$$
where $p_a$ is the ambient air pressure, and $P(x,t)$ is the absolute pressure. In this situation, Hooke's law states that
$$p=-B \frac {\partial ξ}{\partial x}$$
where the constant $B$ is the bulk modulus of air (i. e., air's resistance to uniform compression). Newton’s second law of motion implies that
$$\frac {\partial p}{\partial x} = - p_a \frac{\partial^2 ξ}{\partial t^2}$$
Combining these equations, we get
$$\frac{\partial^2 p}{\partial x^2}=\frac{1}{c^2}\frac{\partial^2 p}{\partial t^2}$$
and
$$\frac{\partial^2 ξ}{\partial x^2}=\frac{1}{c^2}\frac{\partial^2 ξ}{\partial t^2}$$
where $c = \sqrt{B/{\rho}}$ (from Newton-Laplace equation), $\rho$ being air density. Source of this derivation: Dave Benson's Music: A Mathematical Offering.
Later addendum:
As @knzhou pointed out in his answer, it should be mentioned that for an open end, $p = 0$ is really only an approximation, because the volume of air just outside of the tube is not infinite. However, you can make a more accurate representation of the actual tube by working in terms of an effective length, which is a little longer than the geometrical end. This diagram shows the effective length for the fundamental vibrational mode of a flute:
The end correction is the amount by which the effective length exceeds the actual length, and under normal conditions it is usually somewhere around three fifths of the width of the tube.
Also, one could wonder what is actually happening at the boundary between the tube and the open air. All I know is that if inside the tube there is a plane wave, and when the displacement wave exits and propagates externally, it becomes a spherical one, then there must be some complicated effect going on between the two (as was suggested by @docscience in the comments). Perhaps a more knowledgeable person can explain it to us.
The answer you seek is actually in the passage.
There are two ways of describing a sound wave: as a variation in pressure wave and as a mean displacement of particles wave as illustrated below.
. . . the excess pressure and displacement corresponding to the same sound wave vary by π/2 in term of phase . . .
. . . a displacement minima at the rigid end will be a point of pressure maxima. . .
So at a rigid boundary there is zero mean displacement whereas the variation in pressure is a maximum.
Which leads on to the statement,
This implies that the reflected pressure wave from the rigid boundary will have same phase as the incident wave, i.e., a compression pulse is reflected as a compression pulse and a rarefaction pulse is reflected as a rarefaction pulse.
If the phase change after reflection from a rigid boundary is π, . . .
This is for the displacement wave not the pressure wave.
Whereas for the pressure wave which is $\pi/2$ out of phase with the displacement wave there is no phase change at a rigid boundary.
Best Answer
When a wave meets a boundary between 2 media in which the wave speed and/or density is different, there is both transmission and reflection. The bigger the difference in wave speed or density, the higher the % of the wave energy which is reflected.
For normal incidence, the ratio of reflected to incident amplitudes for sound waves in fluids is $R=\frac{\rho_1 c_2 - \rho_2 c_1}{\rho_1 c_2+\rho_2 c_1}$
where $\rho, c$ are the densities and speeds in the two media. See section 2 of these notes from MIT.
The speed of sound in air is about $c_1=330m/s$, while in water it is about $c_2=1484m/s$. The densities are $\rho_1=1.225kg/m^3$ and $\rho_2=1000kg/m^3$. So the reflection coefficient is $R=0.989$ and the % reflection of energy is $R^2=97.8$%. Almost all of the sound energy is reflected by the water surface.