Pandas groubby month and convert it json

Question

I have data frame object like as shown below,

   index                Date     Poly_1     Poly_2  Poly_2_WLS     Poly_3  
0      0 2017-01-04 08:45:00  70.195597  83.613845   83.613845  99.041125   
1      1 2017-01-04 08:53:00  70.195597  83.613845   83.613845  99.041125   
2      2 2017-01-04 09:00:00  70.195597  83.613845   83.613845  99.041125   
3      3 2017-12-13 08:45:00  70.195597  83.613845   83.613845  99.041125   
4      4 2017-12-13 08:53:00  70.195597  83.613845   83.613845  99.041125

I am grouping above data by month using code as follows,

dfgrp=df.groupby(pd.Grouper(key='Date',freq="M"),as_index=False)

Later I want to convert grouped data into json format using below code;

dfgrp.to_json(date_format='iso',orient='records')

however for some reason I am getting below error

AttributeError: Cannot access callable attribute 'to_json' of 'DataFrameGroupBy' objects, try using the 'apply' method

please let me know, how can I convert above data-frame into json?

EDIT:

I have used below line of code from answer below; and able generate JSON.

df.groupby([df.Date.dt.month)])['Poly_1','Poly_2','Poly_2_WLS','Poly_3'].sum().reset_index().to_json()

But JSON is generated in this format

`{"Date":{"0":1,"1":2,"2":3,"3":4,"4":5,"5":9,"6":10,"7":11,"8":12},"Poly_1":{"0":46187.2636499188,"1":56636.9594359758,"2":53218.6089763865,"3":41100.9574106447,"4":49317.907305443,"5":2670.6255284702,"6":34887.4415455112,"7":45857.8601621408,"8":21635.3343188418},"Poly_2":{"0":46193.719351124,"1":56193.0159455145,"2":52890.1916931438,"3":41119.1740551722,"4":49648.1531559606,"5":2767.3530477022,"6":34704.8815525262,"7":45918.9353954344,"8":22077.5341367508},"Poly_2_WLS":{"0":46193.719351124,"1":56193.0159455145,"2":52890.1916931438,"3":41119.1740551722,"4":49648.1531559606,"5":2767.3530477022,"6":34704.8815525262,"7":45918.9353954344,"8":22077.5341367508},"Poly_3":{"0":46037.6280724075,"1":56111.2211081627,"2":53059.8469394733,"3":41282.9093221716,"4":49670.016727901,"5":2660.8721082338,"6":34724.1756869611,"7":45721.7694774285,"8":22244.5188905397}`}

However, I want JSON just like below format;

Expected sample Json format:

{
    ,
    "Poly_1": {
        "Jan": 46187.2636499188,
        "Feb": 56636.9594359758,
        "Mar": 53218.6089763865,
        "Apr": 41100.9574106447,
        "May": 49317.907305443,
        "Jun": 2670.6255284702,
        "July": 34887.4415455112,
        "Aug": 45857.8601621408,
        "Sept": 21635.3343188418
    }


}

Please suggest how can I get above expected format format?

Thanks

do you need `df.groupby(df.Date.dt.month).sum().to_json(date_format='iso',orient='records')` ? if not how do you intend to aggregate the groups? — anky, Jul 04 '19 at 17:14
you need to call an aggregation function after your groupby like `sum` or `mean` — Dan, Jul 04 '19 at 19:27

adhg · Answer 1 · 2019-07-04T19:30:27.397

1

I assume in my answer that you wish to sum all polys (otherwise, simply change the code below).

I would group it this way to get July as (7):

df.groupby([df.Date.dt.month)])['Poly_1','Poly_2','Poly_2_WLS','Poly_3'].sum().reset_index().to_json()

note: I added sum, reset_index and only then to_json.

your grouped df would look like this:

    Date    Poly_1  Poly_2      Poly_2_WLS  Poly_3
0   7   350.977985  418.069225  418.069225  495.205625

your json would be (if you don't reset the index).

'{"Poly_1":{"7":350.977985},"Poly_2":{"7":418.069225},"Poly_2_WLS":{"7":418.069225},"Poly_3":{"7":495.205625}}'

Another pointer: if you go with the Grouper you will have 2019-07-31 and your json would look like this (1564531200000)

df.groupby([pd.Grouper(key='Date',freq="M")])['Poly_1','Poly_2','Poly_2_WLS','Poly_3'].sum().to_json()

'{"Poly_1":{"1564531200000":350.977985},"Poly_2":{"1564531200000":418.069225},"Poly_2_WLS":{"1564531200000":418.069225},"Poly_3":{"1564531200000":495.205625}}'

So adjust to your needs.

edited Jul 04 '19 at 19:30

answered Jul 04 '19 at 19:14

adhg

10,437
12
58
94

your answer helped, but I am getting different JSON format. {"Date":{"0":1,"1":2,"2":3,"3":4,"4":5,"5":9,"6":10,"7":11,"8":12},"Poly_1":{"0":46187.2636499188,"1":56636.9594359758,"2":53218.6089763865,"3":41100.9574106447,"4":49317.907305443,"5":2670.6255284702,"6":34887.4415455112,"7":45857.8601621408,"8":21635.3343188418}} I have updated question by adding sample data format, please have a look at it. – niran Jul 05 '19 at 13:50
@niran give me more input: (1)what python version are you using a (2) did you use reset_index (3) did you use Grouper? – adhg Jul 05 '19 at 17:14
(1) I am using py.2.7 (2) Yes I am have used reset_index, exactly your code. (3) No, I did not use Grouper.Just used groupby as mentioned in my edited question – niran Jul 05 '19 at 18:16
not sure if 2.7 is a must requirement for you. If it's not I strongly recommended you to move to 3.6. Beside the huge benefit, there would be no support for 2.7 as of the very near future. Regardless, my answer to you was based on 3.6 and if you wish to know why you get different values see this: https://stackoverflow.com/questions/51769239/why-json-dumps-in-python-3-return-a-different-value-of-python-2 – adhg Jul 05 '19 at 18:50
I cannot change version of python :) even by using loop is also fine! instead of to_json method – niran Jul 06 '19 at 03:42

Pandas groubby month and convert it json

1 Answers1