Convert data in dataframe - Python

0 votes

I have a dataframe as defined below (df) with daily frequency and I would like to convert this to minute frequency, starting at 7:30 and ending at 15:00.

import pandas as pd
dict = [
        {'ticker':'jpm','date': '2016-11-28','returns': '0.2'},
{ 'ticker':'ge','date': '2016-11-28','returns': '0.2'},
{'ticker':'fb', 'date': '2016-11-28','returns': '0.2'},
{'ticker':'aapl', 'date': '2016-11-28','returns': '0.2'},
{'ticker':'msft','date': '2016-11-28','returns': '0.2'},
{'ticker':'amzn','date': '2016-11-28','returns': '0.2'},
{'ticker':'jpm','date': '2016-11-29','returns': '0.2'},
{'ticker':'ge', 'date': '2016-11-29','returns': '0.2'},
{'ticker':'fb','date': '2016-11-29','returns': '0.2'},
{'ticker':'aapl','date': '2016-11-29','returns': '0.2'},
{'ticker':'msft','date': '2016-11-29','returns': '0.2'},
{'ticker':'amzn','date': '2016-11-29','returns': '0.2'}
df = pd.DataFrame(dict)
df['date']      = pd.to_datetime(df['date'])
df=df.set_index(['date','ticker'], drop=True)  

How do I do this?

May 30 in Python by Vikas

1 answer to this question.

Your answer

Your name to display (optional):
Privacy: Your email address will only be used for sending these notifications.
0 votes

Reshape dataframe using DataFrame.unstack for DatetimeIndex, then set minute frequency by DataFrame.asfreq, filter times by DataFrame.between_time and last use DataFrame.stack for MultiIndex. Use something like this:

df1 = df.unstack().asfreq('Min', method='ffill').between_time('8:30','16:00').stack()
print (df1.head(10))

date                ticker       
2016-11-28 07:30:00 aapl       0.2
                    amzn       0.2
                    fb         0.2
                    ge         0.2
                    jpm        0.2
                    msft       0.2
2016-11-28 07:31:00 aapl       0.2
                    amzn       0.2
                    fb         0.2
                    ge         0.2
answered May 30 by Gargi

Related Questions In Python

0 votes
1 answer

Unique identification for data items in Python

Try the UUID module of Python. For example, ...READ MORE

answered Apr 17, 2018 in Python by Nietzsche's daemon
• 4,260 points
0 votes
1 answer

How can I convert a list of dictionaries from a CSV into a JSON object in Python?

You could try using the AST module. ...READ MORE

answered Apr 17, 2018 in Python by anonymous
0 votes
2 answers

Extracting data from a JSON file in Python

Here is what i found and was ...READ MORE

answered Nov 27, 2018 in Python by Rupali
0 votes
1 answer

How to replace values with None in Pandas data frame in Python?

Actually in later versions of pandas this ...READ MORE

answered Aug 30, 2018 in Python by Priyaj
• 56,140 points
0 votes
1 answer

how can i count the items in a list?

suppose you have a list a = [0,1,2,3,4,5,6,7,8,9,10] now ...READ MORE

answered May 2 in Python by Mohammad
• 1,400 points
+4 votes
6 answers
0 votes
1 answer

How to convert a Pandas GroupBy object to DataFrame in Python

g1 here is a DataFrame. It has a hierarchical index, ...READ MORE

answered Nov 12, 2018 in Python by Nymeria
• 3,500 points
+3 votes
5 answers

How to read multiple data files in python

Firstly we will import pandas to read ...READ MORE

answered Apr 6, 2018 in Python by DeepCoder786
• 1,700 points

© 2018 Brain4ce Education Solutions Pvt. Ltd. All rights Reserved.
"PMP®","PMI®", "PMI-ACP®" and "PMBOK®" are registered marks of the Project Management Institute, Inc. MongoDB®, Mongo and the leaf logo are the registered trademarks of MongoDB, Inc.