pandas fill column with random numbers with a total for each row

Question

I've got a pandas dataframe like this:

     id  foo  
 0   A   col1 
 1   A   col2  
 2   B   col1  
 3   B   col3  
 4   D   col4  
 5   C   col2

I'd like to create four additional columns based on unique values in foo column. col1,col2, col3, col4

     id  foo   col1 col2 col3 col4
 0   A   col1   75   20   5    0
 1   A   col2   20   80   0    0
 2   B   col1   82   10   8    0
 3   B   col3   5    4   80   11
 4   D   col4   0    5   10   85
 5   C   col2   12   78   5    5

The logic for creating the columns is as follows:

if foo = col1 then col1 contains a random number between 75-100 and the other columns (col2, col3, col4) contains random numbers, such that the total for each row is 100

I can manually create a new column and assign a random number, but I'm unsure how to include the logic of sum for each row of 100.

Appreciate any help!

BENY · Accepted Answer · 2019-07-10 22:22:02Z

3

My two cents

d=[]
s=np.random.randint(75,100,size=6)

for x in 100-s:
    a=np.random.randint(100, size=3)
    b=np.random.multinomial(x, a /a.sum())
    d.append(b.tolist())
s=[np.random.choice(x,4,replace= False) for x in np.column_stack((s,np.array(d))) ]


df=pd.concat([df,pd.DataFrame(s,index=df.index)],1)
df

  id   foo   0   1   2   3
0  A  col1  16   1   7  76
1  A  col2   4   2  91   3
2  B  col1   4   4   1  91
3  B  col3  78   8   8   6
4  D  col4   8  87   3   2
5  C  col2   2   0  11  87

edited Jul 10, 2019 at 22:22

answered Jul 10, 2019 at 21:48

BENY

324k22 gold badges176 silver badges250 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Scott Boston · Accepted Answer · 2019-07-10 22:02:18Z

2

IIUC,

df['col1'] = df.apply(lambda x: np.where(x['foo'] == 'col1', np.random.randint(75,100), np.random.randint(0,100)), axis=1)

df['col2'] = df.apply(lambda x: np.random.randint(0,100-x['col1'],1)[0], axis=1)

df['col3'] = df.apply(lambda x: np.random.randint(0,100-x[['col1','col2']].sum(),1)[0], axis=1)

df['col4'] = 100 - df[['col1','col2','col3']].sum(1).astype(int)

df[['col1','col2','col3','col4']].sum(1)

Output:

  id   foo col1  col2  col3  col4
0  A  col1   92     2     5     1
1  A  col2   60    30     0    10
2  B  col1   89     7     3     1
3  B  col3   72    12     0    16
4  D  col4   41    52     3     4
5  C  col2   72     2    22     4

answered Jul 10, 2019 at 22:02

Scott Boston

154k15 gold badges160 silver badges207 bronze badges

Comments

piRSquared · Accepted Answer · 2019-07-10 22:06:47Z

My Approach

import numpy as np

def weird(lower, upper, k, col, cols):
    first_num = np.random.randint(lower, upper)
    delta = upper - first_num
    the_rest = np.random.rand(k - 1)
    the_rest = the_rest / the_rest.sum() * (delta)
    the_rest = the_rest.astype(int)
    the_rest[-1] = delta - the_rest[:-1].sum()

    key = lambda x: x != col
    return dict(zip(sorted(cols, key=key), [first_num, *the_rest]))


def f(c): return weird(75, 100, 4, c, ['col1', 'col2', 'col3', 'col4'])

df.join(pd.DataFrame([*map(f, df.foo)]))

  id   foo  col1  col2  col3  col4
0  A  col1    76     2    21     1
1  A  col2    11    76    11     2
2  B  col1    75     4    10    11
3  B  col3     0     1    97     2
4  D  col4     5     4    13    78
5  C  col2     9    77     6     8

Quang Hoang · Accepted Answer · 2019-07-10 22:39:26Z

If we subtract the numbers between 75-100 by 75, the problem become generating a table of random number between 0-25 whose each row sums to 25. That can be solve by reverse cumsum:

num_cols = 4

# generate random number and sort them in each row
a = np.sort(np.random.randint(0,25, (len(df), num_cols)), axis=1)

# create a dataframe and attach a last column with values 25
new_df = pd.DataFrame(a)
new_df[num_cols] = 25

# compute the difference, which are our numbers and add to the dummies:
dummies = pd.get_dummies(df.foo) * 75
dummies += new_df.diff(axis=1).fillna(new_df[0]).values

And dummies is

   col1  col2  col3  col4
0  76.0  13.0   2.0   9.0
1   1.0  79.0   2.0   4.0
2  76.0   5.0   8.0   9.0
3   1.0   3.0  79.0  10.0
4   1.0   2.0   1.0  88.0
5   1.0  82.0   1.0   7.0

which can be concatenated to the original dataframe.

Collectives™ on Stack Overflow

pandas fill column with random numbers with a total for each row

4 Answers 4

Comments

Comments

My Approach

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

My Approach

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related