0

My table is as bellowed

datetime    source  Day area    Town    County  Country
0   2019-01-01 16:22:46 1273    Tuesday Brighton    Brighton    East Sussex England
1   2019-01-02 09:33:29 1823    Wednesday   Taunton Taunton Somerset    England
2   2019-01-02 09:44:46 1977    Wednesday   Pontefract  Pontefract  West Yorkshire  England
3   2019-01-02 10:01:42 1983    Wednesday   Isle of Wight   NaN NaN NaN
4   2019-01-02 12:03:13 1304    Wednesday   Dover   Dover   Kent    England

My codes are

counts_by_counties = call_by_counties.groupby(['County','Town']).count()
counts_by_counties.head()

My grouped result (Do the column name disappeared?)

                            datetime    source  Day    area  Country
County            Town                  
Aberdeenshire   Aberdeen            8       8      8    8       8
                Banchory            1       1      1    1       1
                Blackburn          18      18     18   18      18
                Ellon               6       6      6    6       6
                Fraserburgh         2       2      2    2       2

I used this codes to rename the column, I am wondering if there is other efficent way to change the column name.

# slicing of  the table 
counts_by_counties = counts_by_counties[['datetime',]]
# rename by datetime into Counts
counts_by_counties.rename(columns={'datetime': 'Counts'})

Expected result

                            Counts
County            Town  
Aberdeenshire   Aberdeen    8
                Banchory    1
                Blackburn   18
James Huang
  • 63
  • 1
  • 10
  • Please show an example of what you want the final output to look like. – Chris Nov 20 '20 at 17:09
  • I have edited the example, thank you for reminders. – James Huang Nov 20 '20 at 17:12
  • 1
    maybe try `.size()` instead of `.count()` – Chris Nov 20 '20 at 17:12
  • Does this answer your question? [Get statistics for each group (such as count, mean, etc) using pandas GroupBy?](https://stackoverflow.com/questions/19384532/get-statistics-for-each-group-such-as-count-mean-etc-using-pandas-groupby) See [this answer](https://stackoverflow.com/a/32801170/3218693). – Bill Huang Nov 20 '20 at 17:20
  • @chris thanks but I would not expect the values of first column repeat many times since there are few data in first column. – James Huang Nov 20 '20 at 17:30
  • @BillHuang Thanks for this, I am looking for information about this. – James Huang Nov 20 '20 at 17:30

1 Answers1

1

Call reset_index as below. Replace

counts_by_counties = call_by_counties.groupby(['County','Town']).count()

with

counts_by_counties = call_by_counties.groupby(['County','Town']).count().reset_index()
Aaj Kaal
  • 1,205
  • 1
  • 9
  • 8