Getting month's end data in python
up vote
2
down vote
favorite
I am very new to Python.
I’m trying to figure out how to get the month’s end ‘Adj Close’ rows for each data set (aapl, intc, ebay and amzn).
Here’s what I have done:
import numpy as np
import pandas as pd
from pandas import Series, DataFrame
from pandas.tseries.offsets import Day,MonthEnd
import pandas_datareader.data as web
import datetime
start = datetime.datetime(2013,10,28)
end = datetime.datetime(2018,10,28)
aapl = web.DataReader('AAPL','yahoo',start,end)
intc = web.DataReader('INTC','yahoo',start,end)
ebay = web.DataReader('EBAY','yahoo',start,end)
amzn = web.DataReader('AMZN','yahoo',start,end)
I put in the DataFrame
amazon = amzn
amzn = pd.DataFrame({'AMZN': amazon['Adj Close']})
I wanted the data to show only the month end’s data, so I did this, but it’s giving me an error:
amzn = amzn.loc[pd.date_range(start, end, freq='BM')]
How do I get to show only the month’s end?
I also wanted to create a table, with dates as the index, showing all stock’s adj close during the month’s end.
I tried this and didn’t work (it says that there are duplicate keys):
alldata = pd.merge(aapl,intc,ebay,amzn)
I know that ‘BM’ gives me month’s end, but I can’t seem to find a way to use it.
I’m scratching my head and have been looking; I can’t find the answer.
python pandas
edited Nov 19 at 22:15
halfer
14.3k758107
asked Nov 19 at 21:04
J Lee
111
add a comment |
up vote
2
down vote
favorite
I am very new to Python.
I’m trying to figure out how to get the month’s end ‘Adj Close’ rows for each data set (aapl, intc, ebay and amzn).
Here’s what I have done:
import numpy as np
import pandas as pd
from pandas import Series, DataFrame
from pandas.tseries.offsets import Day,MonthEnd
import pandas_datareader.data as web
import datetime
start = datetime.datetime(2013,10,28)
end = datetime.datetime(2018,10,28)
aapl = web.DataReader('AAPL','yahoo',start,end)
intc = web.DataReader('INTC','yahoo',start,end)
ebay = web.DataReader('EBAY','yahoo',start,end)
amzn = web.DataReader('AMZN','yahoo',start,end)
I put in the DataFrame
amazon = amzn
amzn = pd.DataFrame({'AMZN': amazon['Adj Close']})
I wanted the data to show only the month end’s data, so I did this, but it’s giving me an error:
amzn = amzn.loc[pd.date_range(start, end, freq='BM')]
How do I get to show only the month’s end?
I also wanted to create a table, with dates as the index, showing all stock’s adj close during the month’s end.
I tried this and didn’t work (it says that there are duplicate keys):
alldata = pd.merge(aapl,intc,ebay,amzn)
I know that ‘BM’ gives me month’s end, but I can’t seem to find a way to use it.
I’m scratching my head and have been looking; I can’t find the answer.
python pandas
edited Nov 19 at 22:15
halfer
14.3k758107
asked Nov 19 at 21:04
J Lee
111
do you only want the days that are also months end days? you could do a join on the datetime index.
– MattR
Nov 19 at 21:20
I want the latest day of the month that has values (Feb 28, 2015, March 30... ).
– J Lee
Nov 19 at 21:23
@davedwards oh I see... yes will do. Thanks!
– J Lee
Nov 19 at 22:32
add a comment |
up vote
2
down vote
favorite
up vote
2
down vote
favorite
I am very new to Python.
I’m trying to figure out how to get the month’s end ‘Adj Close’ rows for each data set (aapl, intc, ebay and amzn).
Here’s what I have done:
import numpy as np
import pandas as pd
from pandas import Series, DataFrame
from pandas.tseries.offsets import Day,MonthEnd
import pandas_datareader.data as web
import datetime
start = datetime.datetime(2013,10,28)
end = datetime.datetime(2018,10,28)
aapl = web.DataReader('AAPL','yahoo',start,end)
intc = web.DataReader('INTC','yahoo',start,end)
ebay = web.DataReader('EBAY','yahoo',start,end)
amzn = web.DataReader('AMZN','yahoo',start,end)
I put in the DataFrame
amazon = amzn
amzn = pd.DataFrame({'AMZN': amazon['Adj Close']})
I wanted the data to show only the month end’s data, so I did this, but it’s giving me an error:
amzn = amzn.loc[pd.date_range(start, end, freq='BM')]
How do I get to show only the month’s end?
I also wanted to create a table, with dates as the index, showing all stock’s adj close during the month’s end.
I tried this and didn’t work (it says that there are duplicate keys):
alldata = pd.merge(aapl,intc,ebay,amzn)
I know that ‘BM’ gives me month’s end, but I can’t seem to find a way to use it.
I’m scratching my head and have been looking; I can’t find the answer.
python pandas
edited Nov 19 at 22:15
halfer
14.3k758107
asked Nov 19 at 21:04
J Lee
111
I am very new to Python.
I’m trying to figure out how to get the month’s end ‘Adj Close’ rows for each data set (aapl, intc, ebay and amzn).
Here’s what I have done:
import numpy as np
import pandas as pd
from pandas import Series, DataFrame
from pandas.tseries.offsets import Day,MonthEnd
import pandas_datareader.data as web
import datetime
start = datetime.datetime(2013,10,28)
end = datetime.datetime(2018,10,28)
aapl = web.DataReader('AAPL','yahoo',start,end)
intc = web.DataReader('INTC','yahoo',start,end)
ebay = web.DataReader('EBAY','yahoo',start,end)
amzn = web.DataReader('AMZN','yahoo',start,end)
I put in the DataFrame
amazon = amzn
amzn = pd.DataFrame({'AMZN': amazon['Adj Close']})
I wanted the data to show only the month end’s data, so I did this, but it’s giving me an error:
amzn = amzn.loc[pd.date_range(start, end, freq='BM')]
How do I get to show only the month’s end?
I also wanted to create a table, with dates as the index, showing all stock’s adj close during the month’s end.
I tried this and didn’t work (it says that there are duplicate keys):
alldata = pd.merge(aapl,intc,ebay,amzn)
I know that ‘BM’ gives me month’s end, but I can’t seem to find a way to use it.
I’m scratching my head and have been looking; I can’t find the answer.
python pandas
python pandas
edited Nov 19 at 22:15
halfer
14.3k758107
asked Nov 19 at 21:04
J Lee
111
edited Nov 19 at 22:15
halfer
14.3k758107
asked Nov 19 at 21:04
J Lee
111
edited Nov 19 at 22:15
halfer
14.3k758107
edited Nov 19 at 22:15
halfer
14.3k758107
edited Nov 19 at 22:15
halfer
14.3k758107
14.3k758107
asked Nov 19 at 21:04
J Lee
111
asked Nov 19 at 21:04
J Lee
111
asked Nov 19 at 21:04
J Lee
111
111
do you only want the days that are also months end days? you could do a join on the datetime index.
– MattR
Nov 19 at 21:20
I want the latest day of the month that has values (Feb 28, 2015, March 30... ).
– J Lee
Nov 19 at 21:23
@davedwards oh I see... yes will do. Thanks!
– J Lee
Nov 19 at 22:32
add a comment |
do you only want the days that are also months end days? you could do a join on the datetime index.
– MattR
Nov 19 at 21:20
I want the latest day of the month that has values (Feb 28, 2015, March 30... ).
– J Lee
Nov 19 at 21:23
@davedwards oh I see... yes will do. Thanks!
– J Lee
Nov 19 at 22:32
do you only want the days that are also months end days? you could do a join on the datetime index.
– MattR
Nov 19 at 21:20
do you only want the days that are also months end days? you could do a join on the datetime index.
– MattR
Nov 19 at 21:20
I want the latest day of the month that has values (Feb 28, 2015, March 30... ).
– J Lee
Nov 19 at 21:23
I want the latest day of the month that has values (Feb 28, 2015, March 30... ).
– J Lee
Nov 19 at 21:23
@davedwards oh I see... yes will do. Thanks!
– J Lee
Nov 19 at 22:32
@davedwards oh I see... yes will do. Thanks!
– J Lee
Nov 19 at 22:32
add a comment |
2 Answers
2
active
oldest
votes
up vote
0
down vote
From your example, given a DataFrame:
data = pd.DataFrame({'AMZN': amzn['Adj Close']})
You could try using the is_month_end function:
data.loc[amzn['Adj Close'].index.is_month_end]
This will produce:
AMZN
Date
2013-10-31 364.029999
2013-12-31 398.790009
2014-01-31 358.690002
...
Notice that it is returning results for the last day of the month, if it has a value.
To get the latest day in the month with a value (but not necessarily the last day of the month), you might try:
data.reset_index().loc[d2['Date']
.groupby(pd.DatetimeIndex(data.index)
.to_period('M')).idxmax()]
answered Nov 19 at 21:24
Wes Doyle
6801619
Thank you! This worked -> data = pd.DataFrame({'AMZN': amzn['Adj Close']}). I can't seem to make the date.reset_index to work. I'm still figuring it out :).
– J Lee
Nov 19 at 22:44
add a comment |
up vote
0
down vote
If the date is your index and you want to subset your dataframe with the latest date in the month, try this: df[df.index.day == df.index.days_in_month]
answered Nov 20 at 9:49
Zanshin
594421
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53382600%2fgetting-months-end-data-in-python%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
From your example, given a DataFrame:
data = pd.DataFrame({'AMZN': amzn['Adj Close']})
You could try using the is_month_end function:
data.loc[amzn['Adj Close'].index.is_month_end]
This will produce:
AMZN
Date
2013-10-31 364.029999
2013-12-31 398.790009
2014-01-31 358.690002
...
Notice that it is returning results for the last day of the month, if it has a value.
To get the latest day in the month with a value (but not necessarily the last day of the month), you might try:
data.reset_index().loc[d2['Date']
.groupby(pd.DatetimeIndex(data.index)
.to_period('M')).idxmax()]
answered Nov 19 at 21:24
Wes Doyle
6801619
Thank you! This worked -> data = pd.DataFrame({'AMZN': amzn['Adj Close']}). I can't seem to make the date.reset_index to work. I'm still figuring it out :).
– J Lee
Nov 19 at 22:44
add a comment |
up vote
0
down vote
From your example, given a DataFrame:
data = pd.DataFrame({'AMZN': amzn['Adj Close']})
You could try using the is_month_end function:
data.loc[amzn['Adj Close'].index.is_month_end]
This will produce:
AMZN
Date
2013-10-31 364.029999
2013-12-31 398.790009
2014-01-31 358.690002
...
Notice that it is returning results for the last day of the month, if it has a value.
To get the latest day in the month with a value (but not necessarily the last day of the month), you might try:
data.reset_index().loc[d2['Date']
.groupby(pd.DatetimeIndex(data.index)
.to_period('M')).idxmax()]
answered Nov 19 at 21:24
Wes Doyle
6801619
Thank you! This worked -> data = pd.DataFrame({'AMZN': amzn['Adj Close']}). I can't seem to make the date.reset_index to work. I'm still figuring it out :).
– J Lee
Nov 19 at 22:44
add a comment |
up vote
0
down vote
up vote
0
down vote
From your example, given a DataFrame:
data = pd.DataFrame({'AMZN': amzn['Adj Close']})
You could try using the is_month_end function:
data.loc[amzn['Adj Close'].index.is_month_end]
This will produce:
AMZN
Date
2013-10-31 364.029999
2013-12-31 398.790009
2014-01-31 358.690002
...
Notice that it is returning results for the last day of the month, if it has a value.
To get the latest day in the month with a value (but not necessarily the last day of the month), you might try:
data.reset_index().loc[d2['Date']
.groupby(pd.DatetimeIndex(data.index)
.to_period('M')).idxmax()]
answered Nov 19 at 21:24
Wes Doyle
6801619
From your example, given a DataFrame:
data = pd.DataFrame({'AMZN': amzn['Adj Close']})
You could try using the is_month_end function:
data.loc[amzn['Adj Close'].index.is_month_end]
This will produce:
AMZN
Date
2013-10-31 364.029999
2013-12-31 398.790009
2014-01-31 358.690002
...
Notice that it is returning results for the last day of the month, if it has a value.
To get the latest day in the month with a value (but not necessarily the last day of the month), you might try:
data.reset_index().loc[d2['Date']
.groupby(pd.DatetimeIndex(data.index)
.to_period('M')).idxmax()]
answered Nov 19 at 21:24
Wes Doyle
6801619
edited Nov 19 at 22:09
answered Nov 19 at 21:24
Wes Doyle
6801619
answered Nov 19 at 21:24
Wes Doyle
6801619
answered Nov 19 at 21:24
Wes Doyle
6801619
6801619
Thank you! This worked -> data = pd.DataFrame({'AMZN': amzn['Adj Close']}). I can't seem to make the date.reset_index to work. I'm still figuring it out :).
– J Lee
Nov 19 at 22:44
add a comment |
Thank you! This worked -> data = pd.DataFrame({'AMZN': amzn['Adj Close']}). I can't seem to make the date.reset_index to work. I'm still figuring it out :).
– J Lee
Nov 19 at 22:44
Thank you! This worked -> data = pd.DataFrame({'AMZN': amzn['Adj Close']}). I can't seem to make the date.reset_index to work. I'm still figuring it out :).
– J Lee
Nov 19 at 22:44
Thank you! This worked -> data = pd.DataFrame({'AMZN': amzn['Adj Close']}). I can't seem to make the date.reset_index to work. I'm still figuring it out :).
– J Lee
Nov 19 at 22:44
add a comment |
up vote
0
down vote
If the date is your index and you want to subset your dataframe with the latest date in the month, try this: df[df.index.day == df.index.days_in_month]
answered Nov 20 at 9:49
Zanshin
594421
add a comment |
up vote
0
down vote
If the date is your index and you want to subset your dataframe with the latest date in the month, try this: df[df.index.day == df.index.days_in_month]
answered Nov 20 at 9:49
Zanshin
594421
add a comment |
up vote
0
down vote
up vote
0
down vote
If the date is your index and you want to subset your dataframe with the latest date in the month, try this: df[df.index.day == df.index.days_in_month]
answered Nov 20 at 9:49
Zanshin
594421
If the date is your index and you want to subset your dataframe with the latest date in the month, try this: df[df.index.day == df.index.days_in_month]
answered Nov 20 at 9:49
Zanshin
594421
answered Nov 20 at 9:49
Zanshin
594421
answered Nov 20 at 9:49
Zanshin
594421
answered Nov 20 at 9:49
Zanshin
594421
594421
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53382600%2fgetting-months-end-data-in-python%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
do you only want the days that are also months end days? you could do a join on the datetime index.
– MattR
Nov 19 at 21:20
I want the latest day of the month that has values (Feb 28, 2015, March 30... ).
– J Lee
Nov 19 at 21:23
@davedwards oh I see... yes will do. Thanks!
– J Lee
Nov 19 at 22:32