title: Pandas DataFrame convert data from BST/Clock to GMT/UTC and vice versa
body: <p>I’m hoping although this seems complex that someone has solved something similar as it is essentially handling daylight savings time.</p>
  
  <p>I have some equipment that records temperature. Some probes record time in GMT/UTC and others record the time in BST/Clock. </p>
  
  <p>The probes record temperature data every hour so for probes that record data GMT/UTC, the data has 24 columns with column 0 corresponding to 00:00, column 1 to 01:00 etc every day of the year.</p>
  
  <p>In the UK the clocks go forward 1 hour at 1am on the last Sunday in March, and back 1 hour at 2am on the last Sunday in October. </p>
  
  <p>For the probes that record data in BST/Clock time, when the clocks go forward, there are only 23 hourly periods of data instead of the normal 24. When the clocks go back there are 25 hourly Periods instead 24. </p>
  
  <p>There are 3 parts to the question. The first is I want to standardise the data in one dataframe in GMT/UTC format so on the day when the clocks go forward and I have 23 readings, I need to move the reading in the first column in the next day/row to column 24 of the previous day. I then need to keep moving all column data, one place to the left. I need to repeat this process of moving the first column of a row to the last column of the previous row and then moving all other columns in the row across 1 place up to and including until the last Sunday in October where there are 25 readings. Here’s an example of what I’m trying to achieve:</p>
  
  <pre><code># BST/Clock Data Format 
  bst = [{0:9, 1:6, 2:7, 3:4, 22:2, 23:1},
          {0:10, 1:12, 2:9, 3:8, 22:16},
          {0:11, 1:9, 2:8, 3:12, 22:15, 23:16}, 
          {0:1, 1:6, 2:5, 3:7, 22:6, 23:8, 24:9},
          {0:2, 1:2, 2:4, 3:4, 22:3, 23:2}] 
  
  
  # Create BST/Clock df
  df_bst = pd.DataFrame(bst, index=['24/03/2018', '25/03/2018', '26/03/2018', '28/10/2018', '29/10/2018'])
  df_bst.index = pd.to_datetime(df_bst.index, dayfirst=True)
  
  
  # Format of what GMT/UTC data should look like
  gmt = [{0:9, 1:6, 2:7, 3:4, 22:2, 23:1},
          {0:10, 1:12, 2:9, 3:8, 22:16, 23:11},
          {0:9, 1:8, 2:12, 3:15, 22:16, 23:5}, 
          {0:6, 1:5, 2:7, 3:6, 22:8, 23:9},
          {0:2, 1:2, 2:4, 3:4, 22:3, 23:2}] 
  
  df_gmt = pd.DataFrame(gmt, index=['24/03/2018', '25/03/2018', '26/03/2018', '28/10/2018', '29/10/2018'])
  df_gmt.index = pd.to_datetime(df_gmt.index, dayfirst=True)
  
  print('Initial format')
  print(df_bst)
  print()
  print('What data should look like after translation with the last Sunday in Mar 2018 and Oct 2018 being 25/03/2018 and 28/10/2018 respectively')
  print(df_gmt)
  </code></pre>
  
  <p>The second part is I would like to calculate the last Sunday in March and October for the data in the dataframe. I was working on something like:</p>
  
  <pre><code>for month in (3, 10):
      last_sunday = max([week[-1] for week in calendar.monthcalendar(2018, month)])
      x = datetime.datetime(2018, month, last_sunday)
      print(x.strftime('%d/%m/%Y'))
  </code></pre>
  
  <p>And extracting the year using the DatetimeIndex.year but there might be times when they dataset spans more than a year so I would need to apply the transformation between 31/03/2018 - 28/10/2018 and then again between 31/03/2019 - today.</p>
  
  <p>The last part of the question is occasionally I would like to report on average temperature between 14:00 BST/Clock - 18:00 BST/Clock. What would be the best way to do this given my data is stored in GMT/UTC?</p>
  
  <p>I’ve tried different options including localize but to no avail. </p>