Pandas date formatting
0
I have the below two functions :
def create_base_df(start_date, end_date):
base_df = pd.DataFrame({"dt": pd.date_range(start_date, end_date)})
base_df["dt_num_key"] = base_df.dt.apply(lambda x: datetime.datetime.strftime(x, "%Y%m%d")).astype(int)
base_df["cal_yr_nkey"] = base_df.dt.dt.strftime("%Y")
base_df["cal_mon_ofyr_nkey"] = base_df.dt.dt.strftime("%m")
base_df["cal_qtr_ofyr_nkey"] = base_df.dt.dt.quarter.astype(str).apply(lambda x: x.rjust(2, '0'))
base_df["cal_wk_ofyr_nkey"] = base_df.dt.dt.week.astype(str)
return base_df
def month_operations(df):
df["cal_mon_nm"] = df.dt.dt.strftime("%B")
df["cal_mon_shrt_nm"] = df.dt.dt.strftime("%b")
df["cal_yr_mon_nkey"] = df["cal_yr_nkey"] + df["cal_mon_ofyr_nkey"]
df["mon_seq_id"] = df.cal_yr_mon_nkey.sort_values().reset_index() ["cal_yr_mon_nkey"].rank(method='dense').astype(int)
df["dt_frst_dayof_mon"] = df.dt.apply(lambda x: datetime.datetime(x.year, x.month, 1))
df["dt_frst_dayof_mon_nkey"] = df["dt_frst_dayof_mon"].dt.strftime("%Y%m%d")
df["dt_lst_dayof_mon"] = df["dt_frst_dayof_mon"] + pd.tseries.offsets.DateOffset(
months=1) - pd.tseries.offsets.DateOffset(days=1)
df["dt_lst_dayof_mon_nkey"] = df["dt_lst_dayof_mon"].dt.strftime("%Y%m%d")
df["dt_frst_dayof_lst_mon"] = df["dt_frst_dayof_mon"] - pd.DateOffset(months=1)
df["dt_frst_dayof_lst_mon_nkey"] = df["dt_frst_dayof_lst_mon"].dt.strftime("%Y%m%d")
df["dt_lst_mon"] = df.dt - pd.tseries.offsets.DateOffset(months=1)
df["dt_lst_mon_nkey"] = df["dt_lst_mon"].dt.strftime("%Y%m%d")
df["dt_lst_yr_lst_mon"] = df.dt_lst_mon - pd.tseries.offsets.DateOffset(years=1)
df["dt_lst_yr_lst_mon_nkey"] = df["dt_lst_yr_lst_mon"].dt.strftime("%Y%m%d")
return df
The columns dt_lst_yr_lst_mon_nkey, dt_lst_mon_nkey and dt_frst_dayof_lst_mon_nkey are returning values in datetime format ('1899-12-01 00:00:00') and I cant seem to figure out why. All the other *key columns return integers as expected
my main looks like below:
base_df = create_base_df(start_date="01/01/1900", end_date="01/12/1900")
month_df = month_operations(base_df)
The expected output : if the value of dt_lst_yr_lst_mon is "1900-12-01 00:00:00" then dt_lst_yr_lst_mon_nkey will be "19001201"
Any pointers on where I am going wrong is appreciated.
Thanks.
python pandas dataframe
asked Nov 19 at 11:38
Abhi
497
add a comment |
0
I have the below two functions :
def create_base_df(start_date, end_date):
base_df = pd.DataFrame({"dt": pd.date_range(start_date, end_date)})
base_df["dt_num_key"] = base_df.dt.apply(lambda x: datetime.datetime.strftime(x, "%Y%m%d")).astype(int)
base_df["cal_yr_nkey"] = base_df.dt.dt.strftime("%Y")
base_df["cal_mon_ofyr_nkey"] = base_df.dt.dt.strftime("%m")
base_df["cal_qtr_ofyr_nkey"] = base_df.dt.dt.quarter.astype(str).apply(lambda x: x.rjust(2, '0'))
base_df["cal_wk_ofyr_nkey"] = base_df.dt.dt.week.astype(str)
return base_df
def month_operations(df):
df["cal_mon_nm"] = df.dt.dt.strftime("%B")
df["cal_mon_shrt_nm"] = df.dt.dt.strftime("%b")
df["cal_yr_mon_nkey"] = df["cal_yr_nkey"] + df["cal_mon_ofyr_nkey"]
df["mon_seq_id"] = df.cal_yr_mon_nkey.sort_values().reset_index() ["cal_yr_mon_nkey"].rank(method='dense').astype(int)
df["dt_frst_dayof_mon"] = df.dt.apply(lambda x: datetime.datetime(x.year, x.month, 1))
df["dt_frst_dayof_mon_nkey"] = df["dt_frst_dayof_mon"].dt.strftime("%Y%m%d")
df["dt_lst_dayof_mon"] = df["dt_frst_dayof_mon"] + pd.tseries.offsets.DateOffset(
months=1) - pd.tseries.offsets.DateOffset(days=1)
df["dt_lst_dayof_mon_nkey"] = df["dt_lst_dayof_mon"].dt.strftime("%Y%m%d")
df["dt_frst_dayof_lst_mon"] = df["dt_frst_dayof_mon"] - pd.DateOffset(months=1)
df["dt_frst_dayof_lst_mon_nkey"] = df["dt_frst_dayof_lst_mon"].dt.strftime("%Y%m%d")
df["dt_lst_mon"] = df.dt - pd.tseries.offsets.DateOffset(months=1)
df["dt_lst_mon_nkey"] = df["dt_lst_mon"].dt.strftime("%Y%m%d")
df["dt_lst_yr_lst_mon"] = df.dt_lst_mon - pd.tseries.offsets.DateOffset(years=1)
df["dt_lst_yr_lst_mon_nkey"] = df["dt_lst_yr_lst_mon"].dt.strftime("%Y%m%d")
return df
The columns dt_lst_yr_lst_mon_nkey, dt_lst_mon_nkey and dt_frst_dayof_lst_mon_nkey are returning values in datetime format ('1899-12-01 00:00:00') and I cant seem to figure out why. All the other *key columns return integers as expected
my main looks like below:
base_df = create_base_df(start_date="01/01/1900", end_date="01/12/1900")
month_df = month_operations(base_df)
The expected output : if the value of dt_lst_yr_lst_mon is "1900-12-01 00:00:00" then dt_lst_yr_lst_mon_nkey will be "19001201"
Any pointers on where I am going wrong is appreciated.
Thanks.
python pandas dataframe
asked Nov 19 at 11:38
Abhi
497
1
Please provide example of expected output.
– user3471881
Nov 19 at 12:28
Hi, Thanks for the feedback have updated the question to show the expected output for one of the columns I am having issues with.
– Abhi
Nov 19 at 12:56
When I run your code without modifications it returns your expected output.
– user3471881
Nov 19 at 13:01
Strange, but I keep getting the same date format instead of the integer compatible one. Is there any other way of doing this?
– Abhi
Nov 19 at 13:24
on jupyter notebook using python3 i get the desired output, but my original environment is 2.7 on an EMR cluster.
– Abhi
Nov 19 at 13:33
add a comment |
0
0
0
I have the below two functions :
def create_base_df(start_date, end_date):
base_df = pd.DataFrame({"dt": pd.date_range(start_date, end_date)})
base_df["dt_num_key"] = base_df.dt.apply(lambda x: datetime.datetime.strftime(x, "%Y%m%d")).astype(int)
base_df["cal_yr_nkey"] = base_df.dt.dt.strftime("%Y")
base_df["cal_mon_ofyr_nkey"] = base_df.dt.dt.strftime("%m")
base_df["cal_qtr_ofyr_nkey"] = base_df.dt.dt.quarter.astype(str).apply(lambda x: x.rjust(2, '0'))
base_df["cal_wk_ofyr_nkey"] = base_df.dt.dt.week.astype(str)
return base_df
def month_operations(df):
df["cal_mon_nm"] = df.dt.dt.strftime("%B")
df["cal_mon_shrt_nm"] = df.dt.dt.strftime("%b")
df["cal_yr_mon_nkey"] = df["cal_yr_nkey"] + df["cal_mon_ofyr_nkey"]
df["mon_seq_id"] = df.cal_yr_mon_nkey.sort_values().reset_index() ["cal_yr_mon_nkey"].rank(method='dense').astype(int)
df["dt_frst_dayof_mon"] = df.dt.apply(lambda x: datetime.datetime(x.year, x.month, 1))
df["dt_frst_dayof_mon_nkey"] = df["dt_frst_dayof_mon"].dt.strftime("%Y%m%d")
df["dt_lst_dayof_mon"] = df["dt_frst_dayof_mon"] + pd.tseries.offsets.DateOffset(
months=1) - pd.tseries.offsets.DateOffset(days=1)
df["dt_lst_dayof_mon_nkey"] = df["dt_lst_dayof_mon"].dt.strftime("%Y%m%d")
df["dt_frst_dayof_lst_mon"] = df["dt_frst_dayof_mon"] - pd.DateOffset(months=1)
df["dt_frst_dayof_lst_mon_nkey"] = df["dt_frst_dayof_lst_mon"].dt.strftime("%Y%m%d")
df["dt_lst_mon"] = df.dt - pd.tseries.offsets.DateOffset(months=1)
df["dt_lst_mon_nkey"] = df["dt_lst_mon"].dt.strftime("%Y%m%d")
df["dt_lst_yr_lst_mon"] = df.dt_lst_mon - pd.tseries.offsets.DateOffset(years=1)
df["dt_lst_yr_lst_mon_nkey"] = df["dt_lst_yr_lst_mon"].dt.strftime("%Y%m%d")
return df
The columns dt_lst_yr_lst_mon_nkey, dt_lst_mon_nkey and dt_frst_dayof_lst_mon_nkey are returning values in datetime format ('1899-12-01 00:00:00') and I cant seem to figure out why. All the other *key columns return integers as expected
my main looks like below:
base_df = create_base_df(start_date="01/01/1900", end_date="01/12/1900")
month_df = month_operations(base_df)
The expected output : if the value of dt_lst_yr_lst_mon is "1900-12-01 00:00:00" then dt_lst_yr_lst_mon_nkey will be "19001201"
Any pointers on where I am going wrong is appreciated.
Thanks.
python pandas dataframe
asked Nov 19 at 11:38
Abhi
497
I have the below two functions :
def create_base_df(start_date, end_date):
base_df = pd.DataFrame({"dt": pd.date_range(start_date, end_date)})
base_df["dt_num_key"] = base_df.dt.apply(lambda x: datetime.datetime.strftime(x, "%Y%m%d")).astype(int)
base_df["cal_yr_nkey"] = base_df.dt.dt.strftime("%Y")
base_df["cal_mon_ofyr_nkey"] = base_df.dt.dt.strftime("%m")
base_df["cal_qtr_ofyr_nkey"] = base_df.dt.dt.quarter.astype(str).apply(lambda x: x.rjust(2, '0'))
base_df["cal_wk_ofyr_nkey"] = base_df.dt.dt.week.astype(str)
return base_df
def month_operations(df):
df["cal_mon_nm"] = df.dt.dt.strftime("%B")
df["cal_mon_shrt_nm"] = df.dt.dt.strftime("%b")
df["cal_yr_mon_nkey"] = df["cal_yr_nkey"] + df["cal_mon_ofyr_nkey"]
df["mon_seq_id"] = df.cal_yr_mon_nkey.sort_values().reset_index() ["cal_yr_mon_nkey"].rank(method='dense').astype(int)
df["dt_frst_dayof_mon"] = df.dt.apply(lambda x: datetime.datetime(x.year, x.month, 1))
df["dt_frst_dayof_mon_nkey"] = df["dt_frst_dayof_mon"].dt.strftime("%Y%m%d")
df["dt_lst_dayof_mon"] = df["dt_frst_dayof_mon"] + pd.tseries.offsets.DateOffset(
months=1) - pd.tseries.offsets.DateOffset(days=1)
df["dt_lst_dayof_mon_nkey"] = df["dt_lst_dayof_mon"].dt.strftime("%Y%m%d")
df["dt_frst_dayof_lst_mon"] = df["dt_frst_dayof_mon"] - pd.DateOffset(months=1)
df["dt_frst_dayof_lst_mon_nkey"] = df["dt_frst_dayof_lst_mon"].dt.strftime("%Y%m%d")
df["dt_lst_mon"] = df.dt - pd.tseries.offsets.DateOffset(months=1)
df["dt_lst_mon_nkey"] = df["dt_lst_mon"].dt.strftime("%Y%m%d")
df["dt_lst_yr_lst_mon"] = df.dt_lst_mon - pd.tseries.offsets.DateOffset(years=1)
df["dt_lst_yr_lst_mon_nkey"] = df["dt_lst_yr_lst_mon"].dt.strftime("%Y%m%d")
return df
The columns dt_lst_yr_lst_mon_nkey, dt_lst_mon_nkey and dt_frst_dayof_lst_mon_nkey are returning values in datetime format ('1899-12-01 00:00:00') and I cant seem to figure out why. All the other *key columns return integers as expected
my main looks like below:
base_df = create_base_df(start_date="01/01/1900", end_date="01/12/1900")
month_df = month_operations(base_df)
The expected output : if the value of dt_lst_yr_lst_mon is "1900-12-01 00:00:00" then dt_lst_yr_lst_mon_nkey will be "19001201"
Any pointers on where I am going wrong is appreciated.
Thanks.
python pandas dataframe
python pandas dataframe
asked Nov 19 at 11:38
Abhi
497
asked Nov 19 at 11:38
Abhi
497
edited Nov 19 at 12:55
asked Nov 19 at 11:38
Abhi
497
asked Nov 19 at 11:38
Abhi
497
asked Nov 19 at 11:38
Abhi
497
497
1
Please provide example of expected output.
– user3471881
Nov 19 at 12:28
Hi, Thanks for the feedback have updated the question to show the expected output for one of the columns I am having issues with.
– Abhi
Nov 19 at 12:56
When I run your code without modifications it returns your expected output.
– user3471881
Nov 19 at 13:01
Strange, but I keep getting the same date format instead of the integer compatible one. Is there any other way of doing this?
– Abhi
Nov 19 at 13:24
on jupyter notebook using python3 i get the desired output, but my original environment is 2.7 on an EMR cluster.
– Abhi
Nov 19 at 13:33
add a comment |
1
Please provide example of expected output.
– user3471881
Nov 19 at 12:28
Hi, Thanks for the feedback have updated the question to show the expected output for one of the columns I am having issues with.
– Abhi
Nov 19 at 12:56
When I run your code without modifications it returns your expected output.
– user3471881
Nov 19 at 13:01
Strange, but I keep getting the same date format instead of the integer compatible one. Is there any other way of doing this?
– Abhi
Nov 19 at 13:24
on jupyter notebook using python3 i get the desired output, but my original environment is 2.7 on an EMR cluster.
– Abhi
Nov 19 at 13:33
1
1
Please provide example of expected output.
– user3471881
Nov 19 at 12:28
Please provide example of expected output.
– user3471881
Nov 19 at 12:28
Hi, Thanks for the feedback have updated the question to show the expected output for one of the columns I am having issues with.
– Abhi
Nov 19 at 12:56
Hi, Thanks for the feedback have updated the question to show the expected output for one of the columns I am having issues with.
– Abhi
Nov 19 at 12:56
When I run your code without modifications it returns your expected output.
– user3471881
Nov 19 at 13:01
When I run your code without modifications it returns your expected output.
– user3471881
Nov 19 at 13:01
Strange, but I keep getting the same date format instead of the integer compatible one. Is there any other way of doing this?
– Abhi
Nov 19 at 13:24
Strange, but I keep getting the same date format instead of the integer compatible one. Is there any other way of doing this?
– Abhi
Nov 19 at 13:24
on jupyter notebook using python3 i get the desired output, but my original environment is 2.7 on an EMR cluster.
– Abhi
Nov 19 at 13:33
on jupyter notebook using python3 i get the desired output, but my original environment is 2.7 on an EMR cluster.
– Abhi
Nov 19 at 13:33
add a comment |
active
oldest
votes
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',
autoActivateHeartbeat: false,
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
});
}
});
draft saved
draft discarded
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%2f53373830%2fpandas-date-formatting%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
draft saved
draft discarded
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.
draft saved
draft discarded
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%2f53373830%2fpandas-date-formatting%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
1
Please provide example of expected output.
– user3471881
Nov 19 at 12:28
Hi, Thanks for the feedback have updated the question to show the expected output for one of the columns I am having issues with.
– Abhi
Nov 19 at 12:56
When I run your code without modifications it returns your expected output.
– user3471881
Nov 19 at 13:01
Strange, but I keep getting the same date format instead of the integer compatible one. Is there any other way of doing this?
– Abhi
Nov 19 at 13:24
on jupyter notebook using python3 i get the desired output, but my original environment is 2.7 on an EMR cluster.
– Abhi
Nov 19 at 13:33