Assign same values in column A for absolute numbers in column B in a pandas dataframe

user_15376262 0
Viewed 130

I have a pandas dataframe that looks like this:

import pandas as pd
import numpy as np
d = {'value1': [1, 2, 3, -4, 5, -1, -7, -2, 4, -5], 'value2': [100, 200, 200, 300, np.nan, np.nan, np.nan, np.nan, np.nan, np.nan]}
df = pd.DataFrame(data=d)

value1  value2
1       100.0
2       200.0
3       200.0
-4      300.0
5       NaN
-1      NaN
-7      NaN
-2      NaN
4       NaN
-5      NaN

How do I assign the same value in column value2 for all absolute numbers in column value1? Like so:

value1  value2
1       100.0
2       200.0
3       200.0
-4      300.0
5       NaN
-1      100.0
-7      NaN
-2      200.0
4       300.0
-5      NaN
pandas python
2 Answers

Use Series.map by new Series by converting value1 to absolute values, converted to index, remove missing values and mapping absolute values of original:

s = df.assign(value1 = df['value1'].abs()).set_index('value1')['value2'].dropna()
df['value2'] = df['value1'].abs().map(s)
#if possible duplicates in `s.index` convert to dict (last dupe is in dict)
#df['value2'] = df['value1'].abs().map(s.to_dict())
print (df)
   value1  value2
0       1   100.0
1       2   200.0
2       3   200.0
3      -4   300.0
4       5     NaN
5      -1   100.0
6      -7     NaN
7      -2   200.0
8       4   300.0
9      -5     NaN

EDIT: Error means there are duplicates in value1, so first remove missing values, convert to absolute values and remove duplicates with keep first (default) or last rows for dupes.

d = {'value1': [1, 2, 3, -4, 5, -1, -7, -2, 4, -5, -1],
     'value2': [100, 200, 200, 300, np.nan, np.nan, np.nan, np.nan, np.nan, np.nan,500]}
df = pd.DataFrame(data=d)

s1 = df.dropna(subset=['value2']).assign(value1 = lambda x: x['value1'].abs()).drop_duplicates('value1').set_index('value1')['value2']
s2 = df.dropna(subset=['value2']).assign(value1 = lambda x: x['value1'].abs()).drop_duplicates('value1', keep='last').set_index('value1')['value2']

df['value21'] = df['value1'].abs().map(s1)
df['value22'] = df['value1'].abs().map(s2)
print (df)
    value1  value2  value21  value22
0        1   100.0    100.0    500.0
1        2   200.0    200.0    200.0
2        3   200.0    200.0    200.0
3       -4   300.0    300.0    300.0
4        5     NaN      NaN      NaN
5       -1     NaN    100.0    500.0
6       -7     NaN      NaN      NaN
7       -2     NaN    200.0    200.0
8        4     NaN    300.0    300.0
9       -5     NaN      NaN      NaN
10      -1   500.0    100.0    500.0
user_29010020

You can create a mapping with the absolute values and use it to replace your other values. For this create a Series from the absolute values, drop NaNs. Finally, map this Series to the absolute values of "Value1".

s = df.assign(value1=df['value1'].abs()).dropna().set_index('value1')['value2']
df['value2'] = df['value1'].abs().map(s)

output:

   value1  value2
0       1   100.0
1       2   200.0
2       3   200.0
3      -4   300.0
4       5     NaN
5      -1   100.0
6      -7     NaN
7      -2   200.0
8       4   300.0
9      -5     NaN
user_163434640
Related