Mapping column value to another column based condition-CodePudding

I have pandas dataframe that looks like this:

ID  Value   datum1  value1      datum2      value2      datum3      value3
111 -2.18   NAVD88  3.54601048  NGVD29      -4.13   Local Assumed Datum -0.93
111 -2.4    NAVD88  3.32601048  NGVD29      -3.91   Local Assumed Datum -1.15
111 -2.83   NAVD88  2.89601048  NGVD29      -3.48   Local Assumed Datum -1.58
111 -2.36   NAVD88  3.36601048  NGVD29      -3.95   Local Assumed Datum -1.11
111 -2.06   NAVD88  3.66601048  NGVD29      -4.25   Local Assumed Datum -0.81
111 -3.99   NAVD88  1.73601048  NGVD29      -2.32   Local Assumed Datum -2.74
111 -4.12   NAVD88  1.60601048  NGVD29      -2.19   Local Assumed Datum -2.87
111 -2.78   NAVD88  2.94601048  NGVD29      -3.53   Local Assumed Datum -1.53
111 -2.38   NAVD88  3.34601048  NGVD29      -3.93   Local Assumed Datum -1.13
111 -2.44   NAVD88  3.28601048  NGVD29      -3.87   Local Assumed Datum -1.19
967 -8.18   NAVD88  101.21  Local Assumed Datum -6.21   NAN         NAN
967 -9.8    NAVD88  99.59   Local Assumed Datum -7.83   NAN         NAN
967 -9.48   NAVD88  99.91   Local Assumed Datum -7.51   NAN         NAN
967 -9.22   NAVD88  100.17  Local Assumed Datum -7.25   NAN         NAN
967 -8.34   NAVD88  101.05  Local Assumed Datum -6.37   NAN         NAN
967 -8.8    NAVD88  100.59  Local Assumed Datum -6.83   NAN         NAN
967 -8.61   NAVD88  100.78  Local Assumed Datum -6.64   NAN         NAN
967 -8.13   NAVD88  101.26  Local Assumed Datum -6.16   NAN         NAN

Where the column datum2 = "NGVD29", I'd like to map the values from datum3 --> datum2 and value3 -->value2.

The dataframe I'm trying to get would look like:

ID  Value   datum1  value1      datum2      value2      datum3      value3
111 -2.18   NAVD88  3.54601048  Local Assumed Datum -0.93   Local Assumed Datum -0.93
111 -2.4    NAVD88  3.32601048  Local Assumed Datum -1.15   Local Assumed Datum -1.15
111 -2.83   NAVD88  2.89601048  Local Assumed Datum -1.58  Local Assumed Datum -1.58
111 -2.36   NAVD88  3.36601048  Local Assumed Datum -1.11   Local Assumed Datum -1.11
111 -2.06   NAVD88  3.66601048  Local Assumed Datum -0.81   Local Assumed Datum -0.81
111 -3.99   NAVD88  1.73601048  Local Assumed Datum -2.74  Local Assumed Datum -2.74
111 -4.12   NAVD88  1.60601048  Local Assumed Datum -2.87   Local Assumed Datum -2.87
111 -2.78   NAVD88  2.94601048  Local Assumed Datum -1.53   Local Assumed Datum -1.53
111 -2.38   NAVD88  3.34601048  Local Assumed Datum -1.13   Local Assumed Datum -1.13
111 -2.44   NAVD88  3.28601048  Local Assumed Datum -1.19   Local Assumed Datum -1.19
967 -8.18   NAVD88  101.21      Local Assumed Datum -6.21   NAN         NAN
967 -9.8    NAVD88  99.59       Local Assumed Datum -7.83   NAN         NAN
967 -9.48   NAVD88  99.91       Local Assumed Datum -7.51   NAN         NAN
967 -9.22   NAVD88  100.17      Local Assumed Datum -7.25   NAN         NAN
967 -8.34   NAVD88  101.05      Local Assumed Datum -6.37   NAN         NAN
967 -8.8    NAVD88  100.59      Local Assumed Datum -6.83   NAN         NAN
967 -8.61   NAVD88  100.78      Local Assumed Datum -6.64   NAN         NAN
967 -8.13   NAVD88  101.26      Local Assumed Datum -6.16   NAN         NAN

After which I could drop the columns datum3 and value3. Using the line:

df['value2']=df['value3'][df['datum2']=='NGVD29']

semi-achieves this but erases the values where datum2 = "Local Assumed Datum".

CodePudding user response：

IIUC, you can try:

m = df['Datum2'].eq('NGVD29')
df.loc[m, ['Datum2', 'value2']] = df[m][['Datum3', 'value3']].values