Pandas: Write dataframe to json with split

Question

I need write data to json. Problem is that I can't establish delimiter to string. My df looks like

     id       date  val
0   123 2015-12-12    1
1   123 2015-12-13    1
2   123 2015-12-14    0
3   123 2015-12-15    1
4   123 2015-12-16    1
5   123 2015-12-17    0
6   123 2015-12-18    1
7   456 2015-12-12    1
8   456 2015-12-13    1
9   456 2015-12-14    0
10  456 2015-12-15    1

I use

df.groupby('id').apply(lambda x: x.set_index('date')['val'].to_dict()).to_json('nielsen', orient='index')

and I want to get smth like

{
"1234567890abcdef1234567890abcdef": {
    "2016-06": 1, 
    "2016-05": 0, 
    "2016-04": 0, 
    "2016-03": 1, 
    "2016-02": 1, 
    "2016-01": 0
}, 
"0987654321abcdef1234567890abcdef": {
    "2016-06": 1, 
    "2016-05": 1, 
    "2016-04": 1, 
    "2016-03": 0, 
    "2016-02": 0, 
    "2016-01": 0
}
}

How can I do that?

jvd10 · Accepted Answer · 2016-07-13 16:04:21Z

2

You could have to_json write to a StringIO object and then use json loads/dumps to format to your liking:

import pandas as pd
import StringIO, json
df = pd.read_csv('data.csv')
nielson = StringIO.StringIO()
df.groupby('id').apply(lambda x: x.set_index('date')['val'].to_dict()).to_json(nielson, orient='index')
print(json.dumps(json.loads(nielson.getvalue()),indent=2))

This produces:

{
  "123": {
    "2015-12-14": 0, 
    "2015-12-15": 1, 
    "2015-12-16": 1, 
    "2015-12-17": 0, 
    "2015-12-12": 1, 
    "2015-12-13": 1, 
    "2015-12-18": 1
  }, 
  "456": {
    "2015-12-14": 0, 
    "2015-12-15": 1, 
    "2015-12-12": 1, 
    "2015-12-13": 1
  }
}

See help(json.dumps) for other formatting options. See help(json.dump) for details on how to write to a file (basic example shown below):

with open('nielsen','w') as f:
    json.dump(json.loads(nielson.getvalue()), f, indent=2)

edited Jul 13, 2016 at 16:04

answered Jul 13, 2016 at 14:41

jvd10

1,89619 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

NineWasps Over a year ago

But it's only with printing. How can I change it in file?

jvd10 Over a year ago

edited answer to illustrate writing to a file using json.dump

NineWasps Over a year ago

can you say, is it real to write that in order? it write '2014-09', '2013-12'but I need conversely

jvd10 Over a year ago

json objects are unordered, but you can pass sort_keys=True to json.dump to have them sorted.

Collectives™ on Stack Overflow

Pandas: Write dataframe to json with split

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related