Replace values by result of a function

user_4739789 0
Viewed 89

I have following dataframe table:

df = pd.DataFrame({'A': [0, 1, 0],
                   'B': [1, 1, 1]},
                  index=['2020-01-01', '2020-02-01', '2020-03-01'])

I'm trying to achieve that every value where 1 is present will be replaced by an increasing number. I'm looking for something like:

df.replace(1, value=3)

that works great but instead of number 3 I need number to be increasing (as I want to use it as ID)

number += 1

If I join those together, it doesn't work (or at least I'm not able to find correct syntax) I'd like to obtain following result:

df = pd.DataFrame({'A': [0, 2, 0],
                   'B': [1, 3, 4]},
                  index=['2020-01-01', '2020-02-01', '2020-03-01'])

Note: I can not use any command that relies on specification of column or row name, because table has 2600 columns and 5000 rows.

pandas dataframe python-3.x python
1 Answers

Element-wise assignment on a copy of df.values can work.

More specifically, a range starting from 1 to the number of 1's (inclusive) is assigned onto the location of 1 elements in the value array. The assigned array is then put back into the original dataframe.

Code

(Data as given)

1. Row-first ordering (what the OP wants)

arr = df.values
mask = (arr > 0)
arr[mask] = range(1, mask.sum() + 1)
for i, col in enumerate(df.columns):
    df[col] = arr[:, i]

# Result
print(df)
            A  B
2020-01-01  0  1
2020-02-01  2  3
2020-03-01  0  4

2. Column-first ordering (another possibility)

arr_tr = df.values.transpose()
mask_tr = (arr_tr > 0)
arr_tr[mask_tr] = range(1, mask_tr.sum() + 1)
for i, col in enumerate(df.columns):
    df[col] = arr_tr[i, :]

# Result
print(df)
            A  B
2020-01-01  0  2
2020-02-01  1  3
2020-03-01  0  4
user_32186930
Related