Resampling and regrouping using pivot table

karlito · (This post was last modified: Apr-25-2020, 11:19 AM by karlito.)

code added

Hi,

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

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

Thks

**buran** · Apr-25-2020, 10:59 AM

Please, don't post images of code and data.
Copy/paste your code and data using BBcode tags.
See BBcode help for more info.
https://idownvotedbecau.se/imageofcode

karlito · Apr-25-2020, 11:20 AM

(Apr-25-2020, 10:59 AM)buran Wrote: Please, don't post images of code and data.
Copy/paste your code and data using BBcode tags.
See BBcode help for more info.
https://idownvotedbecau.se/imageofcode

Hi, code edited. thks

**buran** · Apr-25-2020, 11:50 AM

if I understand correctly

import pandas as pd
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
dataframe['DayOfWeek'] = dataframe['Date_Time'].dt.dayofweek
 
# Set index
#essaie = essaie.set_index('Date_Time') 
 
dataframe = dataframe[['Night_Cons(+)', 'Day', 'Month', 'Year', 'DayOfWeek']]
print(dataframe)
 
#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=dataframe['DayOfWeek']) #, ['Montag','Dienstag','Mittwoch', 'Donnerstag', 'Freitag', 'Samstag', 'Sonntag'])))
print(daily_data)
# daily_data.reindex(daily_data['Night_Cons(+)'].sort_values(by='DayOfWeek', ascending=True))
daily_data = daily_data.rename(index=dict(zip(range(7), ['Montag','Dienstag','Mittwoch', 'Donnerstag', 'Freitag', 'Samstag', 'Sonntag'])))

print(daily_data)

Output:    Date_Time  Night_Cons(+)
0  2017-11-10        4470.76
1  2017-11-11       25465.72
2  2017-11-12       25465.72
3  2017-11-13       25465.72
4  2017-11-14       21480.59
5  2017-11-15       20024.53
6  2017-11-16       19613.29
7  2017-11-17       28015.18
8  2017-11-18       28394.20
9  2017-11-19       29615.69
   Night_Cons(+)  Day  Month  Year  DayOfWeek
0        4470.76   10     11  2017          4
1       25465.72   11     11  2017          5
2       25465.72   12     11  2017          6
3       25465.72   13     11  2017          0
4       21480.59   14     11  2017          1
5       20024.53   15     11  2017          2
6       19613.29   16     11  2017          3
7       28015.18   17     11  2017          4
8       28394.20   18     11  2017          5
9       29615.69   19     11  2017          6
Year            2017
DayOfWeek           
0          25465.720
1          21480.590
2          20024.530
3          19613.290
4          16242.970
5          26929.960
6          27540.705
Year             2017
DayOfWeek            
Montag      25465.720
Dienstag    21480.590
Mittwoch    20024.530
Donnerstag  19613.290
Freitag     16242.970
Samstag     26929.960
Sonntag     27540.705

Note that at the moment aggregate function in the pivot_table is mean (i.e. the default.

karlito · Apr-25-2020, 04:03 PM

(Apr-25-2020, 11:50 AM)buran Wrote: if I understand correctly

import pandas as pd
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
dataframe['DayOfWeek'] = dataframe['Date_Time'].dt.dayofweek
 
# Set index
#essaie = essaie.set_index('Date_Time') 
 
dataframe = dataframe[['Night_Cons(+)', 'Day', 'Month', 'Year', 'DayOfWeek']]
print(dataframe)
 
#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=dataframe['DayOfWeek']) #, ['Montag','Dienstag','Mittwoch', 'Donnerstag', 'Freitag', 'Samstag', 'Sonntag'])))
print(daily_data)
# daily_data.reindex(daily_data['Night_Cons(+)'].sort_values(by='DayOfWeek', ascending=True))
daily_data = daily_data.rename(index=dict(zip(range(7), ['Montag','Dienstag','Mittwoch', 'Donnerstag', 'Freitag', 'Samstag', 'Sonntag'])))

print(daily_data)

Output:    Date_Time  Night_Cons(+)
0  2017-11-10        4470.76
1  2017-11-11       25465.72
2  2017-11-12       25465.72
3  2017-11-13       25465.72
4  2017-11-14       21480.59
5  2017-11-15       20024.53
6  2017-11-16       19613.29
7  2017-11-17       28015.18
8  2017-11-18       28394.20
9  2017-11-19       29615.69
   Night_Cons(+)  Day  Month  Year  DayOfWeek
0        4470.76   10     11  2017          4
1       25465.72   11     11  2017          5
2       25465.72   12     11  2017          6
3       25465.72   13     11  2017          0
4       21480.59   14     11  2017          1
5       20024.53   15     11  2017          2
6       19613.29   16     11  2017          3
7       28015.18   17     11  2017          4
8       28394.20   18     11  2017          5
9       29615.69   19     11  2017          6
Year            2017
DayOfWeek           
0          25465.720
1          21480.590
2          20024.530
3          19613.290
4          16242.970
5          26929.960
6          27540.705
Year             2017
DayOfWeek            
Montag      25465.720
Dienstag    21480.590
Mittwoch    20024.530
Donnerstag  19613.290
Freitag     16242.970
Samstag     26929.960
Sonntag     27540.705

Note that at the moment aggregate function in the pivot_table is mean (i.e. the default.

Thks

Possibly Related Threads…
Thread		Author	Replies	Views	Last Post
	pivot table (excel vs python)	Bric	1	2,345	Feb-02-2022, 11:04 PM Last Post: Bric
	Python and a pivot table	stilwellj	0	3,424	Nov-04-2019, 01:00 AM Last Post: stilwellj
	Pivot Table hash problem	stilwellj	1	2,854	Oct-30-2019, 08:14 AM Last Post: Larz60+
	Pivot dataframe	SriMekala	2	3,071	Jun-22-2019, 03:02 AM Last Post: SriMekala

Resampling and regrouping using pivot table

User Panel Messages

Announcements