CODEWITHSUNDEEP
pythonpandasrange
Taylrl
Taylrl

Reputation: 3919

Creating a column with values in ranges that depend on another column using Python

I have a Pandas DataFrame that contains IDs with associated values and for each value and I want to create a new column that has a value that ranges around the initial value by a determinable amount. My initial data looks like this;

iD     Value
AAAA   10
AAAA   15
AAAA   5
BBBB   10
BBBB   6
BBBB   11
CCCC   8 
CCCC   16
CCCC   14

The code I have so far is the following, where the range is set to +/- 1;

def add_range(lower, higher, oldval):
for i in range(lower,higher):
    print i
    newval = oldval + i
return newel

lower = -1
higher = 1

MSet['NewVal'] = MSet.apply(lambda row: add_range(row['Ords'],lower,higher), axis =1)

What I would like to get to is;

iD     Value   NewVal
AAAA   10      9
AAAA   10      10
AAAA   10      11
AAAA   15      14
AAAA   15      15
AAAA   15      16
AAAA   5       4
AAAA   5       5
AAAA   5       6
BBBB   10      9
BBBB   10      10
BBBB   10      11
BBBB   6       5
BBBB   6       6
BBBB   6       7      
BBBB   11      10
BBBB   11      11
BBBB   11      12
CCCC   8       7
CCCC   8       8
CCCC   8       9
CCCC   16      14
CCCC   16      15
CCCC   16      16
CCCC   14      13
CCCC   14      14
CCCC   14      15

Any tips or suggestions would be massively appreciated.

Thanks!

Upvotes: 1

Views: 1002

Answers (2)

BENY
BENY

Reputation: 323226

pandas.repeat, then using groupby, and unlist 

df1=df.loc[df.index.repeat(3)]
import operator
df1.groupby(['iD','Value'],as_index=False).apply(lambda x :list(map(operator.sub, x['Value'], [1,0,-1]))).\
    apply(pd.Series).stack().reset_index().\
        drop('level_2',1).rename(columns={0:'New'})

Out[253]: 
      iD  Value  New
0   AAAA      5    4
1   AAAA      5    5
2   AAAA      5    6
3   AAAA     10    9
4   AAAA     10   10
5   AAAA     10   11
6   AAAA     15   14
7   AAAA     15   15
8   AAAA     15   16
9   BBBB      6    5
10  BBBB      6    6
11  BBBB      6    7
12  BBBB     10    9
13  BBBB     10   10
14  BBBB     10   11
15  BBBB     11   10
16  BBBB     11   11
17  BBBB     11   12
18  CCCC      8    7
19  CCCC      8    8
20  CCCC      8    9
21  CCCC     14   13
22  CCCC     14   14
23  CCCC     14   15
24  CCCC     16   15
25  CCCC     16   16
26  CCCC     16   17

Upvotes: 1

piRSquared
piRSquared

Reputation: 294258

Option 1
Simpler with loc 

df.loc[df.index.repeat(3)].assign(
    NewVal=(df.Value.values[:, None] + [-1, 0, 1]).ravel())

Option 2
Or rebuild the entire dataframe

i = df.iD.values
v = df.Value.values

pd.DataFrame(
    np.column_stack([
        i.repeat(3),
        v.repeat(3),
        (v[:, None] + [-1, 0, 1]).ravel()
    ]), columns=['iD', 'Value', 'NewVal']
)

Both options make use of adding [-1, 0, 1] to the values using numpy broadcasting and then ravel

      iD Value NewVal
0   AAAA    10      9
1   AAAA    10     10
2   AAAA    10     11
3   AAAA    15     14
4   AAAA    15     15
5   AAAA    15     16
6   AAAA     5      4
7   AAAA     5      5
8   AAAA     5      6
9   BBBB    10      9
10  BBBB    10     10
11  BBBB    10     11
12  BBBB     6      5
13  BBBB     6      6
14  BBBB     6      7
15  BBBB    11     10
16  BBBB    11     11
17  BBBB    11     12
18  CCCC     8      7
19  CCCC     8      8
20  CCCC     8      9
21  CCCC    16     15
22  CCCC    16     16
23  CCCC    16     17
24  CCCC    14     13
25  CCCC    14     14
26  CCCC    14     15

Upvotes: 3

Related Questions