Pandas groupby and contour plot with NaN

Question

I have a DataFrame in the form of:

   Lon Lat  N
0   2   1   1
1   2   2   2
2   2   3   1
3   2   2   1
4   3   2   1
5   3   2   1

where Lon and Lat represent the coordinates (locations), N represents the count number at corresponding locations.

Now, I want to:

1. Compute the sum of N at each point to achieve as follows:

   Lon Lat  N
0   2   1   1
1   2   2   3
2   2   3   1
3   3   2   2

2. Given a meshgrid Lon_grid = np.arange(1,5), Lat_grid = np.arange(1,4), i.e., a 4 * 3 matrix, and then plot the data in contour. Note that locations with no data can be filled with NaN or 0.

If there is not enough data to make a contour, the final results can be like this:

   Lon Lat  N
0   1   1   0
1   2   1   1
2   3   1   0
3   4   1   0
4   1   2   0
5   2   2   3
6   3   2   2
7   4   2   0
8   1   3   0
9   2   3   1
10  3   3   0
11  4   3   0

Thank you very much for your help!

Answer 1

Here is an example with the provided data:

df2 = df.pivot_table(index='Lon', columns='Lat', values='N',
                     aggfunc='sum', fill_value=0)


X,Y = np.meshgrid(df2.index.to_numpy(), df2.columns.to_numpy())

plt.contourf(X, Y, df2.values.T)
plt.colorbar()
plt.show()

output:

For the aggregated data (which I didn't use here):

out = df.groupby(['Lon', 'Lat'], as_index=False).sum()