Mixing
Changing Column Label format from datetime to date
I have been trying to import data from an excel file where multiple column labels are dates. The excel file headers look somewhat like this
Name 2018-08-01 2018-08-02 Amount
After importing it to a dataframe I got the column labels-
Name 2018-08-01 00:00:00 2018-08-02 00:00:00 Amount
So I wanted to remove the hours from the headings. As the dates update automatically in the excel file so I can not replace the labels using '
df['2018-08-01 00:00:00']` I have tried renaming the columns using following code
df1.columns[3] = pd.to_datetime(df1.columns[3], format='%Y-%m-%d')
However this gives the following error:
TypeError: Index does not support mutable operations.
I have also tried
df1.rename(df1.columns[3][lambda t: t.strftime('%Y-%M-%d')])
Which gives the following error
TypeError: 'datetime.datetime' object is not subscriptable
I am very new to Python and dataframe and I am still in the very basic stage. How do I get this done?
Solved
As I already know the column index I converted them from datetime object to
stringand then sliced them. I tried iteration but it was not working so this is the only way I could do it.
Edate=str(df1.columns[1])
Edatep=slice(0,10,1)
Gdate=str(df1.columns[2])
Gdatep=slice(0,10,1)
df1.columns=['Name',Edate[Edatep],Gdate[Gdatep],'Amount']
python pandas datetime dataframe label
edited Jan 1 at 15:05
Cœur
18.7k9110150
asked Aug 30 '18 at 9:12
T0167T0167
408
add a comment |
I have been trying to import data from an excel file where multiple column labels are dates. The excel file headers look somewhat like this
Name 2018-08-01 2018-08-02 Amount
After importing it to a dataframe I got the column labels-
Name 2018-08-01 00:00:00 2018-08-02 00:00:00 Amount
So I wanted to remove the hours from the headings. As the dates update automatically in the excel file so I can not replace the labels using '
df['2018-08-01 00:00:00']` I have tried renaming the columns using following code
df1.columns[3] = pd.to_datetime(df1.columns[3], format='%Y-%m-%d')
However this gives the following error:
TypeError: Index does not support mutable operations.
I have also tried
df1.rename(df1.columns[3][lambda t: t.strftime('%Y-%M-%d')])
Which gives the following error
TypeError: 'datetime.datetime' object is not subscriptable
I am very new to Python and dataframe and I am still in the very basic stage. How do I get this done?
Solved
As I already know the column index I converted them from datetime object to
stringand then sliced them. I tried iteration but it was not working so this is the only way I could do it.
Edate=str(df1.columns[1])
Edatep=slice(0,10,1)
Gdate=str(df1.columns[2])
Gdatep=slice(0,10,1)
df1.columns=['Name',Edate[Edatep],Gdate[Gdatep],'Amount']
python pandas datetime dataframe label
edited Jan 1 at 15:05
Cœur
18.7k9110150
asked Aug 30 '18 at 9:12
T0167T0167
408
add a comment |
I have been trying to import data from an excel file where multiple column labels are dates. The excel file headers look somewhat like this
Name 2018-08-01 2018-08-02 Amount
After importing it to a dataframe I got the column labels-
Name 2018-08-01 00:00:00 2018-08-02 00:00:00 Amount
So I wanted to remove the hours from the headings. As the dates update automatically in the excel file so I can not replace the labels using '
df['2018-08-01 00:00:00']` I have tried renaming the columns using following code
df1.columns[3] = pd.to_datetime(df1.columns[3], format='%Y-%m-%d')
However this gives the following error:
TypeError: Index does not support mutable operations.
I have also tried
df1.rename(df1.columns[3][lambda t: t.strftime('%Y-%M-%d')])
Which gives the following error
TypeError: 'datetime.datetime' object is not subscriptable
I am very new to Python and dataframe and I am still in the very basic stage. How do I get this done?
Solved
As I already know the column index I converted them from datetime object to
stringand then sliced them. I tried iteration but it was not working so this is the only way I could do it.
Edate=str(df1.columns[1])
Edatep=slice(0,10,1)
Gdate=str(df1.columns[2])
Gdatep=slice(0,10,1)
df1.columns=['Name',Edate[Edatep],Gdate[Gdatep],'Amount']
python pandas datetime dataframe label
edited Jan 1 at 15:05
Cœur
18.7k9110150
asked Aug 30 '18 at 9:12
T0167T0167
408
I have been trying to import data from an excel file where multiple column labels are dates. The excel file headers look somewhat like this
Name 2018-08-01 2018-08-02 Amount
After importing it to a dataframe I got the column labels-
Name 2018-08-01 00:00:00 2018-08-02 00:00:00 Amount
So I wanted to remove the hours from the headings. As the dates update automatically in the excel file so I can not replace the labels using '
df['2018-08-01 00:00:00']` I have tried renaming the columns using following code
df1.columns[3] = pd.to_datetime(df1.columns[3], format='%Y-%m-%d')
However this gives the following error:
TypeError: Index does not support mutable operations.
I have also tried
df1.rename(df1.columns[3][lambda t: t.strftime('%Y-%M-%d')])
Which gives the following error
TypeError: 'datetime.datetime' object is not subscriptable
I am very new to Python and dataframe and I am still in the very basic stage. How do I get this done?
Solved
As I already know the column index I converted them from datetime object to
stringand then sliced them. I tried iteration but it was not working so this is the only way I could do it.
Edate=str(df1.columns[1])
Edatep=slice(0,10,1)
Gdate=str(df1.columns[2])
Gdatep=slice(0,10,1)
df1.columns=['Name',Edate[Edatep],Gdate[Gdatep],'Amount']
python pandas datetime dataframe label
python pandas datetime dataframe label
edited Jan 1 at 15:05
Cœur
18.7k9110150
asked Aug 30 '18 at 9:12
T0167T0167
408
edited Jan 1 at 15:05
Cœur
18.7k9110150
asked Aug 30 '18 at 9:12
T0167T0167
408
edited Jan 1 at 15:05
Cœur
18.7k9110150
edited Jan 1 at 15:05
Cœur
18.7k9110150
edited Jan 1 at 15:05
Cœur
18.7k9110150
18.7k9110150
asked Aug 30 '18 at 9:12
T0167T0167
408
asked Aug 30 '18 at 9:12
T0167T0167
408
asked Aug 30 '18 at 9:12
T0167T0167
408
408
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
Indexes in pandas are immutable - your best bet would be to make a new index.
I think here list comprehension working nice with converting only timestamps with if-else:
df1 = pd.DataFrame(columns=['Name', pd.Timestamp('2018-08-01'),
pd.Timestamp('2018-08-02'), 'Amount'])
print (df1)
Empty DataFrame
Columns: [Name, 2018-08-01 00:00:00, 2018-08-02 00:00:00, Amount]
Index:
print (df1.columns.map(type))
Index([ <class 'str'>,
<class 'pandas._libs.tslibs.timestamps.Timestamp'>,
<class 'pandas._libs.tslibs.timestamps.Timestamp'>,
<class 'str'>],
dtype='object')
df1.columns=[x.strftime('%Y-%M-%d') if isinstance(x,pd.Timestamp) else x for x in df1.columns]
print (df1)
Empty DataFrame
Columns: [Name, 2018-00-01, 2018-00-02, Amount]
Index:
Another solutions if input data are strings:
df1.columns = df1.columns.astype(str)
df1.columns = df1.columns.astype(str).str.replace(' 00:00:00', '')
print (df1)
Empty DataFrame
Columns: [Name, 2018-08-01, 2018-08-02, Amount]
Index:
cols = pd.to_datetime(df1.columns, errors='coerce')
df1.columns = df1.columns.where(cols.isnull(), cols.strftime('%Y-%M-%d'))
print (df1)
Empty DataFrame
Columns: [Name, 2018-00-01, 2018-00-02, Amount]
Index:
answered Aug 30 '18 at 9:19
jezraeljezrael
345k25300371
I can not do that as the dates would change every day, so I would need to set the Column names every day.
– T0167
Aug 30 '18 at 10:35
@Tasnuva - Not understand, if usedf1.columns = [x.strftime('%Y-%M-%d') if isinstance(x, pd.Timestamp) else x for x in df1.columns]it working dynamically witch changed datetimes too.
– jezrael
Aug 30 '18 at 10:37
Sorry, I donot think I am getting this. I tried it however it is not working. I still get the hours. Empty Dataframe, Columns, Index -are these correct syntax? cause if I use it it gives syntax error.
– T0167
Aug 30 '18 at 10:53
@Tasnuva - ya, sometimes if copy from So get syntact error, because bad copied some''. Try rewrite solution only.
– jezrael
Aug 30 '18 at 10:54
@Tasnuva - So new solution still not working?
– jezrael
Aug 30 '18 at 12:07
|
show 1 more 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',
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
});
}
});
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%2f52093271%2fchanging-column-label-format-from-datetime-to-date%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Indexes in pandas are immutable - your best bet would be to make a new index.
I think here list comprehension working nice with converting only timestamps with if-else:
df1 = pd.DataFrame(columns=['Name', pd.Timestamp('2018-08-01'),
pd.Timestamp('2018-08-02'), 'Amount'])
print (df1)
Empty DataFrame
Columns: [Name, 2018-08-01 00:00:00, 2018-08-02 00:00:00, Amount]
Index:
print (df1.columns.map(type))
Index([ <class 'str'>,
<class 'pandas._libs.tslibs.timestamps.Timestamp'>,
<class 'pandas._libs.tslibs.timestamps.Timestamp'>,
<class 'str'>],
dtype='object')
df1.columns=[x.strftime('%Y-%M-%d') if isinstance(x,pd.Timestamp) else x for x in df1.columns]
print (df1)
Empty DataFrame
Columns: [Name, 2018-00-01, 2018-00-02, Amount]
Index:
Another solutions if input data are strings:
df1.columns = df1.columns.astype(str)
df1.columns = df1.columns.astype(str).str.replace(' 00:00:00', '')
print (df1)
Empty DataFrame
Columns: [Name, 2018-08-01, 2018-08-02, Amount]
Index:
cols = pd.to_datetime(df1.columns, errors='coerce')
df1.columns = df1.columns.where(cols.isnull(), cols.strftime('%Y-%M-%d'))
print (df1)
Empty DataFrame
Columns: [Name, 2018-00-01, 2018-00-02, Amount]
Index:
answered Aug 30 '18 at 9:19
jezraeljezrael
345k25300371
I can not do that as the dates would change every day, so I would need to set the Column names every day.
– T0167
Aug 30 '18 at 10:35
@Tasnuva - Not understand, if usedf1.columns = [x.strftime('%Y-%M-%d') if isinstance(x, pd.Timestamp) else x for x in df1.columns]it working dynamically witch changed datetimes too.
– jezrael
Aug 30 '18 at 10:37
Sorry, I donot think I am getting this. I tried it however it is not working. I still get the hours. Empty Dataframe, Columns, Index -are these correct syntax? cause if I use it it gives syntax error.
– T0167
Aug 30 '18 at 10:53
@Tasnuva - ya, sometimes if copy from So get syntact error, because bad copied some''. Try rewrite solution only.
– jezrael
Aug 30 '18 at 10:54
@Tasnuva - So new solution still not working?
– jezrael
Aug 30 '18 at 12:07
|
show 1 more comment
Indexes in pandas are immutable - your best bet would be to make a new index.
I think here list comprehension working nice with converting only timestamps with if-else:
df1 = pd.DataFrame(columns=['Name', pd.Timestamp('2018-08-01'),
pd.Timestamp('2018-08-02'), 'Amount'])
print (df1)
Empty DataFrame
Columns: [Name, 2018-08-01 00:00:00, 2018-08-02 00:00:00, Amount]
Index:
print (df1.columns.map(type))
Index([ <class 'str'>,
<class 'pandas._libs.tslibs.timestamps.Timestamp'>,
<class 'pandas._libs.tslibs.timestamps.Timestamp'>,
<class 'str'>],
dtype='object')
df1.columns=[x.strftime('%Y-%M-%d') if isinstance(x,pd.Timestamp) else x for x in df1.columns]
print (df1)
Empty DataFrame
Columns: [Name, 2018-00-01, 2018-00-02, Amount]
Index:
Another solutions if input data are strings:
df1.columns = df1.columns.astype(str)
df1.columns = df1.columns.astype(str).str.replace(' 00:00:00', '')
print (df1)
Empty DataFrame
Columns: [Name, 2018-08-01, 2018-08-02, Amount]
Index:
cols = pd.to_datetime(df1.columns, errors='coerce')
df1.columns = df1.columns.where(cols.isnull(), cols.strftime('%Y-%M-%d'))
print (df1)
Empty DataFrame
Columns: [Name, 2018-00-01, 2018-00-02, Amount]
Index:
answered Aug 30 '18 at 9:19
jezraeljezrael
345k25300371
I can not do that as the dates would change every day, so I would need to set the Column names every day.
– T0167
Aug 30 '18 at 10:35
@Tasnuva - Not understand, if usedf1.columns = [x.strftime('%Y-%M-%d') if isinstance(x, pd.Timestamp) else x for x in df1.columns]it working dynamically witch changed datetimes too.
– jezrael
Aug 30 '18 at 10:37
Sorry, I donot think I am getting this. I tried it however it is not working. I still get the hours. Empty Dataframe, Columns, Index -are these correct syntax? cause if I use it it gives syntax error.
– T0167
Aug 30 '18 at 10:53
@Tasnuva - ya, sometimes if copy from So get syntact error, because bad copied some''. Try rewrite solution only.
– jezrael
Aug 30 '18 at 10:54
@Tasnuva - So new solution still not working?
– jezrael
Aug 30 '18 at 12:07
|
show 1 more comment
Indexes in pandas are immutable - your best bet would be to make a new index.
I think here list comprehension working nice with converting only timestamps with if-else:
df1 = pd.DataFrame(columns=['Name', pd.Timestamp('2018-08-01'),
pd.Timestamp('2018-08-02'), 'Amount'])
print (df1)
Empty DataFrame
Columns: [Name, 2018-08-01 00:00:00, 2018-08-02 00:00:00, Amount]
Index:
print (df1.columns.map(type))
Index([ <class 'str'>,
<class 'pandas._libs.tslibs.timestamps.Timestamp'>,
<class 'pandas._libs.tslibs.timestamps.Timestamp'>,
<class 'str'>],
dtype='object')
df1.columns=[x.strftime('%Y-%M-%d') if isinstance(x,pd.Timestamp) else x for x in df1.columns]
print (df1)
Empty DataFrame
Columns: [Name, 2018-00-01, 2018-00-02, Amount]
Index:
Another solutions if input data are strings:
df1.columns = df1.columns.astype(str)
df1.columns = df1.columns.astype(str).str.replace(' 00:00:00', '')
print (df1)
Empty DataFrame
Columns: [Name, 2018-08-01, 2018-08-02, Amount]
Index:
cols = pd.to_datetime(df1.columns, errors='coerce')
df1.columns = df1.columns.where(cols.isnull(), cols.strftime('%Y-%M-%d'))
print (df1)
Empty DataFrame
Columns: [Name, 2018-00-01, 2018-00-02, Amount]
Index:
answered Aug 30 '18 at 9:19
jezraeljezrael
345k25300371
Indexes in pandas are immutable - your best bet would be to make a new index.
I think here list comprehension working nice with converting only timestamps with if-else:
df1 = pd.DataFrame(columns=['Name', pd.Timestamp('2018-08-01'),
pd.Timestamp('2018-08-02'), 'Amount'])
print (df1)
Empty DataFrame
Columns: [Name, 2018-08-01 00:00:00, 2018-08-02 00:00:00, Amount]
Index:
print (df1.columns.map(type))
Index([ <class 'str'>,
<class 'pandas._libs.tslibs.timestamps.Timestamp'>,
<class 'pandas._libs.tslibs.timestamps.Timestamp'>,
<class 'str'>],
dtype='object')
df1.columns=[x.strftime('%Y-%M-%d') if isinstance(x,pd.Timestamp) else x for x in df1.columns]
print (df1)
Empty DataFrame
Columns: [Name, 2018-00-01, 2018-00-02, Amount]
Index:
Another solutions if input data are strings:
df1.columns = df1.columns.astype(str)
df1.columns = df1.columns.astype(str).str.replace(' 00:00:00', '')
print (df1)
Empty DataFrame
Columns: [Name, 2018-08-01, 2018-08-02, Amount]
Index:
cols = pd.to_datetime(df1.columns, errors='coerce')
df1.columns = df1.columns.where(cols.isnull(), cols.strftime('%Y-%M-%d'))
print (df1)
Empty DataFrame
Columns: [Name, 2018-00-01, 2018-00-02, Amount]
Index:
answered Aug 30 '18 at 9:19
jezraeljezrael
345k25300371
edited Aug 30 '18 at 12:21
answered Aug 30 '18 at 9:19
jezraeljezrael
345k25300371
answered Aug 30 '18 at 9:19
jezraeljezrael
345k25300371
answered Aug 30 '18 at 9:19
jezraeljezrael
345k25300371
345k25300371
I can not do that as the dates would change every day, so I would need to set the Column names every day.
– T0167
Aug 30 '18 at 10:35
@Tasnuva - Not understand, if usedf1.columns = [x.strftime('%Y-%M-%d') if isinstance(x, pd.Timestamp) else x for x in df1.columns]it working dynamically witch changed datetimes too.
– jezrael
Aug 30 '18 at 10:37
Sorry, I donot think I am getting this. I tried it however it is not working. I still get the hours. Empty Dataframe, Columns, Index -are these correct syntax? cause if I use it it gives syntax error.
– T0167
Aug 30 '18 at 10:53
@Tasnuva - ya, sometimes if copy from So get syntact error, because bad copied some''. Try rewrite solution only.
– jezrael
Aug 30 '18 at 10:54
@Tasnuva - So new solution still not working?
– jezrael
Aug 30 '18 at 12:07
|
show 1 more comment
I can not do that as the dates would change every day, so I would need to set the Column names every day.
– T0167
Aug 30 '18 at 10:35
@Tasnuva - Not understand, if usedf1.columns = [x.strftime('%Y-%M-%d') if isinstance(x, pd.Timestamp) else x for x in df1.columns]it working dynamically witch changed datetimes too.
– jezrael
Aug 30 '18 at 10:37
Sorry, I donot think I am getting this. I tried it however it is not working. I still get the hours. Empty Dataframe, Columns, Index -are these correct syntax? cause if I use it it gives syntax error.
– T0167
Aug 30 '18 at 10:53
@Tasnuva - ya, sometimes if copy from So get syntact error, because bad copied some''. Try rewrite solution only.
– jezrael
Aug 30 '18 at 10:54
@Tasnuva - So new solution still not working?
– jezrael
Aug 30 '18 at 12:07
I can not do that as the dates would change every day, so I would need to set the Column names every day.
– T0167
Aug 30 '18 at 10:35
I can not do that as the dates would change every day, so I would need to set the Column names every day.
– T0167
Aug 30 '18 at 10:35
@Tasnuva - Not understand, if use
df1.columns = [x.strftime('%Y-%M-%d') if isinstance(x, pd.Timestamp) else x for x in df1.columns] it working dynamically witch changed datetimes too.– jezrael
Aug 30 '18 at 10:37
@Tasnuva - Not understand, if use
df1.columns = [x.strftime('%Y-%M-%d') if isinstance(x, pd.Timestamp) else x for x in df1.columns] it working dynamically witch changed datetimes too.– jezrael
Aug 30 '18 at 10:37
Sorry, I donot think I am getting this. I tried it however it is not working. I still get the hours. Empty Dataframe, Columns, Index -are these correct syntax? cause if I use it it gives syntax error.
– T0167
Aug 30 '18 at 10:53
Sorry, I donot think I am getting this. I tried it however it is not working. I still get the hours. Empty Dataframe, Columns, Index -are these correct syntax? cause if I use it it gives syntax error.
– T0167
Aug 30 '18 at 10:53
@Tasnuva - ya, sometimes if copy from So get syntact error, because bad copied some
''. Try rewrite solution only.– jezrael
Aug 30 '18 at 10:54
@Tasnuva - ya, sometimes if copy from So get syntact error, because bad copied some
''. Try rewrite solution only.– jezrael
Aug 30 '18 at 10:54
@Tasnuva - So new solution still not working?
– jezrael
Aug 30 '18 at 12:07
@Tasnuva - So new solution still not working?
– jezrael
Aug 30 '18 at 12:07
|
show 1 more 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.
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%2f52093271%2fchanging-column-label-format-from-datetime-to-date%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
