Conditional String Split based on another column str Python

Question

Hi guys is there a clean way to organize this data into its correct columns to geolocate it later?

 import pandas as pd

 coordinates = {'event': ['1', '2', '3', '4'],
     'direction': ['E', 'E,N', 'N,E', 'N'],
     'location': ['316904', '314798,5812040', '5811316,314766', '5811309']}
 df = pd.DataFrame.from_dict(coordinates)
 df

What it need it to look like:

 Event  direction    location    easting    northing
  1       E          316904       316904       NA
  2       E,N     314798,5812040   314798    5812040
  3       N,E     5811316,314766   314766    5811316
  4       N          5811309         NA      5811309

I can split the location with: df['easting'], df['northing'] = df['location'].str.split(',',1).str

but I need the condition when E THEN 1st value is easting, second northing OR condition when N than 1st value is easting and so on..

any ideas would be gladly appreciated!

jezrael · Accepted Answer · 2018-10-04 06:40:27Z

3

Solution 1:

First split columns to new ones and then swap values by boolean mask created by startswith:

df[['easting','northing']] = df['location'].str.split(',',1, expand=True)
mask = df['direction'].str.startswith('N')
df.loc[mask, ['easting','northing']] = df.loc[mask, ['northing','easting']].values

print (df)
  event direction        location easting northing
0     1         E          316904  316904     None
1     2       E,N  314798,5812040  314798  5812040
2     3       N,E  5811316,314766  314766  5811316
3     4         N         5811309    None  5811309

Solution 2:

First flatten values to helper DataFrame, then use pivot and last join to original by join:

from itertools import chain

direc = df['direction'].str.split(',')
loc = df['location'].str.split(',')
lens = loc.str.len()
df1 = pd.DataFrame({
    'direction' : list(chain.from_iterable(direc.tolist())), 
    'loc' : list(chain.from_iterable(loc.tolist())), 
    'event' : df['event'].repeat(lens)
})

df2 = df1.pivot('event','direction','loc').rename(columns={'E':'easting','N':'northing'})
print (df2)
direction easting northing
event                     
1          316904      NaN
2          314798  5812040
3          314766  5811316
4             NaN  5811309

df = df.join(df2, on='event')
print (df)
  event direction        location easting northing
0     1         E          316904  316904      NaN
1     2       E,N  314798,5812040  314798  5812040
2     3       N,E  5811316,314766  314766  5811316
3     4         N         5811309     NaN  5811309

edited Oct 4, 2018 at 6:40

answered Oct 4, 2018 at 6:34

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Seb Over a year ago

The first solution did wonders! Jezrael I owe a good coffee buddy tks!

Collectives™ on Stack Overflow

Conditional String Split based on another column str Python

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related