0

I'm trying to conditionally add random values within the expected range.

data = 

LOT NO  QTY(Kgs)    % PICK      11C     12C     13C      14C    15C     16C
H19       312        6.22                       
H20       936        18.67                      
H21       989        19.72                      
H22       559        11.15                      
H23       639        12.74                      
H24       736        14.68                      
H25       843        16.81

Where 11c to 16C are columns with null values(nans) or zeros.

I want to add or replace with random values(int & float) with a set of conditions

  1. The avg sum of the values in column 11C should be in the range between 9-12.5.
  2. The avg sum of the values in column 12C should be in the range between 43-47. 3. 4. Other conditions are below
11C    |    12C   |     13C  |   14C    |     15C    |   16C
--------------------------------------------------------------
9-12.5 |  45+/-2  |  205-230 |  5.0-6.0 |  <1000     |  <1500
---------------------------------------------------------------

My Expected Output:

LOT NO    QTY (Kgs)   % PICK    11C     12C     13C  14C    15C 16C
H19       312          6.22     10.50   45.30   247  5.46   53  430
H20       936          18.67    10.38   48.48   265  5.64   67  280
H21       989          19.72    10.62   44.38   264  5.66   73  325
H22       559          11.15    10.97   43.52   226  5.54   62  365
H23       639          12.74    10.89   46.53   205  5.71   84  345
H24       736          14.68    11.09   43.76   165  5.62   93  230
H25       843          16.81    11.01   39.96   137  5.68   95  160

How can I do that?

Improve this question

2 Answers 2

Reset to default
2

UPDATED

Ok let's say df2 is your initial DataFrame.Here is an example using a dictionary for conditions:

import pandas as pd
import numpy as np

df = pd.DataFrame()
df2 = pd.DataFrame([[1, 2, 3, 4, 5, 6], [1, 2, 3, 4, 5, 6]],
      columns=['11C', '12C', '13C', '14C', '15C', '16C'])


def n(_min, _max=None, rows=7, getint=None):
    if getint == 'AVG':
        return [round(x, 2) for x in _min + (_max - _min) * np.random.rand(rows)]
    _min = int(_min / rows)
    return np.random.choice(_min, rows)


conditions = {'11C': n(9, 12.5, getint='AVG'), '12C': n(43, 47, getint='AVG'), '13C': n(205, 230, getint='AVG'),
              '14C': n(5, 6, getint='AVG'), '15C': n(1000, None), '16C': n(1500, None)}
for key, val in conditions.items():
    df[key] = val

print(df)
df2.update(df)

df2.update(df) will update all the keys of df that are in df2 but be sure that they have the same numbers of rows while update() will update the existing number of rows.

Result

     11C    12C     13C   14C  15C  16C
0  11.37  43.43  223.43  5.66  126  181
1  11.67  45.08  217.87  5.80   91   16
2   9.39  43.95  218.13  5.24   69   71
3  12.23  44.74  215.62  5.87   11  129
4  12.42  45.86  209.75  5.05    5  132
5   9.49  45.28  227.34  5.83    2    4
6   9.35  45.08  218.40  5.34  129   48
Improve this answer
Sign up to request clarification or add additional context in comments.

12 Comments

1.How to replace the values to the existing dataframe. 2. Column 13C must be int 3. Is there anyway to round the decimals to two places within np.random.rand
@Krishna 2 and 3 are updated above. Now in which dataframe you wan to replace?
One more clarification: The avg sum of the values in columns should be in the specified range. but using the code gives random values in the range. Let's say the number of rows=7. The average of each column should be in the limits as mentioned in the question.
this was really long process, so +1
@Krishna you confused me a bit, I hope this is what you were looking for.
|
0

you can do it like below using np.random functions

df = pd.DataFrame()
n_rows = 10
df["11C"] = 9+ (12.5-9)*np.random.rand(n_rows)
df["12C"] = 43+ (47-43)*np.random.rand(n_rows)
df["13C"] = 205+ (330-205)*np.random.rand(n_rows)
df["14C"] = 5+ (5-6)*np.random.rand(n_rows)

df["15C"] = np.random.choice(1000, n_rows)
df["15C"] = np.random.choice(1500, n_rows)
df
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.