Match the first 3 characters of a string to specific column

user_5942100 0
Viewed 421

I have a dataframe,df, where I would like to take the first 3 characters of a string from a specific column and place these characters under another column

Data

id  value   stat
aaa 10      aaa123
aaa 20  
aaa 500     aaa123
bbb 20  
bbb 10      bbb123
aaa 5       aaa123
            aaa123
            ccc123

Desired

  id    value   stat
  aaa   10      aaa123
  aaa   20  
  aaa   500     aaa123
  bbb   20  
  bbb   10      bbb123
  aaa   5       aaa123
  aaa           aaa123
  ccc           ccc123

Doing

 df.append({'aaa':aaa123}, ignore_index=True)

I believe I have to append the values, perhaps using a mapping or append function, however, not sure how to specify first 3 characters. Any suggestion is appreciated

pandas numpy python
1 Answers

One option would be Series.fillna + Series.str to slice the first 3 values:

df['id'] = df['id'].fillna(df['stat'].str[:3])

    id  value    stat
0  aaa   10.0  aaa123
1  aaa   20.0     NaN
2  aaa  500.0  aaa123
3  bbb   20.0     NaN
4  bbb   10.0  bbb123
5  aaa    5.0  aaa123
6  aaa    NaN  aaa123
7  ccc    NaN  ccc123

Probably overkill for this situation, but Series.str.extract could also be used:

df['id'] = df['id'].fillna(df['stat'].str.extract(r'(^.{3})')[0])

mask if those are empty strings and not NaN:

df['id'] = df['id'].mask(df['id'].eq('')).fillna(df['stat'].str[:3])
user_154978880
Related