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, 2019 in Python by Vikas
710 views

1 answer to this question.

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))

                           returns
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, 2019 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
1,140 views
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
3,437 views
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
29,678 views
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
• 58,100 points
11,689 views
0 votes
2 answers
+1 vote
2 answers

how can i count the items in a list?

Syntax :            list. count(value) Code: colors = ['red', 'green', ...READ MORE

answered Jul 7, 2019 in Python by Neha
• 330 points

edited Jul 8, 2019 by Kalgi 4,398 views
0 votes
1 answer
+5 votes
6 answers

Lowercase in Python

You can simply the built-in function in ...READ MORE

answered Apr 11, 2018 in Python by hemant
• 5,790 points
4,009 views
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,560 points
34,426 views
+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,720 points
15,227 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP