How to use startswith in loc for a dataframe ? Or merger dataframe columns ignoring None?-CodePudding

is there a way to use startswith in a loc function to assign a column value to an other column based on the first column value ?

I've tried :

inventory_at_date.loc[inventory_at_date["asset_class"].str.contains('Cash'), "security_class"] = inventory_at_date["asset_class"]

but I get

ValueError: Cannot mask with non-boolean array containing NA / NaN values

Ex : Column "asset class" contains

0    Cash EUR
1        None
2        None
3        None
4        None
5        None

Column "security class" contains

0        None
1        Equity
2        Equity
3        Equity
4        Equity
5        Equity

Result wanted in "security class" column:

0        Cash EUR
1        Equity
2        Equity
3        Equity
4        Equity
5        Equity

Is there better way to do it ?

Thanks

CodePudding user response：

There is a merger type of function. You can try using combine_first.

df['security class'] = df['asset class'].combine_first(df['security class'])

CodePudding user response：

You can also use np.where to filter NaN values in "security_class" column and fill NaN values there with values from "asset_class" column.

df['security class'] = np.where(df['security_class'].isna(), df['asset_class'], df['security_class'])

Output:

  security_class asset_class security class
0            NaN    Cash EUR       Cash EUR
1         Equity         NaN         Equity
2         Equity         NaN         Equity
3         Equity         NaN         Equity
4         Equity         NaN         Equity
5         Equity         NaN         Equity