How to strip and split in pandas

user_12249472 0
Viewed 2822

Is there a way to perform a split by new line and also do a strip of whitespaces in a single line ? this is how my df looks like originally

 df["Source"]
0       test1   \n test2   
1       test1   \n test2   
2       test1   \ntest2    
Name: Source, dtype: object

I used to do a split based on new line and create a list with the below code

Data = (df["Source"].str.split("\n").to_list())

Data
    [['test1   ', ' test2   '], ['   test1   ', ' test2   '], ['  test1   ', 'test2    ']]

I want to further improve this and remove any leading or trailing white spaces and i am not sure how to use the split and strip in a single line

df['Port']
0    443\n8080\n161
1                25
2               169
3                25
4          2014\n58
Name: Port, dtype: object

when i try to split it based on the new line , it fills in nan values for the ones that does not have \n

df['Port'].str.split("\n").to_list()
[['443', '8080', '161'], nan, nan, nan, ['2014', '58']]

the same works perfectly for other columns

df['Source Hostname']
0    test1\ntest2\ntest3
1                  test5
2         test7\ntest8\n
3                  test1
4           test2\ntest4
Name: Source Hostname, dtype: object
df["Source Hostname"].str.split('\n').apply(lambda z: [e.strip() for e in z]).tolist()
[['test1', 'test2', 'test3'], ['test5'], ['test7', 'test8', ''], ['test1'], ['test2', 'test4']]
pandas python
2 Answers
df['Source'].str.split('\n').apply(lambda x: [e.strip() for e in x]).tolist()
user_6678830

Use Series.str.strip for remove traling whitespaces and then split by regex \s*\n\s* for one or zero whitespaces before and after \n:

df = pd.DataFrame({'Source':['test1   \n test2   ',
                             '    test1   \n test2   ',
                             '    test1   \ntest2   ']})
print (df)
                    Source
0      test1   \n test2   
1      test1   \n test2   
2       test1   \ntest2

Data = (df["Source"].str.strip().str.split("\s*\n\s*").to_list())
print (Data)
[['test1', 'test2'], ['test1', 'test2'], ['test1', 'test2']]

Or if possible split by arbitrary whitespace (it means spaces or \n here):

Data = (df["Source"].str.strip().str.split().to_list())
print (Data)
[['test1', 'test2'], ['test1', 'test2'], ['test1', 'test2']]
user_29010020
Related