How can I update an existing dataframe to add values, without overwriting other existing values in t-CodePudding

I have an existing dataframe with two columns as follows:

       reason         market_state
0        NaN        UNSCHEDULED_AUCTION
1        NaN                 None
2        NaN               CLOSED
3        NaN      CONTINUOUS_TRADING
4        NaN                 None
5        NaN     UNSCHEDULED_AUCTION
6        NaN     UNSCHEDULED_AUCTION
7         F                 None
8        NaN      CONTINUOUS_TRADING
9         SL                 None
10       NaN               HALTED
11       NaN                 None
12       NaN                 None
13        L                  None

I am trying to apply the following 3 mappings to the above dataframe:

market_info_df['market_state'] = market_info_df['reason'].map({'F': OPENING_AUCTION})
market_info_df['market_state'] = market_info_df['reason'].map({'SL': CLOSING_AUCTION})
market_info_df['market_state'] = market_info_df['reason'].map({'L': CLOSED})

But when I run the above 3 lines, it seems to overwrite the existing mappings:

    market_state  reason
0        NaN       NaN
1        NaN       NaN
2        NaN       NaN
3        NaN       NaN
4        NaN       NaN
5        NaN       NaN
6        NaN       NaN
7        NaN        F
8        NaN       NaN
9        NaN        SL
10       NaN       NaN
11       NaN       NaN
12       NaN       NaN
13      CLOSED      L

(And it seems to have swapped the columns? - though this doesn't matter)

Each of the lines seems to overwrite the dataframe. Is there a way simply to update the dataframe, i.e. so it just updates the three mappings, like this:

      reason         market_state
0      NaN         UNSCHEDULED_AUCTION
1      NaN                 None
2      NaN               CLOSED
3      NaN        CONTINUOUS_TRADING
4      NaN                 None
5      NaN        UNSCHEDULED_AUCTION
6      NaN        UNSCHEDULED_AUCTION
7       F           OPENING_AUCTION
8      NaN       CONTINUOUS_TRADING
9       SL          CLOSING_AUCTION
10     NaN               HALTED
11     NaN                None
12     NaN                None
13      L               CLOSED

CodePudding user response：

Join values to one dictionary and add Series.fillna by same column market_state:

d = {'F': 'OPENING_AUCTION','SL': 'CLOSING_AUCTION', 'L': 'CLOSED'}
market_info_df['market_state'] = (market_info_df['reason'].map(d)
                                             .fillna(market_info_df['market_state']))
print (market_info_df)
   reason         market_state
0     NaN  UNSCHEDULED_AUCTION
1     NaN                 None
2     NaN               CLOSED
3     NaN   CONTINUOUS_TRADING
4     NaN                 None
5     NaN  UNSCHEDULED_AUCTION
6     NaN  UNSCHEDULED_AUCTION
7       F      OPENING_AUCTION
8     NaN   CONTINUOUS_TRADING
9      SL      CLOSING_AUCTION
10    NaN               HALTED
11    NaN                 None
12    NaN                 None
13      L               CLOSED

CodePudding user response：

Use a single dictionary, then fillna with the original values if needed:

market_info_df['market_state'] = (
 market_info_df['reason']
  .map({'F': 'OPENING_AUCTION',   # only ONE dictionary
        'SL': 'CLOSING_AUCTION',
        'L': 'CLOSED'})
 .fillna(market_info_df['market_state'])
)

Or, to only update the NA values:

df.loc[df['market_state'].isna(), 'market_state'] = (
    market_info_df['reason']
     .map({'F': 'OPENING_AUCTION',   # only ONE dictionary
           'SL': 'CLOSING_AUCTION',
           'L': 'CLOSED'})
)

Output:

   reason         market_state
0     NaN  UNSCHEDULED_AUCTION
1     NaN                 None
2     NaN               CLOSED
3     NaN   CONTINUOUS_TRADING
4     NaN                 None
5     NaN  UNSCHEDULED_AUCTION
6     NaN  UNSCHEDULED_AUCTION
7       F      OPENING_AUCTION
8     NaN   CONTINUOUS_TRADING
9      SL      CLOSING_AUCTION
10    NaN               HALTED
11    NaN                 None
12    NaN                 None
13      L               CLOSED