Mixing
Pandas Timegrouper on Dataframe using aggregate function count
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
1
I'm working on Timegrouper on Data Frame from Excel and trying to do a Pviot using Date as column header and Time as row and aggregate count on Y is "Barton LLC".
Data.xls
X Y Z D
740150 Barton LLC B1-20000 2014-01-01 02:21:51
740150 Barton LLC B1-50809 2014-01-01 02:21:51
740150 Barton LLC B1-53102 2014-01-01 02:21:51
740150 Barton LLC S2-16558 2014-01-02 21:21:01
740150 Barton LLC B1-86481 2014-01-02 21:21:01
740150 Curlis L S1-06532 2014-01-02 21:21:01
740150 Barton LLC S1-47412 2014-01-02 21:21:01
740150 Barton LLC B1-33364 2014-01-02 21:21:01
740150 Barton LLC S1-93683 2014-02-07 04:34:50
740150 Barton LLC S2-10342 2014-02-07 04:34:50
Tried using resample and pivot and timegrouper but got sequence of errors
import pandas as pd
import numpy as np
df = pd.read_excel("data.xlsx")
ndf = df[df['Type'].eq('df')].pivot_table(columns= ['Y'],values='Y',
index=pd.Grouper(key='D',freq='H'),aggfunc='count',fill_value=0)
Result
2014-01-01,2014-01-02,2014-02-07
02:21 3,NaN,NaN
21:21 NaN,4,NaN
04:34 NaN,NaN,2
python pandas
asked Jan 3 at 11:53
J.RAMJ.RAM
105
add a comment |
1
I'm working on Timegrouper on Data Frame from Excel and trying to do a Pviot using Date as column header and Time as row and aggregate count on Y is "Barton LLC".
Data.xls
X Y Z D
740150 Barton LLC B1-20000 2014-01-01 02:21:51
740150 Barton LLC B1-50809 2014-01-01 02:21:51
740150 Barton LLC B1-53102 2014-01-01 02:21:51
740150 Barton LLC S2-16558 2014-01-02 21:21:01
740150 Barton LLC B1-86481 2014-01-02 21:21:01
740150 Curlis L S1-06532 2014-01-02 21:21:01
740150 Barton LLC S1-47412 2014-01-02 21:21:01
740150 Barton LLC B1-33364 2014-01-02 21:21:01
740150 Barton LLC S1-93683 2014-02-07 04:34:50
740150 Barton LLC S2-10342 2014-02-07 04:34:50
Tried using resample and pivot and timegrouper but got sequence of errors
import pandas as pd
import numpy as np
df = pd.read_excel("data.xlsx")
ndf = df[df['Type'].eq('df')].pivot_table(columns= ['Y'],values='Y',
index=pd.Grouper(key='D',freq='H'),aggfunc='count',fill_value=0)
Result
2014-01-01,2014-01-02,2014-02-07
02:21 3,NaN,NaN
21:21 NaN,4,NaN
04:34 NaN,NaN,2
python pandas
asked Jan 3 at 11:53
J.RAMJ.RAM
105
1
what's the expected result?
– RomanPerekhrest
Jan 3 at 11:58
add a comment |
1
1
1
I'm working on Timegrouper on Data Frame from Excel and trying to do a Pviot using Date as column header and Time as row and aggregate count on Y is "Barton LLC".
Data.xls
X Y Z D
740150 Barton LLC B1-20000 2014-01-01 02:21:51
740150 Barton LLC B1-50809 2014-01-01 02:21:51
740150 Barton LLC B1-53102 2014-01-01 02:21:51
740150 Barton LLC S2-16558 2014-01-02 21:21:01
740150 Barton LLC B1-86481 2014-01-02 21:21:01
740150 Curlis L S1-06532 2014-01-02 21:21:01
740150 Barton LLC S1-47412 2014-01-02 21:21:01
740150 Barton LLC B1-33364 2014-01-02 21:21:01
740150 Barton LLC S1-93683 2014-02-07 04:34:50
740150 Barton LLC S2-10342 2014-02-07 04:34:50
Tried using resample and pivot and timegrouper but got sequence of errors
import pandas as pd
import numpy as np
df = pd.read_excel("data.xlsx")
ndf = df[df['Type'].eq('df')].pivot_table(columns= ['Y'],values='Y',
index=pd.Grouper(key='D',freq='H'),aggfunc='count',fill_value=0)
Result
2014-01-01,2014-01-02,2014-02-07
02:21 3,NaN,NaN
21:21 NaN,4,NaN
04:34 NaN,NaN,2
python pandas
asked Jan 3 at 11:53
J.RAMJ.RAM
105
I'm working on Timegrouper on Data Frame from Excel and trying to do a Pviot using Date as column header and Time as row and aggregate count on Y is "Barton LLC".
Data.xls
X Y Z D
740150 Barton LLC B1-20000 2014-01-01 02:21:51
740150 Barton LLC B1-50809 2014-01-01 02:21:51
740150 Barton LLC B1-53102 2014-01-01 02:21:51
740150 Barton LLC S2-16558 2014-01-02 21:21:01
740150 Barton LLC B1-86481 2014-01-02 21:21:01
740150 Curlis L S1-06532 2014-01-02 21:21:01
740150 Barton LLC S1-47412 2014-01-02 21:21:01
740150 Barton LLC B1-33364 2014-01-02 21:21:01
740150 Barton LLC S1-93683 2014-02-07 04:34:50
740150 Barton LLC S2-10342 2014-02-07 04:34:50
Tried using resample and pivot and timegrouper but got sequence of errors
import pandas as pd
import numpy as np
df = pd.read_excel("data.xlsx")
ndf = df[df['Type'].eq('df')].pivot_table(columns= ['Y'],values='Y',
index=pd.Grouper(key='D',freq='H'),aggfunc='count',fill_value=0)
Result
2014-01-01,2014-01-02,2014-02-07
02:21 3,NaN,NaN
21:21 NaN,4,NaN
04:34 NaN,NaN,2
python pandas
python pandas
asked Jan 3 at 11:53
J.RAMJ.RAM
105
asked Jan 3 at 11:53
J.RAMJ.RAM
105
asked Jan 3 at 11:53
J.RAMJ.RAM
105
asked Jan 3 at 11:53
J.RAMJ.RAM
105
asked Jan 3 at 11:53
J.RAMJ.RAM
105
105
1
what's the expected result?
– RomanPerekhrest
Jan 3 at 11:58
add a comment |
1
what's the expected result?
– RomanPerekhrest
Jan 3 at 11:58
1
1
what's the expected result?
– RomanPerekhrest
Jan 3 at 11:58
what's the expected result?
– RomanPerekhrest
Jan 3 at 11:58
add a comment |
2 Answers
2
active
oldest
votes
3
You could split the datetime column in date and time and use pivot_table:
df['date'] = df['D'].dt.date
df['time'] = df['D'].dt.time
pd.pivot_table(df, 'D', 'time', 'date', aggfunc='count')
date 2014-01-01 2014-01-02 2014-02-07
time
02:21:51 3.0 NaN NaN
04:34:50 NaN NaN 2.0
21:21:01 NaN 5.0 NaN
Note that you were missing one count for the date 2014-01-02 21:21:01
answered Jan 3 at 12:02
yatuyatu
15.6k41642
Tried but it was error.. ~/anaconda3/lib/python3.7/site-packages/pandas/core/frame.py in __getitem__(self, key) 2686 return self._getitem_multilevel(key) 2687 else: -> 2688 return self._getitem_column(key) 2689 2690 def _getitem_column(self, key): pandas._libs.hashtable.PyObjectHashTable.get_item() pandas/_libs/hashtable_class_helper.pxi in pandas._libs.hashtable.PyObjectHashTable.get_item() KeyError: 'D'
– J.RAM
Jan 3 at 12:45
addingdf.D = pd.to_datetime(df.D)in the beginning?
– yatu
Jan 3 at 12:45
@ yatu - How to use only HH:MM discarding Secs .
– J.RAM
Jan 3 at 13:38
Change the second line withdf['time'] = df['D'].dt.strftime('%H:%M')`
– yatu
Jan 3 at 13:42
@ yatu , yes its working can we use cumcount in pivot like this pd.pivot_table(df, 'D', 'time', 'date', aggfunc='cumcount')
– J.RAM
Jan 3 at 14:03
add a comment |
1
Use crosstab with strftime for convert datetimes to custom strings:
df.D = pd.to_datetime(df.D)
ndf = pd.crosstab(df['D'].dt.strftime('%H:%M').rename('H'), df['D'].dt.strftime('%Y-%m-%d'))
print (ndf)
D 2014-01-01 2014-01-02 2014-02-07
H
02:21 3 0 0
04:34 0 0 2
21:21 0 5 0
ndf = pd.crosstab(df['D'].dt.time.rename('T'), df['D'].dt.date)
print (ndf)
D 2014-01-01 2014-01-02 2014-02-07
T
02:21:51 3 0 0
04:34:50 0 0 2
21:21:01 0 5 0
answered Jan 3 at 12:08
jezraeljezrael
358k26323402
no there is error when tried running .. ~/anaconda3/lib/python3.7/site-packages/pandas/core/frame.py in __getitem__(self, key) 2686 return self._getitem_multilevel(key) 2687 else: -> 2688 return self._getitem_column(key) 2689 2690 def _getitem_column(self, key): pandas/_libs/index.pyx in pandas._libs.index.IndexEngine.get_loc()
– J.RAM
Jan 3 at 12:47
@J.RAM - What isprint (df.columns)?
– jezrael
Jan 3 at 12:48
print (df.columns) Index(['X', 'Y', 'Z', 'D'], dtype='object')
– J.RAM
Jan 3 at 13:19
@J.RAM - Andprint (df.dtypes)?
– jezrael
Jan 3 at 13:21
1
it works after adding df.D = pd.to_datetime(df.D) df['date'] = df['D'].dt.date df['time'] = df['D'].dt.time
– J.RAM
Jan 3 at 13:24
|
show 2 more comments
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%2f54021773%2fpandas-timegrouper-on-dataframe-using-aggregate-function-count%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
3
You could split the datetime column in date and time and use pivot_table:
df['date'] = df['D'].dt.date
df['time'] = df['D'].dt.time
pd.pivot_table(df, 'D', 'time', 'date', aggfunc='count')
date 2014-01-01 2014-01-02 2014-02-07
time
02:21:51 3.0 NaN NaN
04:34:50 NaN NaN 2.0
21:21:01 NaN 5.0 NaN
Note that you were missing one count for the date 2014-01-02 21:21:01
answered Jan 3 at 12:02
yatuyatu
15.6k41642
Tried but it was error.. ~/anaconda3/lib/python3.7/site-packages/pandas/core/frame.py in __getitem__(self, key) 2686 return self._getitem_multilevel(key) 2687 else: -> 2688 return self._getitem_column(key) 2689 2690 def _getitem_column(self, key): pandas._libs.hashtable.PyObjectHashTable.get_item() pandas/_libs/hashtable_class_helper.pxi in pandas._libs.hashtable.PyObjectHashTable.get_item() KeyError: 'D'
– J.RAM
Jan 3 at 12:45
addingdf.D = pd.to_datetime(df.D)in the beginning?
– yatu
Jan 3 at 12:45
@ yatu - How to use only HH:MM discarding Secs .
– J.RAM
Jan 3 at 13:38
Change the second line withdf['time'] = df['D'].dt.strftime('%H:%M')`
– yatu
Jan 3 at 13:42
@ yatu , yes its working can we use cumcount in pivot like this pd.pivot_table(df, 'D', 'time', 'date', aggfunc='cumcount')
– J.RAM
Jan 3 at 14:03
add a comment |
3
You could split the datetime column in date and time and use pivot_table:
df['date'] = df['D'].dt.date
df['time'] = df['D'].dt.time
pd.pivot_table(df, 'D', 'time', 'date', aggfunc='count')
date 2014-01-01 2014-01-02 2014-02-07
time
02:21:51 3.0 NaN NaN
04:34:50 NaN NaN 2.0
21:21:01 NaN 5.0 NaN
Note that you were missing one count for the date 2014-01-02 21:21:01
answered Jan 3 at 12:02
yatuyatu
15.6k41642
Tried but it was error.. ~/anaconda3/lib/python3.7/site-packages/pandas/core/frame.py in __getitem__(self, key) 2686 return self._getitem_multilevel(key) 2687 else: -> 2688 return self._getitem_column(key) 2689 2690 def _getitem_column(self, key): pandas._libs.hashtable.PyObjectHashTable.get_item() pandas/_libs/hashtable_class_helper.pxi in pandas._libs.hashtable.PyObjectHashTable.get_item() KeyError: 'D'
– J.RAM
Jan 3 at 12:45
addingdf.D = pd.to_datetime(df.D)in the beginning?
– yatu
Jan 3 at 12:45
@ yatu - How to use only HH:MM discarding Secs .
– J.RAM
Jan 3 at 13:38
Change the second line withdf['time'] = df['D'].dt.strftime('%H:%M')`
– yatu
Jan 3 at 13:42
@ yatu , yes its working can we use cumcount in pivot like this pd.pivot_table(df, 'D', 'time', 'date', aggfunc='cumcount')
– J.RAM
Jan 3 at 14:03
add a comment |
3
3
3
You could split the datetime column in date and time and use pivot_table:
df['date'] = df['D'].dt.date
df['time'] = df['D'].dt.time
pd.pivot_table(df, 'D', 'time', 'date', aggfunc='count')
date 2014-01-01 2014-01-02 2014-02-07
time
02:21:51 3.0 NaN NaN
04:34:50 NaN NaN 2.0
21:21:01 NaN 5.0 NaN
Note that you were missing one count for the date 2014-01-02 21:21:01
answered Jan 3 at 12:02
yatuyatu
15.6k41642
You could split the datetime column in date and time and use pivot_table:
df['date'] = df['D'].dt.date
df['time'] = df['D'].dt.time
pd.pivot_table(df, 'D', 'time', 'date', aggfunc='count')
date 2014-01-01 2014-01-02 2014-02-07
time
02:21:51 3.0 NaN NaN
04:34:50 NaN NaN 2.0
21:21:01 NaN 5.0 NaN
Note that you were missing one count for the date 2014-01-02 21:21:01
answered Jan 3 at 12:02
yatuyatu
15.6k41642
answered Jan 3 at 12:02
yatuyatu
15.6k41642
answered Jan 3 at 12:02
yatuyatu
15.6k41642
answered Jan 3 at 12:02
yatuyatu
15.6k41642
15.6k41642
Tried but it was error.. ~/anaconda3/lib/python3.7/site-packages/pandas/core/frame.py in __getitem__(self, key) 2686 return self._getitem_multilevel(key) 2687 else: -> 2688 return self._getitem_column(key) 2689 2690 def _getitem_column(self, key): pandas._libs.hashtable.PyObjectHashTable.get_item() pandas/_libs/hashtable_class_helper.pxi in pandas._libs.hashtable.PyObjectHashTable.get_item() KeyError: 'D'
– J.RAM
Jan 3 at 12:45
addingdf.D = pd.to_datetime(df.D)in the beginning?
– yatu
Jan 3 at 12:45
@ yatu - How to use only HH:MM discarding Secs .
– J.RAM
Jan 3 at 13:38
Change the second line withdf['time'] = df['D'].dt.strftime('%H:%M')`
– yatu
Jan 3 at 13:42
@ yatu , yes its working can we use cumcount in pivot like this pd.pivot_table(df, 'D', 'time', 'date', aggfunc='cumcount')
– J.RAM
Jan 3 at 14:03
add a comment |
Tried but it was error.. ~/anaconda3/lib/python3.7/site-packages/pandas/core/frame.py in __getitem__(self, key) 2686 return self._getitem_multilevel(key) 2687 else: -> 2688 return self._getitem_column(key) 2689 2690 def _getitem_column(self, key): pandas._libs.hashtable.PyObjectHashTable.get_item() pandas/_libs/hashtable_class_helper.pxi in pandas._libs.hashtable.PyObjectHashTable.get_item() KeyError: 'D'
– J.RAM
Jan 3 at 12:45
addingdf.D = pd.to_datetime(df.D)in the beginning?
– yatu
Jan 3 at 12:45
@ yatu - How to use only HH:MM discarding Secs .
– J.RAM
Jan 3 at 13:38
Change the second line withdf['time'] = df['D'].dt.strftime('%H:%M')`
– yatu
Jan 3 at 13:42
@ yatu , yes its working can we use cumcount in pivot like this pd.pivot_table(df, 'D', 'time', 'date', aggfunc='cumcount')
– J.RAM
Jan 3 at 14:03
Tried but it was error.. ~/anaconda3/lib/python3.7/site-packages/pandas/core/frame.py in __getitem__(self, key) 2686 return self._getitem_multilevel(key) 2687 else: -> 2688 return self._getitem_column(key) 2689 2690 def _getitem_column(self, key): pandas._libs.hashtable.PyObjectHashTable.get_item() pandas/_libs/hashtable_class_helper.pxi in pandas._libs.hashtable.PyObjectHashTable.get_item() KeyError: 'D'
– J.RAM
Jan 3 at 12:45
Tried but it was error.. ~/anaconda3/lib/python3.7/site-packages/pandas/core/frame.py in __getitem__(self, key) 2686 return self._getitem_multilevel(key) 2687 else: -> 2688 return self._getitem_column(key) 2689 2690 def _getitem_column(self, key): pandas._libs.hashtable.PyObjectHashTable.get_item() pandas/_libs/hashtable_class_helper.pxi in pandas._libs.hashtable.PyObjectHashTable.get_item() KeyError: 'D'
– J.RAM
Jan 3 at 12:45
adding
df.D = pd.to_datetime(df.D) in the beginning?– yatu
Jan 3 at 12:45
adding
df.D = pd.to_datetime(df.D) in the beginning?– yatu
Jan 3 at 12:45
@ yatu - How to use only HH:MM discarding Secs .
– J.RAM
Jan 3 at 13:38
@ yatu - How to use only HH:MM discarding Secs .
– J.RAM
Jan 3 at 13:38
Change the second line with
df['time'] = df['D'].dt.strftime('%H:%M') `– yatu
Jan 3 at 13:42
Change the second line with
df['time'] = df['D'].dt.strftime('%H:%M') `– yatu
Jan 3 at 13:42
@ yatu , yes its working can we use cumcount in pivot like this pd.pivot_table(df, 'D', 'time', 'date', aggfunc='cumcount')
– J.RAM
Jan 3 at 14:03
@ yatu , yes its working can we use cumcount in pivot like this pd.pivot_table(df, 'D', 'time', 'date', aggfunc='cumcount')
– J.RAM
Jan 3 at 14:03
add a comment |
1
Use crosstab with strftime for convert datetimes to custom strings:
df.D = pd.to_datetime(df.D)
ndf = pd.crosstab(df['D'].dt.strftime('%H:%M').rename('H'), df['D'].dt.strftime('%Y-%m-%d'))
print (ndf)
D 2014-01-01 2014-01-02 2014-02-07
H
02:21 3 0 0
04:34 0 0 2
21:21 0 5 0
ndf = pd.crosstab(df['D'].dt.time.rename('T'), df['D'].dt.date)
print (ndf)
D 2014-01-01 2014-01-02 2014-02-07
T
02:21:51 3 0 0
04:34:50 0 0 2
21:21:01 0 5 0
answered Jan 3 at 12:08
jezraeljezrael
358k26323402
no there is error when tried running .. ~/anaconda3/lib/python3.7/site-packages/pandas/core/frame.py in __getitem__(self, key) 2686 return self._getitem_multilevel(key) 2687 else: -> 2688 return self._getitem_column(key) 2689 2690 def _getitem_column(self, key): pandas/_libs/index.pyx in pandas._libs.index.IndexEngine.get_loc()
– J.RAM
Jan 3 at 12:47
@J.RAM - What isprint (df.columns)?
– jezrael
Jan 3 at 12:48
print (df.columns) Index(['X', 'Y', 'Z', 'D'], dtype='object')
– J.RAM
Jan 3 at 13:19
@J.RAM - Andprint (df.dtypes)?
– jezrael
Jan 3 at 13:21
1
it works after adding df.D = pd.to_datetime(df.D) df['date'] = df['D'].dt.date df['time'] = df['D'].dt.time
– J.RAM
Jan 3 at 13:24
|
show 2 more comments
1
Use crosstab with strftime for convert datetimes to custom strings:
df.D = pd.to_datetime(df.D)
ndf = pd.crosstab(df['D'].dt.strftime('%H:%M').rename('H'), df['D'].dt.strftime('%Y-%m-%d'))
print (ndf)
D 2014-01-01 2014-01-02 2014-02-07
H
02:21 3 0 0
04:34 0 0 2
21:21 0 5 0
ndf = pd.crosstab(df['D'].dt.time.rename('T'), df['D'].dt.date)
print (ndf)
D 2014-01-01 2014-01-02 2014-02-07
T
02:21:51 3 0 0
04:34:50 0 0 2
21:21:01 0 5 0
answered Jan 3 at 12:08
jezraeljezrael
358k26323402
no there is error when tried running .. ~/anaconda3/lib/python3.7/site-packages/pandas/core/frame.py in __getitem__(self, key) 2686 return self._getitem_multilevel(key) 2687 else: -> 2688 return self._getitem_column(key) 2689 2690 def _getitem_column(self, key): pandas/_libs/index.pyx in pandas._libs.index.IndexEngine.get_loc()
– J.RAM
Jan 3 at 12:47
@J.RAM - What isprint (df.columns)?
– jezrael
Jan 3 at 12:48
print (df.columns) Index(['X', 'Y', 'Z', 'D'], dtype='object')
– J.RAM
Jan 3 at 13:19
@J.RAM - Andprint (df.dtypes)?
– jezrael
Jan 3 at 13:21
1
it works after adding df.D = pd.to_datetime(df.D) df['date'] = df['D'].dt.date df['time'] = df['D'].dt.time
– J.RAM
Jan 3 at 13:24
|
show 2 more comments
1
1
1
Use crosstab with strftime for convert datetimes to custom strings:
df.D = pd.to_datetime(df.D)
ndf = pd.crosstab(df['D'].dt.strftime('%H:%M').rename('H'), df['D'].dt.strftime('%Y-%m-%d'))
print (ndf)
D 2014-01-01 2014-01-02 2014-02-07
H
02:21 3 0 0
04:34 0 0 2
21:21 0 5 0
ndf = pd.crosstab(df['D'].dt.time.rename('T'), df['D'].dt.date)
print (ndf)
D 2014-01-01 2014-01-02 2014-02-07
T
02:21:51 3 0 0
04:34:50 0 0 2
21:21:01 0 5 0
answered Jan 3 at 12:08
jezraeljezrael
358k26323402
Use crosstab with strftime for convert datetimes to custom strings:
df.D = pd.to_datetime(df.D)
ndf = pd.crosstab(df['D'].dt.strftime('%H:%M').rename('H'), df['D'].dt.strftime('%Y-%m-%d'))
print (ndf)
D 2014-01-01 2014-01-02 2014-02-07
H
02:21 3 0 0
04:34 0 0 2
21:21 0 5 0
ndf = pd.crosstab(df['D'].dt.time.rename('T'), df['D'].dt.date)
print (ndf)
D 2014-01-01 2014-01-02 2014-02-07
T
02:21:51 3 0 0
04:34:50 0 0 2
21:21:01 0 5 0
answered Jan 3 at 12:08
jezraeljezrael
358k26323402
edited Jan 3 at 13:24
answered Jan 3 at 12:08
jezraeljezrael
358k26323402
answered Jan 3 at 12:08
jezraeljezrael
358k26323402
answered Jan 3 at 12:08
jezraeljezrael
358k26323402
358k26323402
no there is error when tried running .. ~/anaconda3/lib/python3.7/site-packages/pandas/core/frame.py in __getitem__(self, key) 2686 return self._getitem_multilevel(key) 2687 else: -> 2688 return self._getitem_column(key) 2689 2690 def _getitem_column(self, key): pandas/_libs/index.pyx in pandas._libs.index.IndexEngine.get_loc()
– J.RAM
Jan 3 at 12:47
@J.RAM - What isprint (df.columns)?
– jezrael
Jan 3 at 12:48
print (df.columns) Index(['X', 'Y', 'Z', 'D'], dtype='object')
– J.RAM
Jan 3 at 13:19
@J.RAM - Andprint (df.dtypes)?
– jezrael
Jan 3 at 13:21
1
it works after adding df.D = pd.to_datetime(df.D) df['date'] = df['D'].dt.date df['time'] = df['D'].dt.time
– J.RAM
Jan 3 at 13:24
|
show 2 more comments
no there is error when tried running .. ~/anaconda3/lib/python3.7/site-packages/pandas/core/frame.py in __getitem__(self, key) 2686 return self._getitem_multilevel(key) 2687 else: -> 2688 return self._getitem_column(key) 2689 2690 def _getitem_column(self, key): pandas/_libs/index.pyx in pandas._libs.index.IndexEngine.get_loc()
– J.RAM
Jan 3 at 12:47
@J.RAM - What isprint (df.columns)?
– jezrael
Jan 3 at 12:48
print (df.columns) Index(['X', 'Y', 'Z', 'D'], dtype='object')
– J.RAM
Jan 3 at 13:19
@J.RAM - Andprint (df.dtypes)?
– jezrael
Jan 3 at 13:21
1
it works after adding df.D = pd.to_datetime(df.D) df['date'] = df['D'].dt.date df['time'] = df['D'].dt.time
– J.RAM
Jan 3 at 13:24
no there is error when tried running .. ~/anaconda3/lib/python3.7/site-packages/pandas/core/frame.py in __getitem__(self, key) 2686 return self._getitem_multilevel(key) 2687 else: -> 2688 return self._getitem_column(key) 2689 2690 def _getitem_column(self, key): pandas/_libs/index.pyx in pandas._libs.index.IndexEngine.get_loc()
– J.RAM
Jan 3 at 12:47
no there is error when tried running .. ~/anaconda3/lib/python3.7/site-packages/pandas/core/frame.py in __getitem__(self, key) 2686 return self._getitem_multilevel(key) 2687 else: -> 2688 return self._getitem_column(key) 2689 2690 def _getitem_column(self, key): pandas/_libs/index.pyx in pandas._libs.index.IndexEngine.get_loc()
– J.RAM
Jan 3 at 12:47
@J.RAM - What is
print (df.columns) ?– jezrael
Jan 3 at 12:48
@J.RAM - What is
print (df.columns) ?– jezrael
Jan 3 at 12:48
print (df.columns) Index(['X', 'Y', 'Z', 'D'], dtype='object')
– J.RAM
Jan 3 at 13:19
print (df.columns) Index(['X', 'Y', 'Z', 'D'], dtype='object')
– J.RAM
Jan 3 at 13:19
@J.RAM - And
print (df.dtypes) ?– jezrael
Jan 3 at 13:21
@J.RAM - And
print (df.dtypes) ?– jezrael
Jan 3 at 13:21
1
1
it works after adding df.D = pd.to_datetime(df.D) df['date'] = df['D'].dt.date df['time'] = df['D'].dt.time
– J.RAM
Jan 3 at 13:24
it works after adding df.D = pd.to_datetime(df.D) df['date'] = df['D'].dt.date df['time'] = df['D'].dt.time
– J.RAM
Jan 3 at 13:24
|
show 2 more comments
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.
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%2f54021773%2fpandas-timegrouper-on-dataframe-using-aggregate-function-count%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
what's the expected result?
– RomanPerekhrest
Jan 3 at 11:58