3

I want read in weekday data and then reindex the data to fill the weekend with Friday's data. I have tried the following code but it will not reindex the data. Set_index produces a length error message.

import pandas as pd

def fill_dataframe(filename):
    dataf = pd.read_csv(filename, header= None, index_col = [0])
return(dataf)

rng = pd.date_range('10/1/2010', periods=61)
date_rng = pd.DataFrame(rng,index = rng)

data_1.reindex(date_rng, method = 'ffill')

The data read in has 41 rows and the generated date values have 61 rows. Any suggestions?

data read in by csv (1st 7 rows)
        X0     X1
10/1/2010  71.27
10/4/2010  70.33
10/5/2010  72.94
10/6/2010  74.15
10/7/2010  71.40
10/8/2010  72.58
10/11/2010  72.66

dates generated by rng in the second Data Frame (first 11 rows)
                         0
2010-10-01 2010-10-01 00:00:00
2010-10-02 2010-10-02 00:00:00
2010-10-03 2010-10-03 00:00:00
2010-10-04 2010-10-04 00:00:00
2010-10-05 2010-10-05 00:00:00
2010-10-06 2010-10-06 00:00:00
2010-10-07 2010-10-07 00:00:00
2010-10-08 2010-10-08 00:00:00
2010-10-09 2010-10-09 00:00:00
2010-10-10 2010-10-10 00:00:00
2010-10-11 2010-10-11 00:00:00
crayzeewulf
  • 5,840
  • 1
  • 27
  • 30
Dick Eshelman
  • 1,103
  • 2
  • 12
  • 17

1 Answers1

3

Reindexing just by the (1D) timeseries or as a Series this works (in 0.10.1):

data_1.reindex(rng, method = 'ffill')
data_1.reindex(Series(rng, index=rng), method = 'ffill')

.

With date_rng as the DataFrame I get TypeError: Cannot compare Timestamp with 0, I suspect this could be a bug, but I'm not entirely sure what the expected behaviour should be...

Andy Hayden
  • 359,921
  • 101
  • 625
  • 535
  • Andy, I have run both of the methods you suggested and do not get any reindexing. The date rows 10-02-10 & 10-03-10 do not show up in the Data Frame data_1 between 10-01-13 and 10-04-10 under either method, which is what I would have expected. – Dick Eshelman Mar 28 '13 at 02:04
  • 1
    @DickEshelman What version of pandas are you using? 0.10.1 these dates show up. (I copied your first DataFrame to clipboard and used `data_1 = pd.read_clipboard(sep=' ', header=None, parse_dates=[0]).set_index(0)`.) – Andy Hayden Mar 28 '13 at 07:16
  • Andy, I upgraded to 0.10.1 after your response. Because of other attempts to get this to work I think there is some interaction to the Pandas CSV reader. I was not able to get the same results you got with the clipboard. Would you mind trying the same thing using the reader? I will try the clipboard. – Dick Eshelman Mar 28 '13 at 15:50
  • read_clipboard should be equivalent to read_csv, are you parsing the dates with `parse_dates=[0]` ? – Andy Hayden Mar 28 '13 at 15:55
  • 1
    Thank you Andy, Your read parameters are the key, without the "set_index(0)" the reindexing will not work. – Dick Eshelman Mar 29 '13 at 05:36