-1

I have imported this file as a Data Frame in Pandas. The left-most column is time (7 am to 9:15 am. Rows show traffic volume at intersection in 15 minute increments. How do I find the peak hour? or the hour with most volume? To get the hourly volumes, I have to add 4 rows.

I am a newbie with Python and any help is appreciated.

import pandas as pd
f_path ="C:/Users/reggi/Dropbox/1. 2020/6. Programming Python/Text Files/TMC118txt.txt"
df = pd.read_csv(f_path, index_col=0, sep='\s+')

Sample of the data file below:: First column is time in 15 minute increments, first row is traffic count by movement.

 NBL  NBT  NBR  SBL  SBT  SBR  EBL  EBT  EBR  WBL  WBT  WBR

 715    8    3   12    1    1    0    4   93   18   36   68    4

730   16    5   20    5    2    1    0  135   12   39  128    3

745    9    1   29    6    2    3    4  169   21   28  163    6

 800   10    2   33    4    0    4    4  147    8   34  174    6

 815   11    1   30    1    4    3    4   93   10   28  140    8
king_python
  • 105
  • 1
  • 9
  • It would be good if you could share your work in trying to achieve this problem.. !!! – Rishu Shrivastava Apr 23 '20 at 20:07
  • I am a traffic engineer and these are intersection traffic counts every 15 minutes (row) by traffic movement (columns). NBL stands for North Bound Left, SBT - South Bound Through, etc. I am trying to analyze the data using Python. Hope that this helps. – king_python Apr 23 '20 at 20:09
  • Welcome to stack overflow! We ask that you provide a [mcve] including your sample input and output as _text_ in your question, not as a picture or link, so that we can reproduce it. See [How to make good pandas examples](https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples) We also ask that you provide code for what you've tried based on your own research so that we can better understand how to help. – G. Anderson Apr 23 '20 at 20:14
  • I am a newbie and learning my way around stack overflow. I will edit and add my code. Thank you for guiding me. :) – king_python Apr 23 '20 at 20:20
  • There is plenty of information available on Pandas, is there a specific issue which isn't covered by the existing resources? Also, please do not share information as images unless absolutely necessary. See: https://meta.stackoverflow.com/questions/303812/discourage-screenshots-of-code-and-or-errors, https://idownvotedbecau.se/imageofcode, https://idownvotedbecau.se/imageofanexception/. – AMC Apr 23 '20 at 21:38

1 Answers1

0

My approach would be to move the time to a column:

df.reset_index(inplace=True)

Then I would create a new column for hour and one for minutes:

df['hour'] = df['index'].apply(lambda x: x[:-2])
df['minute'] = df['index'].apply(lambda x: x{-2:]

Then you could do a groupby on hour and sum the traffic movements, sort, etc. 

hourly = df.groupby(by='hour').sum()
whege
  • 1,391
  • 1
  • 5
  • 13