How to split this column into 2 or more columns. I've used str.split('/',2) to split but it just removed the '/' and did not split into 2 columns.

What I want is:

Even better is if I can have four column headers for the four cardinal directions and filling it with the values from the first table such as:

If I have read on the documentation correctly, I believe this can be done with regex patterns but is there an efficient way to do this concisely?

Here is the original df for use: df = ['East Bound: 6900 / West Bound: 7700', 'East Bound: 7800 / West Bound: 8700', 'North Bound: 5000 / South Bound: 4900', 'North Bound: 7000 / South Bound: 9000', 'East Bound: 4900 / West Bound: 9700']

CodePudding user response：

For Q1, you can try .str.split

df[['First Direction', 'Second direction']] = df['X'].str.split(' / ', expand=True)

print(df)

                                       X     First Direction    Second direction
0    East Bound: 6900 / West Bound: 7700   East Bound: 6900     West Bound: 7700
1    East Bound: 7800 / West Bound: 8700   East Bound: 7800     West Bound: 8700
2  North Bound: 5000 / South Bound: 4900  North Bound: 5000    South Bound: 4900
3  North Bound: 7000 / South Bound: 9000  North Bound: 7000    South Bound: 9000
4    East Bound: 4900 / West Bound: 9700   East Bound: 4900     West Bound: 9700

For Q2, you can try to convert X column to dictionary then explode the column into separate columns

out = df['X'].apply(lambda x: dict([direction.split(':') for direction in x.split(' / ')])).apply(pd.Series)

print(out)

  East Bound West Bound North Bound South Bound
0       6900       7700         NaN         NaN
1       7800       8700         NaN         NaN
2        NaN        NaN        5000        4900
3        NaN        NaN        7000        9000
4       4900       9700         NaN         NaN

CodePudding user response：

My approach would be to use Series.str.extractall with a specific pattern to get the direction and the amount, convert the amount to a suitable type (I've just gone for integer here), then pivot_table filling in with zeros where appropriate, eg:

out = (
    df['X'].str.extractall(r'(?P<bound>North|South|West|East) (?:Bound): (?P<n>\d )')
    .astype({'n': int})
    .pivot_table(index=pd.Grouper(level=0), columns='bound', values='n', fill_value=0)
)

This'll give you:

bound  East  North  South  West
0      6900      0      0  7700
1      7800      0      0  8700
2         0   5000   4900     0
3         0   7000   9000     0
4      4900      0      0  9700

This retains your original DF ID's... so you can merge/join back to your original DF at some point.