Pandas pivot table columns (0 to 23) rows (1 to 30 dates)

🎈 Using Streamlit
1

i have dataframe with Date field from that i make year, month, dates, hour, fields
then i will filter dataframe for particular month
then i make pandas pivot table dates as rows and hours as columns (to show count )
but i need full month dates in row and 0 to 23 hours in column
even though there is no data.

kindly help
Thanks in advance
my data:
Stcode ResNo startdate
1101 2223 23-02-2022 23:10
1101 2233 14-02-2022 00:34

2

Hi there,

Thanks for sharing your question with the community! Check out our guidelines on how to post an effective question here – in particular, please include a code snippet that will allow us to reproduce the issue.

Caroline :balloon:

3

data1 = pd.read_csv(“countR.csv”,dayfirst=True, parse_dates=True,encoding=‘latin1’,error_bad_lines=False)

data=data1[[‘Reservation_start_date’,‘Reservation_branch_code’,‘Reservation_vehicle_group’]].copy()

data["Reservation_start_date"]=pd.to_datetime(data['Reservation_start_date'],dayfirst=True)
data['year']=pd.to_datetime(data['Reservation_start_date']).dt.year
data['month'] = pd.to_datetime(data['Reservation_start_date']).dt.month
data['date']=pd.to_datetime(data['Reservation_start_date']).dt.date
data['hour']=pd.to_datetime(data['Reservation_start_date']).dt.hour

here i filter dataframe for a particular month

pivot = data.pivot_table(index=[‘date’,‘WeekdayName’],
columns=‘hour’,
values=‘Reservation_branch_code’,
fill_value=“”,
margins=False,
aggfunc=len)
pivot = pivot.reindex(columns=np.arange(24), fill_value=“”)
pivot = pivot.astype(str)
st.dataframe(pivot)

i get 0-23 hours in column i need all dates of the month in rows
also i need another help counted values showing with fraction i need integer part only
like 1 ,2,5 not 1.0, 2.0, 5.0
many thanks for streamlit community.

4

Hi @B_Ismail,

Thanks for sharing that! Please update your post to format your code properly (the link I shared above provides on instructions on how to do so).

Caroline

5 
data1 = pd.read_csv("countR.csv",dayfirst=True, parse_dates=True,encoding='latin1',error_bad_lines=False)
     

data=data1[['Reservation_start_date','Reservation_branch_code','Reservation_vehicle_group']].copy()

data["Reservation_start_date"]=pd.to_datetime(data['Reservation_start_date'],dayfirst=True)
data['year']=pd.to_datetime(data['Reservation_start_date']).dt.year
data['month'] = pd.to_datetime(data['Reservation_start_date']).dt.month
data['date']=pd.to_datetime(data['Reservation_start_date']).dt.date
data['hour']=pd.to_datetime(data['Reservation_start_date']).dt.hour
data['date']=pd.to_datetime(data['date'])


data=data[data['month']==9]  # may be different month 
data=data[data['Reservation_branch_code']==1163]   # any branch code  


data['date1']=pd.to_datetime(data['date'],dayfirst=True).dt.date
pivot = data.pivot_table(index=['date1'],
                            columns='hour',
                            values='Reservation_branch_code',
                            fill_value="",
                            margins=True,
                            dropna=True,
                            aggfunc=len)

    
pivot = pivot.reindex(columns=np.arange(24) ,fill_value="")

pivot=pivot.astype(str)  # this is needed if fill value is ""  
st.dataframe(pivot)

StreamlitAPIException : (“object of type <class ‘str’> cannot be converted to int”, ‘Conversion failed for column date1 with type object’)

this is the message i get…kindly correct my code…i need fill value “” not 0…and even there is no data i need to show full month dates…and integer number in count not with decimal …many thanks for your time.

6

Hi