Início / coding / Perguntas / 79125764
Accepted
Andi
Asked: 2024-10-25 20:51:39 +0800 CST

Encontre a interseção de colunas de diferentes dataframes polares

  • 772

Tenho um número variável de pl.DataFramesque compartilham algumas colunas (por exemplo, symbole date). Cada um pl.DataFrametem um número de colunas adicionais, que não são importantes para a tarefa real.

As symbolcolunas têm exatamente o mesmo conteúdo (os strvalores diferentes existem em cada dataframe). As datecolunas são um pouco diferentes na forma como não têm exatamente as mesmas datas em cada pl.DataFrame.

A tarefa real é encontrar as datas comuns por agrupamento (ou seja, symbol) e filtrar cada uma pl.DataFrameadequadamente.

Aqui estão três exemplos pl.DataFrame:

import polars as pl

df1 = pl.DataFrame(
    {
        "symbol": ["AAPL"] * 4 + ["GOOGL"] * 3,
        "date": [
            "2023-01-01", "2023-01-02", "2023-01-03", "2023-01-04",
            "2023-01-02", "2023-01-03", "2023-01-04",
        ],
        "some_other_col": range(7),
    }
)

df2 = pl.DataFrame(
    {
        "symbol": ["AAPL"] * 3 + ["GOOGL"] * 5,
        "date": [
            "2023-01-02", "2023-01-03", "2023-01-04",
            "2023-01-01", "2023-01-02", "2023-01-03", "2023-01-04", "2023-01-05",
        ],
        "another_col": range(8),
    }
)

df3 = pl.DataFrame(
    {
        "symbol": ["AAPL"] * 4 + ["GOOGL"] * 2,
        "date": [
            "2023-01-02", "2023-01-03", "2023-01-04", "2023-01-05",
            "2023-01-03", "2023-01-04",
        ],
        "some_col": range(6),
    }
)
DataFrame 1:
shape: (7, 3)
┌────────┬────────────┬────────────────┐
│ symbol ┆ date       ┆ some_other_col │
│ ---    ┆ ---        ┆ ---            │
│ str    ┆ str        ┆ i64            │
╞════════╪════════════╪════════════════╡
│ AAPL   ┆ 2023-01-01 ┆ 0              │
│ AAPL   ┆ 2023-01-02 ┆ 1              │
│ AAPL   ┆ 2023-01-03 ┆ 2              │
│ AAPL   ┆ 2023-01-04 ┆ 3              │
│ GOOGL  ┆ 2023-01-02 ┆ 4              │
│ GOOGL  ┆ 2023-01-03 ┆ 5              │
│ GOOGL  ┆ 2023-01-04 ┆ 6              │
└────────┴────────────┴────────────────┘

DataFrame 2:
shape: (8, 3)
┌────────┬────────────┬─────────────┐
│ symbol ┆ date       ┆ another_col │
│ ---    ┆ ---        ┆ ---         │
│ str    ┆ str        ┆ i64         │
╞════════╪════════════╪═════════════╡
│ AAPL   ┆ 2023-01-02 ┆ 0           │
│ AAPL   ┆ 2023-01-03 ┆ 1           │
│ AAPL   ┆ 2023-01-04 ┆ 2           │
│ GOOGL  ┆ 2023-01-01 ┆ 3           │
│ GOOGL  ┆ 2023-01-02 ┆ 4           │
│ GOOGL  ┆ 2023-01-03 ┆ 5           │
│ GOOGL  ┆ 2023-01-04 ┆ 6           │
│ GOOGL  ┆ 2023-01-05 ┆ 7           │
└────────┴────────────┴─────────────┘

DataFrame 3:
shape: (6, 3)
┌────────┬────────────┬──────────┐
│ symbol ┆ date       ┆ some_col │
│ ---    ┆ ---        ┆ ---      │
│ str    ┆ str        ┆ i64      │
╞════════╪════════════╪══════════╡
│ AAPL   ┆ 2023-01-02 ┆ 0        │
│ AAPL   ┆ 2023-01-03 ┆ 1        │
│ AAPL   ┆ 2023-01-04 ┆ 2        │
│ AAPL   ┆ 2023-01-05 ┆ 3        │
│ GOOGL  ┆ 2023-01-03 ┆ 4        │
│ GOOGL  ┆ 2023-01-04 ┆ 5        │
└────────┴────────────┴──────────┘

