how to replace the nth character in a string with another [ELIXIR/ ERLANG]

Question

How do you perform a String replace_at in Elixir or Erlang?

For example given this fixed width file:

EmployeeFundMappingID EmployeeID  FundID      IsActive EntryDate               ExitDate                ExitTypeID  DateCreated             CreatedByID DateModified            ModifiedByID ConfirmedBy DateConfirmed           GUID                                     IsPooled DatePooled
1                     1118544     1           1        2009-04-20 00:00:00.000 NULL                    NULL        2014-05-17 08:46:48.020 1           2014-10-30 13:34:47.177 NULL         1           2009-04-20 17:48:12.067 NULL                                     NULL     NULL
2                     1027350     1           1        2008-03-03 00:00:00.000 NULL                    NULL        2014-05-17 08:46:48.020 1           2014-10-30 13:34:47.177 NULL         1           2008-05-04 15:13:30.303 NULL                                     NULL     NULL
3                     1024795     1           1        2008-02-29 00:00:00.000 NULL                    NULL        2014-05-17 08:46:48.020 1           2014-10-30 13:34:47.177 NULL         1           2008-05-04 15:13:30.303 NULL                                     NULL     NULL
4                     1116497     1           1        2009-03-24 00:00:00.000 NULL                    NULL        2014-05-17 08:46:48.020 1           2014-10-30 13:34:47.177 NULL         1           2009-03-24 13:00:15.277 NULL                                     NULL     NULL
5                     1116569     1           1        2009-03-24 00:00:00.000 NULL                    NULL        2014-05-17 08:46:48.020 1           2014-10-30 13:34:47.177 NULL         1           2009-03-24 14:43:08.280 NULL                                     NULL     NULL
6                     1116920     1           1        2009-03-27 00:00:00.000 NULL                    NULL        2014-05-17 08:46:48.020 1           2014-10-30 13:34:47.177 NULL         1           2009-03-27 17:16:35.073 NULL                                     NULL     NULL

with col positions at:

[0, 22, 34, 46, 55, 79, 103, 115, 139, 151, 175, 188, 200, 224, 265, 274]

How do we replace \s with \t at each col positions?

I'm effectively trying to convert a Fixed-Width file into a csv

Have you tried String.replace/4? It can use regex to replace the spaces with commas. — Abhyudit Jain
– Abhyudit Jain, Commented Jun 11, 2018 at 10:13
@AbhyuditJain, the spaces are at specific locations. How would that work? — Charles Okwuagwu
– Charles Okwuagwu, Commented Jun 11, 2018 at 10:15
@AbhyuditJain Thanks. this works, but it splits on date-time — Charles Okwuagwu
– Charles Okwuagwu, Commented Jun 11, 2018 at 10:20
I didn't notice the space there. It's going to be a tricky regex to figure out. — Abhyudit Jain
– Abhyudit Jain, Commented Jun 11, 2018 at 10:24

Charles Okwuagwu · Accepted Answer · 2018-06-11 18:52:18Z

3

I would go with reducing the original lines with a set of functions changing respective positions in the string.

funs =
  [22, 34, 46, 55, 79, 103, 115, 139, 151, 175, 188, 200, 224, 265, 274]
  |> Enum.map(& &1 - 1)
  |> Enum.map(fn len ->
       fn <<s :: binary-size(len), " ", rest :: binary>> ->
         s <> "\t" <> rest
       end
     end)

input
|> String.trim
|> String.split("\n")
|> Enum.map(fn line ->
     Enum.reduce(funs, line, fn fun, acc -> fun.(acc) end)
   end)

That might be done in a more elegant way using generated macros, once per a position, and recursive calls, but reducing on function list looks more straightforward to me here.

The advantage of this approach would be that it immediately fails on any inconsistent data, assuring (more or less) that if it passed, the conversion was done properly, unlike all others shorter solutions.

Also it’s drastically faster than any Regex solution.

Since this is to be applied to 16M rows, here is the probably most performant version, that matches the whole row at once:

