Conditionally use replace on column by replacing a string with 10 or 100 respectively to keep the ta-CodePudding

I have the following air pressure data in a column. I need to replace 'sp' with 10 or 100 depending on the amount of numbers currently in the row and how much needed to keep it above 1000. can someone please assist? For example, 'sp25' will only need the sp part replaced with 10 but 'sp3' will need sp part to be replaced with 100. Hope this makes sense :)

0       sp25
1       sp25
2       sp25
3       sp25
4       sp25
        ... 
8758     sp3
8759     sp6
8760    sp22
8761    sp23
8762    sp25

CodePudding user response：

Assuming col you column, if you want strings you can use:

df['col2'] = '1' df['col'].str.replace('sp', '').str.zfill(3)

If you want integers:

df['col2'] = 1000   pd.to_numeric(df['col'].str.replace('sp', ''))

output:

       col  col2
0     sp25  1025
1     sp25  1025
2     sp25  1025
3     sp25  1025
4     sp25  1025
8758   sp3  1003
8759   sp6  1006
8760  sp22  1022
8761  sp23  1023
8762  sp25  1025