Agora, o primeiro passo seria encontrar as datas comuns para cada symbol.
AAPL: ["2023-01-02", "2023-01-03", "2023-01-04"]
GOOGL:["2023-01-03", "2023-01-04"]

Isso significa que cada um pl.DataFrameprecisa ser filtrado adequadamente. O resultado esperado se parece com isso:

DataFrame 1 filtered:

shape: (5, 3)
┌────────┬────────────┬────────────────┐
│ symbol ┆ date       ┆ some_other_col │
│ ---    ┆ ---        ┆ ---            │
│ str    ┆ str        ┆ i64            │
╞════════╪════════════╪════════════════╡
│ AAPL   ┆ 2023-01-02 ┆ 1              │
│ AAPL   ┆ 2023-01-03 ┆ 2              │
│ AAPL   ┆ 2023-01-04 ┆ 3              │
│ GOOGL  ┆ 2023-01-03 ┆ 5              │
│ GOOGL  ┆ 2023-01-04 ┆ 6              │
└────────┴────────────┴────────────────┘

DataFrame 2 filtered:
shape: (5, 3)
┌────────┬────────────┬─────────────┐
│ symbol ┆ date       ┆ another_col │
│ ---    ┆ ---        ┆ ---         │
│ str    ┆ str        ┆ i64         │
╞════════╪════════════╪═════════════╡
│ AAPL   ┆ 2023-01-02 ┆ 0           │
│ AAPL   ┆ 2023-01-03 ┆ 1           │
│ AAPL   ┆ 2023-01-04 ┆ 2           │
│ GOOGL  ┆ 2023-01-03 ┆ 5           │
│ GOOGL  ┆ 2023-01-04 ┆ 6           │
└────────┴────────────┴─────────────┘

DataFrame 3 filtered:
shape: (5, 3)
┌────────┬────────────┬──────────┐
│ symbol ┆ date       ┆ some_col │
│ ---    ┆ ---        ┆ ---      │
│ str    ┆ str        ┆ i64      │
╞════════╪════════════╪══════════╡
│ AAPL   ┆ 2023-01-02 ┆ 0        │
│ AAPL   ┆ 2023-01-03 ┆ 1        │
│ AAPL   ┆ 2023-01-04 ┆ 2        │
│ GOOGL  ┆ 2023-01-03 ┆ 4        │
│ GOOGL  ┆ 2023-01-04 ┆ 5        │
└────────┴────────────┴──────────┘
python

2 respostas

  1. Você pode encontrar a intersecção com junções:

    on = ["symbol", "date"]

intersection = (
   df1
    .select(on)
    .join(df2.select(on), on=on)
    .join(df3.select(on), on=on)
)

    shape: (5, 2)
┌────────┬────────────┐
│ symbol ┆ date       │
│ ---    ┆ ---        │
│ str    ┆ str        │
╞════════╪════════════╡
│ AAPL   ┆ 2023-01-02 │
│ AAPL   ┆ 2023-01-03 │
│ AAPL   ┆ 2023-01-04 │
│ GOOGL  ┆ 2023-01-03 │
│ GOOGL  ┆ 2023-01-04 │
└────────┴────────────┘

    E use joinnovamente para "filtrar":

    intersection.join(df1, on=on)
intersection.join(df2, on=on)
intersection.join(df3, on=on)

    shape: (5, 3)
┌────────┬────────────┬────────────────┐
│ symbol ┆ date       ┆ some_other_col │
│ ---    ┆ ---        ┆ ---            │
│ str    ┆ str        ┆ i64            │
╞════════╪════════════╪════════════════╡
│ AAPL   ┆ 2023-01-02 ┆ 1              │
│ AAPL   ┆ 2023-01-03 ┆ 2              │
│ AAPL   ┆ 2023-01-04 ┆ 3              │
│ GOOGL  ┆ 2023-01-03 ┆ 5              │
│ GOOGL  ┆ 2023-01-04 ┆ 6              │
└────────┴────────────┴────────────────┘

    shape: (5, 3)
