By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
439,986 Members | 1,568 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 439,986 IT Pros & Developers. It's quick & easy.

How to insert data where it's missing?

P: 11
Hello,

I am trying to insert -9999 for any missing 10 minute data in a text file I am reading.

Data format sample is as follows in the input text file:

061201 1910 4.88 5.01
061201 1920 4.54 4.71
061201 1930 4.58 5.20
061201 2010 4.65 4.91

Required output:

061201 1910 4.88 5.01
061201 1920 4.54 4.71
061201 1930 4.58 5.20
061201 1940 -9999 -9999
061201 1950 -9999 -9999
061201 2000 -9999 -9999
061201 2010 4.65 4.91

I would appreciate any help. Thank you, RB
Jan 12 '11 #1
Share this Question
Share on Google+
8 Replies


Expert 100+
P: 621
Why aren't you including 1960-1990, and what happens if 1950 is the first record or the last record. Should the values always range from 1910-2010?
Expand|Select|Wrap|Line Numbers
  1. test_list = ["061201 1910 4.88 5.01", 
  2. "061201 1920 4.54 4.71",
  3. "061201 1930 4.58 5.20",
  4. "061201 2010 4.65 4.91" ]
  5.  
  6. test_dict = {}
  7. first = ""
  8. for rec in test_list:
  9.     substrs = rec.split()
  10.     test_dict[int(substrs[1])] = rec
  11.     first = substrs[0]
  12.  
  13. years_list = [1910, 1920, 1930, 1940, 1950, 2000, 2010]
  14. for year in years_list:
  15.     if year in test_dict:
  16.         print test_dict[year]
  17.     else:
  18.         print "%s %d -9999 -9999" % (first, year) 
Jan 12 '11 #2

P: 11
Here's more detail: Col 0 is date (mmddyy), Col 1 is time (hhmm), cols 2,3 are wind speeds. Col 1 time and Col 0 date may start at any time whenever sensor starts measuring data. Once I fill in missing dates, times and values (-9999), then I'll go back and make hourly averages of the 10minute data - but I know how to do that.
061201 1910 4.88 5.01
061201 1920 4.54 4.71
061201 1930 4.58 5.20
061201 2010 4.65 4.91
Jan 12 '11 #3

Rabbit
Expert Mod 10K+
P: 12,373
Why would you want to use -9999 for missing values if you're going to calculate averages? It's going to throw your averages way off and will also give you negative averages.

While dwblas' code isn't exactly what you wanted, it contains the basic concept of what you're trying to accomplish. You should be able to use that as a starting point to move towards your end goal.
Jan 12 '11 #4

P: 11
Thanks for the feedback - I'll deal with the -9999's in the average calculations loop.
Jan 12 '11 #5

bvdet
Expert Mod 2.5K+
P: 2,851
Here's my suggested code:
Expand|Select|Wrap|Line Numbers
  1. data = '''061201 1910  4.88  5.01
  2. 061201 1920  4.54  4.71
  3. 061201 1930  4.58  5.20
  4. 061201 2010  4.65  4.91'''
  5.  
  6. # parse the data string to simulate reading in the file
  7. dataList = [[s.strip() for s in item.split() if s.strip()] \
  8.             for item in data.split("\n")]
  9.  
  10. # create time list of 10 minute intervals based on first and last time
  11. start = int(dataList[0][1])
  12. stop = int(dataList[-1][1])
  13. timeList = ["%s" % (start+i*10) for i in range((stop-start)/10+1)]
  14.  
  15. # create dictionary from dataList, then add missing data
  16. dateStr = dataList[0][0]
  17. dd = {}
  18. for item in dataList:
  19.     dd[item[1]] = [item[i] for i in [0,2,3]]
  20. for timeStr in timeList:
  21.     dd.setdefault(timeStr, [dateStr, "-9999", "-9999"])
  22.  
  23. # join data in dictionary for output
  24. output = "\n".join([" ".join([dd[key][0],
  25.                               key, dd[key][1],
  26.                               dd[key][2]]) for key in timeList])
  27.  
  28. print output
Output:
Expand|Select|Wrap|Line Numbers
  1. >>> 061201 1910 4.88 5.01
  2. 061201 1920 4.54 4.71
  3. 061201 1930 4.58 5.20
  4. 061201 1940 -9999 -9999
  5. 061201 1950 -9999 -9999
  6. 061201 1960 -9999 -9999
  7. 061201 1970 -9999 -9999
  8. 061201 1980 -9999 -9999
  9. 061201 1990 -9999 -9999
  10. 061201 2000 -9999 -9999
  11. 061201 2010 4.65 4.91
  12. >>> 
Jan 12 '11 #6

P: 11
Hello,
I'm trying to follow your (dwblas) code solution above but I need some comments along the way. Could you briefly comment each line please? Thanks, RB
Jan 12 '11 #7

P: 11
bvdet,

I'm interested to know how you would change your solution above since column 2 is in the form of hhmm and so after 1950 the output needs to be 2000 and so on. And, after 2350, the date needs to change to 061202. How would you adjust for this? Thanks much! RB
Jan 28 '11 #8

bvdet
Expert Mod 2.5K+
P: 2,851
In view of that requirement, the best option would be to go to the time module.

Convert the string "061201 1910" into a struct_time object, then convert to the number of seconds since the epoch. Add 10 minutes (600 seconds) and convert back to a string.
Expand|Select|Wrap|Line Numbers
  1. >>> import time
  2. >>> stObj = time.strptime("061201 1910", "%y%m%d %H%M")
  3. >>> stObj
  4. (2006, 12, 1, 19, 10, 0, 4, 335, -1)
  5. >>> seconds = time.mktime(stObj)
  6. >>> seconds
  7. 1165021800.0
  8. >>> newStr = time.strftime("%y%m%d %H%M", time.localtime(seconds+600))
  9. >>> newStr
  10. '061201 1920'
  11. >>> stObj = time.strptime("061201 2350", "%y%m%d %H%M")
  12. >>> time.strftime("%y%m%d %H%M", time.localtime(time.mktime(stObj)+600))
  13. '061202 0000'
  14. >>> stObj = time.strptime("061201 1950", "%y%m%d %H%M")
  15. >>> time.strftime("%y%m%d %H%M", time.localtime(time.mktime(stObj)+600))
  16. '061201 2000'
  17. >>> 
Jan 29 '11 #9

Post your reply

Sign in to post your reply or Sign up for a free account.