Suppose I have aggregated the mean and the median of some value over 3 months, like:

df = (data.group_by('month_code').agg(pl.col('value').mean().alias('avg'),
                                      pd.col('value').median().alias('med')
                                      )
          .sort('month_code')
          .collect()
      )

Resulting in something like:

df = pd.DataFrame({'month': ['M202412','M202501','M202502'],
                   'avg': [0.037824, 0.03616, 0.038919],
                   'med': [0.01381, 0.013028, 0.014843]
                   })

And I'd like to visualize it, so should convert to the format:

df_ = pd.DataFrame({'month': ['M202412','M202501','M202502']*2,
                   'type': ['avg','avg','avg','med','med','med'],
                   'value': [0.037824, 0.03616, 0.038919, 0.01381, 0.013028, 0.014843],
                   })

Which is then easy to visualize:

df_.plot.line(x='month',y='value',color='type').properties(width=400, height=350, title='avg and med')

What is the simplest way to convert df to df_ above?

Suppose I have aggregated the mean and the median of some value over 3 months, like:

df = (data.group_by('month_code').agg(pl.col('value').mean().alias('avg'),
                                      pd.col('value').median().alias('med')
                                      )
          .sort('month_code')
          .collect()
      )

Resulting in something like:

df = pd.DataFrame({'month': ['M202412','M202501','M202502'],
                   'avg': [0.037824, 0.03616, 0.038919],
                   'med': [0.01381, 0.013028, 0.014843]
                   })

And I'd like to visualize it, so should convert to the format:

df_ = pd.DataFrame({'month': ['M202412','M202501','M202502']*2,
                   'type': ['avg','avg','avg','med','med','med'],
                   'value': [0.037824, 0.03616, 0.038919, 0.01381, 0.013028, 0.014843],
                   })

Which is then easy to visualize:

df_.plot.line(x='month',y='value',color='type').properties(width=400, height=350, title='avg and med')

What is the simplest way to convert df to df_ above?

• python
• python-polars
• altair

Share Improve this question Follow edited Mar 25 at 9:40 Sandipan Dey 23.3k44 gold badges5757 silver badges7171 bronze badges asked Mar 25 at 9:28 lmocsilmocsi 1,09811 gold badge1111 silver badges2626 bronze badges

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 2

In Polars df.melt is deprecated for df.unpivot.

Unpivot does also not require passing the columns to unpivot if the "index" column is specified.

So, in a Polars, a current solution is as below

df = pl.DataFrame({
    'month': ['M202412','M202501','M202502'],
    'avg': [0.037824, 0.03616, 0.038919],
    'med': [0.01381, 0.013028, 0.014843],
})
df_ = df.unpivot(index="month", variable_name="type")

print(df_)

# shape: (6, 3)
# ┌─────────┬──────┬──────────┐
# │ month   ┆ type ┆ value    │
# │ ---     ┆ ---  ┆ ---      │
# │ str     ┆ str  ┆ f64      │
# ╞═════════╪══════╪══════════╡
# │ M202412 ┆ avg  ┆ 0.037824 │
# │ M202501 ┆ avg  ┆ 0.03616  │
# │ M202502 ┆ avg  ┆ 0.038919 │
# │ M202412 ┆ med  ┆ 0.01381  │
# │ M202501 ┆ med  ┆ 0.013028 │
# │ M202502 ┆ med  ┆ 0.014843 │
# └─────────┴──────┴──────────┘

You can try uisng df.melt to convert from wide to long formats. It keeps the month column as an identifier and unpivots the avg and med columns into rows with corresponding type and value columns.

df_ = df.melt(
    id_vars=["month"],
    value_vars=["avg", "med"],
    variable_name="type",
    value_name="value"
)

Full reproducable:

import polars as pl
import plotly.express as px

df = pl.DataFrame({
    'month': ['M202412', 'M202501', 'M202502'],
    'avg': [0.037824, 0.03616, 0.038919],
    'med': [0.01381, 0.013028, 0.014843]
})

df_ = df.melt(
    id_vars=["month"],
    value_vars=["avg", "med"],
    variable_name="type",
    value_name="value"
)

fig = px.line(
    df_.to_pandas(),  
    x='month',
    y='value',
    color='type',
    title='Average and Median Values'
)

fig.update_layout(
    width=400,
    height=350
)

fig.show()