CODEWITHSUNDEEP
regexapache-sparkpyspark
Nikhil Baby
Nikhil Baby

Reputation: 883

Filter all patterns matching regex as a separate row in RDD in PySpark

I have a .csv file that has records written as a single line instead of separate rows. I was able to match the pattern of each row using the regex (?:"([a-zA-Z0-9 /\-\:\.\,]+)",|\\N,|"",|""){26}.

I have the following code that reads the .csv file and specifies the pattern using re.

textFile = sc.textFile("/home/Stores.csv")
import re
pattern = re.compile('(?:"([a-zA-Z0-9 /\-\:\.\,]+)",|\\N,|"",|""){26}')

Is it possible to do something like the code below to split the .csv file into separate rows?.

textFile.flatMap(lambda x: pattern.split(x)).collect()

The above code does not work. Please help me to split the single row into multiple rows with the pattern using PySpark.

Upvotes: 1

Views: 2578

Answers (1)

user3689574
user3689574

Reputation: 1676

Can you show an example of "pattern" working on a piece of your file in python?

When I run something similar it seems to work.

import re
pattern = re.compile('\s+')
text = "abxd 4567 tyreyr fgdf"

print pattern.split(text)

result = sc.parallelize([text])
print result.flatMap(lambda x: pattern.split(x)).collect()

Prints

['abxd', '4567', 'tyreyr', 'fgdf']
['abxd', '4567', 'tyreyr', 'fgdf']

Edit: Ok, how about this:

import re
pattern = re.compile('(?:"([a-zA-Z0-9 /\-\:\.\,]+)",|\\N,|"",|"")')
text = '"40","353","xyz","xyz","zyx353","1","26","1","dd/mm","5","0",\N,\N,"0","0","dd/mm","one",\N,\N,"0","2015-08-06 13:12:30.557000",\N,"two","",\N,"""66","1090","abc","abc1","abc1090","1","6","1","dd/mm","5","1",\N,\N,\N,"1","dd/mm","one",\N,\N,"0","2015-09-04 17:28:00.323000",\N,"two",\N,\N,"""80","1326","kmy","kmy","kmiii","1","26","1","dd/mm","5","0",\N,\N,"0","0","dd/mm","Active",\N,\N,"0","2015-09-30 11:49:47.857000",\N,"two",\N,\N,"""81","1332","haii","haii","haiii","1","26","1","dd/mm","5","1",\N,\N,"0","0","dd/mm","one",\N,\N,"0","2015-10-01 15:59:11.843000",\N,"two","",\N,""'
result_list =  pattern.findall(text)
print [result_list[x:x+26] for x in xrange(0, len(result_list), 26)]

result = sc.parallelize([text])

def split_my_file(row, pattern):
    result_list =  pattern.findall(row)
    return [result_list[x:x+26] for x in xrange(0, len(result_list), 26)]

print result.flatMap(lambda x: split_my_file(x, pattern)).collect()

result:

[['40', '353', 'xyz', 'xyz', 'zyx353', '1', '26', '1', 'dd/mm', '5', '0', '', '', '0', '0', 'dd/mm', 'one', '', '', '0', '2015-08-06 13:12:30.557000', '', 'two', '', '', ''], ['66', '1090', 'abc', 'abc1', 'abc1090', '1', '6', '1', 'dd/mm', '5', '1', '', '', '', '1', 'dd/mm', 'one', '', '', '0', '2015-09-04 17:28:00.323000', '', 'two', '', '', ''], ['80', '1326', 'kmy', 'kmy', 'kmiii', '1', '26', '1', 'dd/mm', '5', '0', '', '', '0', '0', 'dd/mm', 'Active', '', '', '0', '2015-09-30 11:49:47.857000', '', 'two', '', '', ''], ['81', '1332', 'haii', 'haii', 'haiii', '1', '26', '1', 'dd/mm', '5', '1', '', '', '0', '0', 'dd/mm', 'one', '', '', '0', '2015-10-01 15:59:11.843000', '', 'two', '', '', '']]
[['40', '353', 'xyz', 'xyz', 'zyx353', '1', '26', '1', 'dd/mm', '5', '0', '', '', '0', '0', 'dd/mm', 'one', '', '', '0', '2015-08-06 13:12:30.557000', '', 'two', '', '', ''], ['66', '1090', 'abc', 'abc1', 'abc1090', '1', '6', '1', 'dd/mm', '5', '1', '', '', '', '1', 'dd/mm', 'one', '', '', '0', '2015-09-04 17:28:00.323000', '', 'two', '', '', ''], ['80', '1326', 'kmy', 'kmy', 'kmiii', '1', '26', '1', 'dd/mm', '5', '0', '', '', '0', '0', 'dd/mm', 'Active', '', '', '0', '2015-09-30 11:49:47.857000', '', 'two', '', '', ''], ['81', '1332', 'haii', 'haii', 'haiii', '1', '26', '1', 'dd/mm', '5', '1', '', '', '0', '0', 'dd/mm', 'one', '', '', '0', '2015-10-01 15:59:11.843000', '', 'two', '', '', '']]

Upvotes: 1

Related Questions