Pandas Groupby is returning an empty dataframe

Question

I have been trying to combine a set of data (77x1005 rows) using a Pandas dataframe with the groupby option. I need to sum the grouped values of each column. Whilst I am able to get a result if I put some columns manually, I get an empty dataframe if I select almost all columns.

This is the code I am using

    MD_transpose_freq=Machine_Data_filter_freq.transpose()
    MD_freq_temp=MD_transpose_freq
    MD_freq_temp.iloc[2:,:]=MD_transpose_freq.iloc[2:,:].sort_values([0,1])
    MD_freq_temp.columns=MD_freq_temp.iloc[0] # we copy the first row as the column name
    MD_freq=MD_freq_temp[2:] #We delete the first two rows so make the grouping easier
    MD_freq=MD_freq.reset_index(drop=True)

    List_freq=list(MD_freq[5:].columns)
    List_freq=List_freq[5:] #we remove the columns we do not need.
    
    MD_freq_day=MD_freq.groupby(['Date','Week','Month','Year']).sum()

And this is the return I got

MD_freq_day
Out[614]: 
Empty DataFrame
Columns: []
Index: [(2022-05-01, 17, 5, 2022), (2022-05-02, 18, 5, 2022), (2022-05-03, 18, 5, 2022), (2022-05-04, 18, 5, 2022), (2022-05-05, 18, 5, 2022), (2022-05-06, 18, 5, 2022), (2022-06-01, 22, 6, 2022), (2022-06-02, 22, 6, 2022), (2022-06-03, 22, 6, 2022), (2022-06-04, 22, 6, 2022), (2022-06-05, 22, 6, 2022), (2022-07-01, 26, 7, 2022), (2022-07-02, 26, 7, 2022), (2022-07-03, 26, 7, 2022), (2022-07-04, 27, 7, 2022), (2022-07-05, 27, 7, 2022), (2022-08-01, 31, 8, 2022), (2022-08-02, 31, 8, 2022), (2022-08-03, 31, 8, 2022), (2022-08-04, 31, 8, 2022), (2022-08-05, 31, 8, 2022), (2022-09-01, 35, 9, 2022), (2022-09-02, 35, 9, 2022), (2022-09-03, 35, 9, 2022), (2022-09-04, 35, 9, 2022), (2022-09-05, 36, 9, 2022)]

I have also tried the following variation

MD_freq_day=MD_freq.groupby(['Date','Week','Month','Year'], as_index=False)[List_freq].sum()

And I get the following error

Traceback (most recent call last):

  File "C:\Users\jvms\AppData\Local\Temp\ipykernel_20172\4128687025.py", line 1, in 
    MD_freq_day=MD_freq.groupby(['Date','Week','Month','Year'], as_index=False)[List_freq].sum()

  File "C:\Users\jvms\OneDrive - Cap\Personal\Portable Apps\Python-Portable-3.9.6\apps\lib\site-packages\pandas\core\groupby\generic.py", line 1538, in __getitem__
    return super().__getitem__(key)

  File "C:\Users\jvms\OneDrive - Cap\Personal\Portable Apps\Python-Portable-3.9.6\apps\lib\site-packages\pandas\core\base.py", line 222, in __getitem__
    raise KeyError(f"Columns not found: {str(bad_keys)[1:-1]}")

KeyError: 'Columns not found: '

This is how my data looks like Data screenshot

My data does not have any empty values or na values.

Pandas Groupby is returning an empty dataframe

0 Answers