Merge list of dataframes with common keys

[Mateuscvieira]

Merge list of dataframes with common keys

[cmdlineluser]

Did you try align instead of diagonal?

df1 = pl.DataFrame({"date": [1, 2, 3], "usdeur": ["a", "b", "c"]})
df2 = pl.DataFrame({"date": [1, 2, 3], "abcdef": ["d", "e", "f"]})
df3 = pl.DataFrame({"date": [4, 5], "foobar": ["g", "h"]})

pl.concat([df1, df2, df3], how="align")

# shape: (5, 4)
# ┌──────┬────────┬────────┬────────┐
# │ date ┆ usdeur ┆ abcdef ┆ foobar │
# │ ---  ┆ ---    ┆ ---    ┆ ---    │
# │ i64  ┆ str    ┆ str    ┆ str    │
# ╞══════╪════════╪════════╪════════╡
# │ 1    ┆ a      ┆ d      ┆ null   │
# │ 2    ┆ b      ┆ e      ┆ null   │
# │ 3    ┆ c      ┆ f      ┆ null   │
# │ 4    ┆ null   ┆ null   ┆ g      │
# │ 5    ┆ null   ┆ null   ┆ h      │
# └──────┴────────┴────────┴────────┘

[Mateuscvieira]

Yeah, that's exactly what I needed lol
Sorry for the spam.

[mcrumiller]

Sorry, can you be clearer with an example? How does it have a known structure if the size is unknown? And if every column is different, how do you plan polars to figure out which columns you want to be considered "the same column"?

[Mateuscvieira]

Sorry if I wasn't clear. Here's an example:
Say my organization stores time series data in a warehouse and every series has a date column with name "date", but the value column is named differently according to the variable it represents. So a time series of the dollar-euro exchange rate would have columns "date" and "USDEUR". I might be tasked with writing a utility function for our in-house library that calculates statistics for an arbitrary list of time series.
When writing such a function, I am faced with the situation I described: I know that every dataframe will have a column named "date", yet I can't join them together in a single, wide DataFrame cleanly. The most efficient way I can think of is to pivot every Dataframe to a long format, have the value column named "value" with it's original name moved to a "variable" column, then vertically concat. But this seems wasteful, as in the end I'll probably want to pivot to the wider format again to make cross-sectional calculations.

I know this example could easily be solved by having the data stored a different way, I'm just trying to come up with a simple use case.