Explorer: DataFrame with StartDate and EndDate columns to timeline

Thanks for you help. You completly deconstructed it computed the inner list and than made a new DataFrame.

I think, we don’t have to do everything, like others do, but it has to make sense within its inner logic.

Thank you! That makes it much easier to answer.

So I think @josevalim’s answer was what you want (for now, see below):

df = DF.new([
  %{start_day: 244, end_day: 248, fw: 15.0},
  %{start_day: 247, end_day: 249, fw: 16.0}
])

df
|> DF.to_rows()
|> Enum.with_index()
|> Enum.flat_map(fn {row, index} ->
  row["start_day"]..row["end_day"]
  |> Enum.map(&Map.merge(row, %{"day" => &1, "index" => index}))
end)
|> DF.new()
|> DF.arrange(index) # You can order by any column
|> DF.print(limit: :infinity)
# +---------------------------------------------------------+
# |        Explorer DataFrame: [rows: 8, columns: 5]        |
# +-----------+-----------+---------+-----------+-----------+
# |    day    |  end_day  |   fw    |   index   | start_day |
# | <integer> | <integer> | <float> | <integer> | <integer> |
# +===========+===========+=========+===========+===========+
# | 244       | 248       | 15.0    | 0         | 244       |
# +-----------+-----------+---------+-----------+-----------+
# | 245       | 248       | 15.0    | 0         | 244       |
# +-----------+-----------+---------+-----------+-----------+
# | 246       | 248       | 15.0    | 0         | 244       |
# +-----------+-----------+---------+-----------+-----------+
# | 247       | 248       | 15.0    | 0         | 244       |
# +-----------+-----------+---------+-----------+-----------+
# | 248       | 248       | 15.0    | 0         | 244       |
# +-----------+-----------+---------+-----------+-----------+
# | 247       | 249       | 16.0    | 1         | 247       |
# +-----------+-----------+---------+-----------+-----------+
# | 248       | 249       | 16.0    | 1         | 247       |
# +-----------+-----------+---------+-----------+-----------+
# | 249       | 249       | 16.0    | 1         | 247       |
# +-----------+-----------+---------+-----------+-----------+

Also, I don’t want to speak for the core team, but explode does appear to be on the roadmap:

https://github.com/elixir-explorer/explorer/issues/296

So if/when that functionality is added, you can do a nearly one-to-one translation of the Pandas code.

@billylanchantin I just checked Polars also can do that. Polars Explode

But it’s not in the explorer documentation, olso the types list and array…
Am I right?

@billylanchantin I did it in Python Pandas and ilustrate it with some images.
I start with a DataFrame with start and Enddates and some values:

input486×354 24 KB

Than I add a column (last column), that holds the days from start to end date:

input_with_list476×273 13.2 KB

input_exploded927×331 7.43 KB

The image shows that line zero exists now 5 times, as the list in column day is exploded.

Here is the python code:

from pandas import read_csv

data_df = read_csv("../doy_data.csv")
data_df["day"] = data_df.apply(lambda x: list(range(x["start_day"], x["end_day"]+1)), axis="columns")
data_df = data_df.explode("day")
data_df.to_csv("doy_data_unrolled.csv", index=False)

How would I reach the result in Explorer?

I’m taking some liberties to illustrate the idea.

alias Explorer.Series, as: S
require Explorer.DataFrame, as: DF

data = [
  %{start_date: ~D[2023-01-01], end_date: ~D[2023-01-03], value: 1},
  %{start_date: ~D[2023-02-01], end_date: ~D[2023-02-03], value: 2}
]

df =
  data
  |> DF.new()
  |> DF.mutate(
    start_day: day_of_year(start_date),
    end_day: day_of_year(end_date)
  )

computed_values =
  df
  |> DF.to_rows()
  |> Enum.flat_map(fn row ->
    Enum.map(row["start_day"]..row["end_day"], fn day ->
      # Hold onto "start_day" so we group-then-sort by it later
      row |> Map.take(["start_day", "value"]) |> Map.put("day", day)
    end)
  end)
  # Do whatever computation you're trying to do here.
  |> Enum.map(fn %{"day" => day, "value" => value} = row ->
    Map.put(row, "value2", day + value)
  end)
  # Now we can group by "start_day", which makes n_rows match again.
  |> Enum.group_by(&(&1["start_day"]), &(&1["value2"]))
  |> Enum.sort()
  |> Enum.map(fn {_start_day, values} -> Enum.sum(values) end)

DF.put(df, :computed_values, computed_values)

A few points:

1. The above is not optimal. I focused in individual steps to clarify the idea.
2. I admit, I don’t quite know what unrolling the list accomplishes if you need to collapse it back down. Presumably, for most computations you could skip the unrolling and just operate on the dataframe directly.

If you show exactly what computation you’re trying to accomplish, I could say more. But the above is what I meant by “grouping/etc”.

@billylanchantin I musst be afk and try clear the problem from remote as good as I can.

What I meant is, the original DataFrame has about ten entries.
Than I take the dates, as start and End number and construct either a list of list, or what @josevalim proposed A flat_map which creates a flat list list of length 40 or so and than makes a Series out of it.
So the problems from my point of few are:
a) Putting a Series of length 40 into a DataFrame of Shape 10, n_cols is not possible?!
b) I don’t know to transform the DataFrame to a length shape of 40, n_cols and preserve the same row order as in the Series.
You talked about grouping. How would it help in this topic?

Hey @sehHeiden,

I wasn’t able to follow why @josevalim’s suggestion doesn’t work. You say:

I think you’ll need to do the grouping/etc. while it’s still a list. Then I think the lengths should match.

Also, if you’re trying to do interpolation with dates, this answer might help.

The closest I have seen is the pivot_longer function. But it’s works on existing columns that are combined into one column. But instead of columns I have a list of data (of variable length).
@josevalim I hope that makes clear, what I want. Do you know how to address it?

Okay, so something like pandas iterrows or apply. I assume that is not possible in Elixir to Enum.map over a Data Frame.

Yes, I’m not at all sure about this. The map_rows/2 function is not available in Elixir’s Explorer library. It seems to be a function of the df library in R.

However, you can get a similar effect by using Enum.map/2 to iterate over each row of the DataFrame, unless I’m mistaken?