Better solution for Python Pandas

user_4082453 0
Viewed 80

So I have 2 columns and I would like to create a 3rd column based on the values of the second column. I want to start with a text and a number lets say B0292, and if the second value of the column remains the same, my number in the new column will remain the same. If the number changes, then my number will go one up. Like B0293.

d = {'col1': ['a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j' ], 'col2': ['200', '200', '201', '201', '201', '201', '210', '210', '250', '251']}
df = pd.DataFrame(data=d)
df

the table:

    col1    col2
0   a   200
1   b   200
2   c   201
3   d   201
4   e   201
5   f   201
6   g   210
7   h   210
8   i   250
9   j   251

The result i want:

col1    col2    New Calculated Column
0   a   200 B0292 - 200
1   b   200 B0292 - 200
2   c   201 B0293 - 201
3   d   201 B0293 - 201
4   e   201 B0293 - 201
5   f   201 B0293 - 201
6   g   210 B0294 - 210
7   h   210 B0294 - 210
8   i   250 B0295 - 250
9   j   251 B0296 - 251

I have solved this with the below code, but i am wondering if there is a better more pandas/numpy solution.

df['New Calculated Column'] = ''
a = 291
b = 0
for number in df.col2:
    if number != df.iloc[b-1,1]:
        a += 1    
    df['New Calculated Column'].iloc[(b)] = 'B0' + str(a) + ' - ' + df.iloc[b,1]
    if b < 9:
        b += 1
pandas numpy function python
1 Answers

Assuming your string has numbers starting from the 2nd position , You can try series.factorize with string slicing and addition of series:

s = "B0292"
s1 = s[0] + pd.Series(int(s[1:]) + df['col2'].factorize()[0],dtype=str)
df['New'] = df['col2'].radd(s1+'-')

print(df)

  ccol1 col2       New
0    a  200  B292-200
1    b  200  B292-200
2    c  201  B293-201
3    d  201  B293-201
4    e  201  B293-201
5    f  201  B293-201
6    g  210  B294-210
7    h  210  B294-210
8    i  250  B295-250
9    j  251  B296-251
user_98406370
Related