input
|> String.trim
|> String.split("\n")
|> Enum.map(
     # [22, 34, 46, 55, 79, 103,
     #  115, 139, 151, 175, 188,
     #  200, 224, 265, 274]
     # note: this assumes the listed positions above are 1-based
     fn <<
        c1 :: binary-size(21),
        " ",
        c2 :: binary-size(11),
        " ",
        c3 :: binary-size(11),
        " ",
        c4 :: binary-size(8),
        " ",
        c5 :: binary-size(23),
        " ",
        c6 :: binary-size(23),
        " ",
        c7 :: binary-size(11),
        " ",
        c8 :: binary-size(23),
        " ",
        c9 :: binary-size(11),
        " ",
        c10 :: binary-size(23),
        " ",
        c11 :: binary-size(12),
        " ",
        c12 :: binary-size(11),
        " ",
        c13 :: binary-size(23),
        " ",
        c14 :: binary-size(40),
        " ",
        c15 :: binary-size(8),
        " ",
        c16 :: binary
        >> ->
     c1 <> "\t" <> 
       c2 <> "\t" <> 
       c3 <> "\t" <> 
       c4 <> "\t" <> 
       c5 <> "\t" <> 
       c6 <> "\t" <> 
       c7 <> "\t" <> 
       c8 <> "\t" <> 
       c9 <> "\t" <> 
       c10 <> "\t" <> 
       c11 <> "\t" <> 
       c12 <> "\t" <> 
       c13 <> "\t" <> 
       c14 <> "\t" <> 
       c15 <> "\t" <> 
       c16
   end)

edited Jun 11, 2018 at 18:52

Charles Okwuagwu

11k21 gold badges96 silver badges169 bronze badges

answered Jun 11, 2018 at 12:06

Aleksei Matiushkin

121k12 gold badges109 silver badges173 bronze badges

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

7 Comments

Abhyudit Jain Over a year ago

The issue with this is it's not converting it into csv

Aleksei Matiushkin Over a year ago

@AbhyuditJain excuse me? CSV perfectly accepts "\t" as column separators. If you want comma-separation, change "\t" in s <> "\t" <> rest to whatever you want.

Charles Okwuagwu Over a year ago

@mudasobwa hi. Please i'm applying this to a file with over 16M rows ... will this perform well?

Aleksei Matiushkin Over a year ago

I have updated the answer with the probably most performant version; do a benchmark if you need to know what is better exactly, but I am pretty sure 16M lines should be processed fine with both versions.

Charles Okwuagwu Over a year ago

@mudasobwa my initial thinking was that i would need to use File.stream at some point to read in the file line by line, so the size would bot be an issue... then apply the map transform, then write back to a file, line by line. I guess input could be a File.stream ... correct?

|

Abhyudit Jain · Accepted Answer · 2018-06-11 11:03:41Z

1

What you can do is join the date-time first and then replace all the spaces with commas and then revert the date-time to original format:

data
|> String.replace(~r/(-\d+)([\s]{1})(\d+)/, "\\1T\\3")
|> String.replace(~r/ +/, ",")
|> String.replace(~r/(\d)(T)(\d)/, "\\1 \\3")

answered Jun 11, 2018 at 11:03

Abhyudit Jain

3,7582 gold badges26 silver badges33 bronze badges

Comments

Charles Okwuagwu · Accepted Answer · 2018-06-12 10:53:27Z

0

Comparing two implementations on a dataset with over 16M rows:

  def flat2csv1(src, dst) do
    Logger.info("START")

    t = System.system_time(:millisecond)

    funs =
      [12, 52, 76]
      |> Enum.map(&(&1 - 1))
      |> Enum.map(fn len ->
        fn <<s::binary-size(len), " ", rest::binary>> ->
          s <> "\t" <> rest
        end
      end)

    File.stream!(src)
    |> Enum.map(fn line ->
      Enum.reduce(funs, line, fn fun, acc -> fun.(acc) end)
    end)
    |> write(dst)

    log_elapsed("DONE", t)
  end

  def flat2csv0(src, dst) do
    Logger.info("START")

    t = System.system_time(:millisecond)

    File.stream!(src)
    |> Enum.map(fn <<
                     c1::binary-size(11),
                     " ",
                     c2::binary-size(39),
                     " ",
                     c3::binary-size(23),
                     " ",
                     ce::binary
                   >> ->
      c1 <> "\t" <> c2 <> "\t" <> c3 <> "\t" <> ce
    end)
    |> write(dst)

    log_elapsed("DONE", t)
  end

  defp log_elapsed(s, t) do
    t = System.system_time(:millisecond) - t
    Logger.debug("#{s}: #{t} ms")
  end

  defp write(s, dst) do
    File.write!(dst, s, [:append])
  end

Results

# flat2csv0
11:40:25.055 [info] START
11:42:26.028 [info] DONE: 120969 ms

# flat2csv1
11:45:17.521 [info] START
11:48:25.433 [info] DONE: 187906 ms

answered Jun 12, 2018 at 10:53

Charles Okwuagwu

11k21 gold badges96 silver badges169 bronze badges

1 Comment

Aleksei Matiushkin Over a year ago

Wow. I always respected Erlang VM binary pattern match optimizations but I could not imagine they are as incredibly great.—Thank you!

Collectives™ on Stack Overflow

how to replace the nth character in a string with another [ELIXIR/ ERLANG]

3 Answers 3

7 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related