Dataframe compare, combine and merge for rectangular meshgrid-CodePudding

I have two dataframes shown below:

df_1 =

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

and

df_2 =

    Lon Lat N
0   1.0 1.0 NaN
1   2.0 1.0 NaN
2   3.0 1.0 NaN
3   4.0 1.0 NaN
4   1.0 2.0 NaN
5   2.0 2.0 NaN
6   3.0 2.0 NaN
7   4.0 2.0 NaN
8   1.0 3.0 NaN
9   2.0 3.0 NaN
10  3.0 3.0 NaN
11  4.0 3.0 NaN

What I want to do is to compare these two dfs and merge them according to Lon and Lat. That is to say NaN in df_2 will be covered with values in df_1 if the corresponding Lon and Lat are identical. The ideal output should be as:

    Lon Lat N
0   1.0 1.0 NaN
1   2.0 1.0 1
2   3.0 1.0 NaN
3   4.0 1.0 NaN
4   1.0 2.0 NaN
5   2.0 2.0 3
6   3.0 2.0 2
7   4.0 2.0 NaN
8   1.0 3.0 NaN
9   2.0 3.0 1
10  3.0 3.0 NaN
11  4.0 3.0 NaN

The reason I want to do this is df_1's coordinates Lat and Lon are non-rectangular or unstructured grid, and I need to fill some NaN values so as to get a rectangular meshgrid and make contourf applicable. It would be highly appreciated if you can provide better ways to make the contour plot.

I have tried df_2.combine_first(df_1), but it doesn't work.

Thanks!

CodePudding user response：

If you first create the df_2 with all needed values you can update it with the second DataFrame by using pandas.DataFrame.update.

For this you need to first set the the correct index by using pandas.DataFrame.set_index.

Have a look at this Post for more information.

CodePudding user response：

df_2.drop(columns = 'N').merge(df_1, on = ['Lon', 'Lat'], how = 'left')

    Lon  Lat    N
0   1.0  1.0  NaN
1   2.0  1.0  1.0
2   3.0  1.0  NaN
3   4.0  1.0  NaN
4   1.0  2.0  NaN
5   2.0  2.0  3.0
6   3.0  2.0  2.0
7   4.0  2.0  NaN
8   1.0  3.0  NaN
9   2.0  3.0  1.0
10  3.0  3.0  NaN
11  4.0  3.0  NaN