how to create the pivot table in pandas with multiple index?-CodePudding

I have a dataframe with Date as index and LMP and month as values.

import pandas as pd
import numpy as np
df = pd.DataFrame({
        'Date': pd.date_range('01-01-2022 00:00', '31-12-2022 23:00', freq='1H'),
        'LMP': np.random.randint(10, 100, 8760)
    }).set_index('Date')
df['month'] = pd.DatetimeIndex(df.index).month

I want to create the pivot table with month and LMP as index and count of LMP values as values. I tried this :

table = pd.pivot_table(df, values='LMP', index=['month', 'LMP'], columns=['LMP'], aggfunc='count')

Index output as expected but somehow this dataframe is empty and I don't the reason

Empty DataFrame
Columns: []
Index: [(1, 0.0), (1, 14.86921095), (1, 16.642), (1, 16.9799000102), (1, 49.926), (1, 59.99633145), (1, 60.602355), (1, 61.2145), (2, 0.0), (2, 16.642), (2, 19.1963000103), (2, 33.284), (2, 61.2145), (2, 62.4574023059), (2, 71.3282), (3, 0.0), (3, 11.8437231895), (3, 16.3108242), (3, 16.47558), (3, 16.642), (3, 21.4402768105), (3, 33.284), (3, 59.99633145), (3, 61.2145), (4, 0.0), (4, 16.642), (4, 33.284), (4, 54.6936253875), (4, 61.2145), (5, 0.0), (5, 16.642), (5, 19.1963000103), (5, 61.2145), (5, 61.8328282828), (5, 68.5128291222), (5, 71.3282), (6, 0.0), (6, 16.3108242), (6, 16.642), (6, 61.2145), (6, 71.3282), (7, 0.0), (7, 16.3108242), (7, 16.642), (7, 33.284), (7, 59.3963681355), (7, 61.2145), (7, 71.3282), (7, 80.0), (8, 0.0), (8, 16.642), (8, 33.284), (8, 49.926), (8, 54.6936253875), (8, 59.3963681355), (8, 61.2145), (8, 71.3282), (8, 80.0), (9, 0.0), (9, 14.6578492963), (9, 16.642), (9, 18.6261507037), (9, 33.284), (9, 61.2145), (9, 71.3282), (10, 0.0), (10, 16.47558), (10, 16.642), (10, 61.2145), (11, 0.0), (11, 16.642), (11, 33.284), (11, 59.99633145), (11, 61.2145), (12, 0.0), (12, 16.642), (12, 18.6261507037), (12, 33.284), (12, 49.926), (12, 61.2145), (12, 71.3282)]

[81 rows x 0 columns]

The expected output should look like this (this is in excel)

CodePudding user response：

Use groupby and count:

df.groupby(['month', 'LMP'])['LMP'].count()