dataframe value modification randomly

Question

Assume we have the following:

df = pd.DataFrame({'1':[0.1,0.2,0.05,0.6],'2':[0.2,0.4,0.1,0.06],'3':[0.5,0.4,0.05,0.1]})

or:

      1     2     3
0  0.10  0.20  0.50
1  0.20  0.40  0.40
2  0.05  0.10  0.05
3  0.60  0.06  0.10

Sum of columns are 0.95, 0.76, 1.05. The desired summation is 1.

The goal:

make every column add up to 1. Some of the columns add to a number greater than one and some to a number smaller than one.

Constraint:

Add or subtract only from one member in each column.

Desired output:

The output should be something like this:

      1     2     3
0  0.15  0.20  0.50
1  0.20  0.40  0.40
2  0.05  0.10  0.05
3  0.60  0.30  0.05

Sum of all columns are now 1. However, only one element in each column has changed.

My efforts:

My plan has been to calculate how much a column is more/less than one as follows:

1-df.sum(axis=0)

which returns:

1    0.05
2    0.24
3   -0.05
dtype: float64

This will give us a series that contain the difference of each element and one.

I can select a random element of each column by:

df.apply(lambda x: x.sample(1))

which returned (YMMV since it is random selection):

      1    2    3
1   NaN  0.4  NaN
2  0.05  NaN  NaN
3   NaN  NaN  0.1

Now I cannot figure out how to add these values to a random member of each column.

Any help is greatly appreciated.

Answer 1

Using for loop you can do it.

import random
import pandas as pd

df = pd.DataFrame({'1':[0.1,0.2,0.05,0.6],'2':[0.2,0.4,0.1,0.06],'3':[0.5,0.4,0.05,0.1]})

for i in df.columns:
    s=df[i].sum()
    x = random.randint(0,len(df))
    if s > 1:
        df.iloc[x][i] = df.iloc[x][i] - (s-1)

    elif s < 1 :
        df.iloc[x][i] = df.iloc[x][i] + (1-s)

output:

    1       2       3
0   0.1     0.20    0.50
1   0.2     0.40    0.35
2   0.1     0.34    0.05
3   0.6     0.06    0.10