[Solved] Split a numeric ID column into two using pandas

by


Option 1
The simplest and most direct – use the str accessor.

v = df.ID.astype(str)
df['Year'], df['ID'] = v.str[:4], v.str[4:]

df

              DateTime  Junction  Vehicles       ID  Year
0 2015-11-01  00:00:00         1        15  1101001  2015
1 2015-11-01  01:00:00         1        13  1101011  2015
2 2015-11-01  02:00:00         1        10  1101021  2015
3 2015-11-01  03:00:00         1         7  1101031  2015
4 2015-11-01  04:00:00         1         9  1101041  2015
5 2015-11-01  05:00:00         1         6  1101051  2015
6 2015-11-01  06:00:00         1         9  1101061  2015
7 2015-11-01  07:00:00         1         8  1101071  2015
8 2015-11-01  08:00:00         1        11  1101081  2015
9 2015-11-01  09:00:00         1        12  1101091  2015

Option 2
str.extract

v = df.ID.astype(str).str.extract('(?P<Year>\d{4})(?P<ID>.*)', expand=True)
df = pd.concat([df.drop('ID', 1), v], 1)

df

              DateTime  Junction  Vehicles  Year       ID
0 2015-11-01  00:00:00         1        15  2015  1101001
1 2015-11-01  01:00:00         1        13  2015  1101011
2 2015-11-01  02:00:00         1        10  2015  1101021
3 2015-11-01  03:00:00         1         7  2015  1101031
4 2015-11-01  04:00:00         1         9  2015  1101041
5 2015-11-01  05:00:00         1         6  2015  1101051
6 2015-11-01  06:00:00         1         9  2015  1101061
7 2015-11-01  07:00:00         1         8  2015  1101071
8 2015-11-01  08:00:00         1        11  2015  1101081
9 2015-11-01  09:00:00         1        12  2015  1101091

2

solved Split a numeric ID column into two using pandas