Resampling and regrouping using pivot table

Question

edited --- code added

I'm trying to group all the values of the dataframe essaie['night_cons'] by day (and by year) but the result just gives me NAN.

colss = {'Date_Time': ['2017-11-10','2017-11-11','2017-11-12','2017-11-13', '2017-11-14', '2017-11-15', '2017-11-16', '2017-11-17', '2017-11-18', '2017-11-19'],
        'Night_Cons(+)': [4470.76,25465.72,25465.72,25465.72, 21480.59, 20024.53, 19613.29, 28015.18, 28394.20, 29615.69]
        }

dataframe = pd.DataFrame(colss, columns = ['Date_Time', 'Night_Cons(+)'])

#print (dataframe)

dataframe['Date_Time'] = pd.to_datetime(dataframe['Date_Time'], errors = 'coerce')

# Create new columns
dataframe['Day'] = dataframe['Date_Time'].dt.day
dataframe['Month'] = dataframe['Date_Time'].dt.month
dataframe['Year'] = dataframe['Date_Time'].dt.year

# Set index
#essaie = essaie.set_index('Date_Time') 

dataframe = dataframe[['Night_Cons(+)', 'Day', 'Month', 'Year']]

#dataframe

#daily_data = pd.pivot_table(essaie, values = "Night_Cons(+)", columns = ["Month"], index = "Day")
daily_data = pd.pivot_table(dataframe, values = "Night_Cons(+)", columns = ["Year"], index = "Day")
daily_data = daily_data.reindex(index = ['Montag','Dienstag','Mittwoch', 'Donnerstag', 'Freitag', 'Samstag', 'Sonntag'])

daily_data

DataFrame and Results

please see the image below.

Answer 1

Sample:

colss = {'Date_Time': ['2017-11-10','2017-11-11','2017-11-12','2017-11-13', '2017-11-14', '2017-11-15', '2017-11-16', '2017-11-17', '2017-11-18', '2017-11-19'],
        'Night_Cons(+)': [4470.76,25465.72,25465.72,25465.72, 21480.59, 20024.53, 19613.29, 28015.18, 28394.20, 29615.69]
        }

dataframe = pd.DataFrame(colss, columns = ['Date_Time', 'Night_Cons(+)'])

---

First convert Date column to Series.dt.dayofweek, then pivoting and last rename index values:

dataframe['Date_Time'] = pd.to_datetime(dataframe['Date_Time'], errors = 'coerce')
dataframe['Year'] = dataframe['Date_Time'].dt.year
dataframe['Date'] = dataframe['Date_Time'].dt.dayofweek
daily_data = dataframe.pivot_table(values = "Night_Cons(+)", 
                                   columns = "Year",
                                   index = "Date")

days = ['Montag','Dienstag','Mittwoch', 'Donnerstag', 'Freitag', 'Samstag', 'Sonntag']

daily_data = daily_data.rename(dict(enumerate(days)))
print (daily_data)
Year             2017
Date                 
Montag      25465.720
Dienstag    21480.590
Mittwoch    20024.530
Donnerstag  19613.290
Freitag     16242.970
Samstag     26929.960
Sonntag     27540.705