title: Python34 NMEA sentences put into xlsx using xlsxwriter
body: <p>I'm working on an nmea sentence project where I get a txt file with nmea sentences delimited by commas. I'm trying to use xlsxwriter to write the results into excel. The results I want are having the timestamp,latitude, longitude and altitude printed in rows that correspond to each other...but I keep getting the first iteration of my 'query' written out five times before the other iterations are written into xlsx. I know there must be a simple solution to this..can you tell me where I'm going wrong?</p>
  
  <hr>
  
  <p>This is the beginning of my script so you have a full picture of what is going on</p>
  
  <pre><code>import os
  import csv
  from csv import *
  import numpy
  import matplotlib
  from numpy import *
  from matplotlib import *
  import matplotlib.pyplot as plt
  from matplotlib.pylab import *
  import numpy as np
  
  
  #to export to excel
  import xlsxwriter
  from xlsxwriter.workbook import Workbook
  
  #to get the csv converter functions
  import os
  import subprocess
  import glob
  
  #to get the datetime functions
  import datetime
  from datetime import datetime
  from pytz import timezone
  import time
  import calendar
  
  
  #creates the path needed for incoming and outgoing files
  path_in = 'C:/Python34/gps_txts/'
  path_out = 'C:/Python34/output_files/'
  
  #prints all the data in the file if you want
  q_show_content = input('Print list of files type y:')
  if q_show_content == 'y':
      for root, dirs, files in os.walk(path_in):
            print(root, dirs, files)
  else:
      print('ok')
  
  
  data = []  #empty because we will store data into it
  
  
  #Reads a CSV file and return it as a list of rows
  def read_csv_file(filename):
      """Reads a CSV file and return it as a list of rows."""
  
      for row in csv.reader(open(filename)):
          data.append(row)
      return data
  
  #request of what file to look at
  print ("- - - - - - - - - - - - -")
  data_file = input('Which file do you want to look at?')
  
  f = open(path_in + data_file)
  read_it = read_csv_file(path_in + data_file)
  
  with f as csvfile:
      readCSV = csv.reader(csvfile,delimiter=',')
      plots = csv.reader(csvfile, delimiter=',')
  
  
  #creates the workbook
  output_filename = input('output filename:')
  workbook = xlsxwriter.Workbook(path_out + output_filename + '.xlsx')
  worksheet = workbook.add_worksheet()
  
  #formatting definitions
  bold    = workbook.add_format({'bold': True})
  date_format = workbook.add_format({'num_format': "m/d/yyyy hh:mm:ss"})
  
  #print number of rows
  print ("- - - - - - - - - - - - -")
  rows = len(read_it)
  print (data_file, " has "+ str(rows) + " rows of data")
  print ("- - - - - - - - - - - - -")
  
  #Counts the number of times a GPS command is observed
  def list_gps_commands(data):
      """Counts the number of times a GPS command is observed.
  
  Returns a dictionary object."""
  
      gps_cmds = dict()
      for row in data:
          try:
              gps_cmds[row[0]] += 1 
          except KeyError:
              gps_cmds[row[0]] = 1
  
      return gps_cmds
  
  print(list_gps_commands(read_it))
  print ("- - - - - - - - - - - - -")
  
  
  #prints all the data in the file if you want
  
  q_show_data = input('Print data inside ' + data_file + ' type y:')
  if q_show_data == 'y':
      for row in read_it:
          print(row)
  else:
      print('ok')
  </code></pre>
  
  <hr>
  
  <p>This is the part I'm having an issue with</p>
  
  <pre><code>#Function process_gps_data for GPS 
  
  NMI = 1852.0
  def process_gps_data(data):
      """Processes GPS data, NMEA 0183 format.
  
  Returns a tuple of arrays: latitude, longitude, velocity [km/h],
  time [sec] and number of satellites.
  See also: http://www.gpsinformation.org/dale/nmea.htm.
      """
  
  
      latitude  = []
      longitude = []
      altitude  = []
      velocity  = []
      timestamp = []
      num_sats  = []
  
  
  
      for row in data:
  
          if row[0] == '$GPRMC':     # Valid position/time sentence
              y = (float(row[3][0:2]) + float(row[3][2:])/60.0)
              if row[4] == "S":
                  y = -y
              latitude.append(y)
              x = (float(row[5][0:3]) + float(row[5][3:])/60.0)
              if row[6] == "W":
                  x = -x
              longitude.append(x)
              print('x,y:',x,y)
              velocity.append(float(row[7])*NMI/1000.0)
  
  
              gpstime = row[1][0:6]                     # hhmmss
              gdate = row[9]                            # ddmmyy
              gpsdate = gdate[4:6]+gdate[2:4]+gdate[0:2]  # yymmdd
  
              real_time =gpsdate + gpstime
              add_date_time = datetime.strptime(real_time, "%y%m%d%H%M%S")
              print(add_date_time)
  
              timestamp.append(add_date_time)
  
  
  
      return (array(latitude), array(longitude), array(velocity), array(timestamp))
  
  
  
  
  
  # how to call process_gps_data()
  (lati, long, v, t_stamp) = process_gps_data(data)
  
  
  print ("- - - - - - - - - - - - -")
  print('lati:',lati)
  print ("- - - - - - - - - - - - -")
  print('long:',long)
  print ("- - - - - - - - - - - - -")
  print('v:',v)
  print ("- - - - - - - - - - - - -")
  print('date:', t_stamp)
  print ("- - - - - - - - - - - - -")
  
  
  
  
  
  #sets up the header row
  worksheet.write('A1','TimeStamp',bold)
  worksheet.write('B1', 'Latitude',bold)
  worksheet.write('C1', 'Longitude',bold)
  worksheet.write('D1', 'Velocity',bold)
  worksheet.autofilter('A1:D1')   #dropdown menu created for filtering
  
  # Create a For loop to iterate through each row in the XLS file, starting at row 2 to skip the headers
  for r, row in enumerate(data, start=1):  #where you want to start printing results inside workbook
      for c, col in enumerate(row):
          worksheet.write_column(r,0, t_stamp, date_format)
          worksheet.write_column(r,1, lati)
          worksheet.write_column(r,2, long)
          worksheet.write_column(r,3, v)
  
  
  workbook.close()
  f.close()
  </code></pre>
  
  <hr>
  
  <p>I am using a dummy gps cache with these lines in a txt file. Notice the GPRMC have a different year in row[9] == 01,02,03</p>
  
  <pre><code>$GPRMC,002454,A,3553.5295,N,13938.6570,E,0.0,43.1,180701,7.1,W,A*3F
  $GPRMB,A,,,,,,,,,,,,A,A*0B
  $GPGGA,002455,3553.5295,N,13938.6570,E,1,05,2.2,18.3,M,39.0,M,,*7F
  $GPRMC,002456,A,3553.5321,N,13938.6581,E,0.0,43.1,180702,7.1,W,A*3D
  $GPRMC,104715.20,A,5100.2111,N,00500.0006,E,21.7,003.0,140803,01.,W*70
  </code></pre>
  
  <p>My results being printed out looks the way that they are expected to...</p>
  
  <pre><code>- - - - - - - - - - - - -
  garmin etrex summit.txt  has 5 rows of data
  - - - - - - - - - - - - -
  {'$GPRMC': 3, '$GPGGA': 1, '$GPRMB': 1}
  - - - - - - - - - - - - -
  Print data inside garmin etrex summit.txt type y:
  ok
  x,y: 139.64428333333333 35.892158333333334
  2000-07-18 00:24:54
  x,y: 139.64430166666668 35.892201666666665
  2001-07-18 00:24:56
  x,y: 5.00001 51.00351833333333
  2003-08-14 10:47:15
  - - - - - - - - - - - - -
  lati: [ 35.89215833  35.89220167  51.00351833]
  - - - - - - - - - - - - -
  long: [ 139.64428333  139.64430167    5.00001   ]
  - - - - - - - - - - - - -
  v: [  0.       0.      40.1884]
  - - - - - - - - - - - - -
  date: [datetime.datetime(2000, 7, 18, 0, 24, 54)
   datetime.datetime(2001, 7, 18, 0, 24, 56)
   datetime.datetime(2003, 8, 14, 10, 47, 15)]
  - - - - - - - - - - - - -
  </code></pre>
  
  <p>But when i look at my produced xlsx file, I have the first five rows looking like this:</p>
  
  <pre><code>TimeStamp             Latitude   Longitude  Velocity        
  7/18/2001 00:24:54  35.89215833 139.6442833   0
  7/18/2001 00:24:54  35.89215833 139.6442833   0
  7/18/2001 00:24:54  35.89215833 139.6442833   0
  7/18/2001 00:24:54  35.89215833 139.6442833   0
  7/18/2001 00:24:54  35.89215833 139.6442833   0
  7/18/2002 00:24:56  35.89220167 139.6443017   0
  8/14/2003 10:47:15  51.00351833 5.00001      40.1884
  </code></pre>
  
  <p>So my issue is that I get 5 of the same first 'query' and then the other 2 iteration of '$GPRMC'</p>
  
  <p>Where am i going wrong?</p>