Explorer: DataFrame with StartDate and EndDate columns to timeline

What happens if you call S.from_list() before? Something like:

dates = polls_timeline["start_day"]
 |> S.to_list()
 |> Enum.zip(S.to_list(polls_timeline["end_day"]))
 |> Enum.flat_map(fn {start_date, end_date} -> start_date..end_date end)
 |> S.from_list()

a flatmap is really able to work with a series. But this still cannot be put, as the lengths do not match.

At the start I have a dataframe like:

| start_date | end_date | value1 | value2 |
| <datetime[μs]> |<datetime[μs]> | | |
+===========+==============+=====================+
| 2023-08-29 16:30:13.000000 | 2023-08-30 16:30:13.000000| 0.2758| 0.30290 |

and want that every row unrolls to something like:

| day | value1 | value2 |
| | | |
+===========+==============+=====================+
| 241 | 0.2758| 0.30290 |
±----------±---------------------------±--------------------+
| 242 | 0.2758| 0.30290 |

After the flatmap have literaly gotten the day column correct, but the value columns still have the original length. Therefore I can not put the new col.

P.S.: The next steps would be, group(day)->mean(values)-> distinct(). I know how to that. The final step is, what I do not know. How would I fill all the missing day between the first and the last date, that are missing and fill the value columns with nils, so that I cann fill the value will last values. Although I would like to fill with linear interpolation. Still don’t know how to do that.
In the end I would have a timeline with all values filled and a row for each day.^^

Hi,
I’m not really sure I understand the context, in case it helps?
polls_timeline =
polls_timeline
|> DF.map_rows(fn row →
start_day = elem(row, 2) # Assume than start_day at position 2
end_day = elem(row, 3) # Assume end_day at position 3
date_range = Enum.to_list(start_day..end_day)
Tuple.append(row, date_range)
end)

new_rows =
polls_timeline
|> DF.to_list()
|> Enum.flat_map(fn row →
date_range = List.last(row)
Enum.map(date_range, fn date →
List.replace_at(row, -1, date)
end)
end)

new_df = DF.new(new_rows, names: DF.names(polls_timeline))

@nseaSeb map_rows was neither included in the Documentation of Explorer, nor could I use it in the git Version I currently use. Could you tell, me a bit more about it?

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?

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

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?

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.

@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?

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”.