CODEWITHSUNDEEP
pandas
EP1986
EP1986

Reputation: 873

convert hourly time period in 15-minute time period

I have a dataframe like that:

df = pd.read_csv("fileA.csv", dtype=str, delimiter=";", skiprows = None, parse_dates=['Date'])

Date         Buy           Sell

0  01.08.2009   01:00          15              25

1  01.08.2009   02:00          0               30

2  01.08.2009   03:00          10              18

But I need that one (in 15-min-periods):

Date         Buy           Sell

0  01.08.2009   01:00          15              25

1  01.08.2009   01:15          15              25

2  01.08.2009   01:30          15              25

3  01.08.2009   01:45          15              25

4  01.08.2009   02:00          0               30

5  01.08.2009   02:15          0               30

6  01.08.2009   02:30          0               30

7  01.08.2009   02:45          0               30

8  01.08.2009   03:00          10              18

....and so on.

I have tried df.resample(). But it does not worked. Does someone know a nice pandas method?!

Upvotes: 1

Views: 165

Answers (1)

unutbu
unutbu

Reputation: 879251

If fileA.csv looks like this:

Date;Buy;Sell
01.08.2009   01:00;15;25
01.08.2009   02:00;0;30
01.08.2009   03:00;10;18

then you could parse the data with 

df = pd.read_csv("fileA.csv", delimiter=";", parse_dates=['Date'])

so that df will look like this:

In [41]: df
Out[41]: 
                 Date  Buy  Sell
0 2009-01-08 01:00:00   15    25
1 2009-01-08 02:00:00    0    30
2 2009-01-08 03:00:00   10    18

You might want to check df.info() to make sure you successfully parsed your data into a DataFrame with three columns, and that the Date column has dtype datetime64[ns]. Since the repr(df) you posted prints the date in a different format and the column headers do not align with the data, there is a good chance that the data has not yet been parsed properly. If that's true and you post some sample lines from the csv, we should be able help you parse the data into a DataFrame.

In [51]: df.info()
<class 'pandas.core.frame.DataFrame'>
Int64Index: 3 entries, 0 to 2
Data columns (total 3 columns):
Date    3 non-null datetime64[ns]
Buy     3 non-null int64
Sell    3 non-null int64
dtypes: datetime64[ns](1), int64(2)
memory usage: 96.0 bytes

Once you have the DataFrame correctly parsed, resampling to 15 minute time periods can be done with asfreq with forward-filling the missing values:

In [50]: df.set_index('Date').asfreq('15T', method='ffill')
Out[50]: 
                     Buy  Sell
2009-01-08 01:00:00   15    25
2009-01-08 01:15:00   15    25
2009-01-08 01:30:00   15    25
2009-01-08 01:45:00   15    25
2009-01-08 02:00:00    0    30
2009-01-08 02:15:00    0    30
2009-01-08 02:30:00    0    30
2009-01-08 02:45:00    0    30
2009-01-08 03:00:00   10    18

Upvotes: 3

Related Questions