┌────────┬────────────┬─────────────┐
│ symbol ┆ date       ┆ another_col │
│ ---    ┆ ---        ┆ ---         │
│ str    ┆ str        ┆ i64         │
╞════════╪════════════╪═════════════╡
│ AAPL   ┆ 2023-01-02 ┆ 0           │
│ AAPL   ┆ 2023-01-03 ┆ 1           │
│ AAPL   ┆ 2023-01-04 ┆ 2           │
│ GOOGL  ┆ 2023-01-03 ┆ 5           │
│ GOOGL  ┆ 2023-01-04 ┆ 6           │
└────────┴────────────┴─────────────┘

    shape: (5, 3)
┌────────┬────────────┬──────────┐
│ symbol ┆ date       ┆ some_col │
│ ---    ┆ ---        ┆ ---      │
│ str    ┆ str        ┆ i64      │
╞════════╪════════════╪══════════╡
│ AAPL   ┆ 2023-01-02 ┆ 0        │
│ AAPL   ┆ 2023-01-03 ┆ 1        │
│ AAPL   ┆ 2023-01-04 ┆ 2        │
│ GOOGL  ┆ 2023-01-03 ┆ 4        │
│ GOOGL  ┆ 2023-01-04 ┆ 5        │
└────────┴────────────┴──────────┘
    • 2
  2. Best Answer

    Você pode usar pl.DataFrame.join()com how="semio parâmetro ":

    semi Retorna linhas da tabela da esquerda que têm uma correspondência na tabela da direita.

    on = ["symbol","date"]
df1.join(df2, on=on, how="semi").join(df3, on=on, how="semi")
df2.join(df1, on=on, how="semi").join(df3, on=on, how="semi")
df3.join(df1, on=on, how="semi").join(df2, on=on, how="semi")

    shape: (5, 3)
┌────────┬────────────┬────────────────┐
│ symbol ┆ date       ┆ some_other_col │
│ ---    ┆ ---        ┆ ---            │
│ str    ┆ str        ┆ i64            │
╞════════╪════════════╪════════════════╡
│ AAPL   ┆ 2023-01-02 ┆ 1              │
│ AAPL   ┆ 2023-01-03 ┆ 2              │
│ AAPL   ┆ 2023-01-04 ┆ 3              │
│ GOOGL  ┆ 2023-01-03 ┆ 5              │
│ GOOGL  ┆ 2023-01-04 ┆ 6              │
└────────┴────────────┴────────────────┘
shape: (5, 3)
┌────────┬────────────┬─────────────┐
│ symbol ┆ date       ┆ another_col │
│ ---    ┆ ---        ┆ ---         │
│ str    ┆ str        ┆ i64         │
╞════════╪════════════╪═════════════╡
│ AAPL   ┆ 2023-01-02 ┆ 0           │
│ AAPL   ┆ 2023-01-03 ┆ 1           │
│ AAPL   ┆ 2023-01-04 ┆ 2           │
│ GOOGL  ┆ 2023-01-03 ┆ 5           │
│ GOOGL  ┆ 2023-01-04 ┆ 6           │
└────────┴────────────┴─────────────┘
shape: (5, 3)
┌────────┬────────────┬──────────┐
│ symbol ┆ date       ┆ some_col │
│ ---    ┆ ---        ┆ ---      │
│ str    ┆ str        ┆ i64      │
╞════════╪════════════╪══════════╡
│ AAPL   ┆ 2023-01-02 ┆ 0        │
│ AAPL   ┆ 2023-01-03 ┆ 1        │
│ AAPL   ┆ 2023-01-04 ┆ 2        │
│ GOOGL  ┆ 2023-01-03 ┆ 4        │
│ GOOGL  ┆ 2023-01-04 ┆ 5        │
└────────┴────────────┴──────────┘

    Ou você provavelmente poderia generalizar um pouco:

    on = ["symbol","date"]
dfs = [df1, df2, df3]

# filter first dataframe on all others
for df in dfs[1:]:
    dfs[0] = dfs[0].join(df, on=on, how="semi")

# then filter all others on first one
for i, df in enumerate(dfs[1:]):
    dfs[i] = df.join(dfs[0], on=on, how="semi")

for df in dfs:
    print(df)
    • 1

relate perguntas