Getting a JSON from a stacked pandas dataframe

Question

I have the sample dataframe below:

d = {'key': ['foo', 'foo', 'foo', 'foo', 'bar', 'bar', 'bar', 'bar', 'crow', 'crow', 'crow', 'crow'], 
     'count': [12, 3, 5, 5, 3, 1, 4, 1, 7, 3, 8, 2],
     'text': ["hello", "i", "am", "a", "piece", "of", "text", "have", "a", "nice", "day", "friends"],
}

}
df = pd.DataFrame(data=d)
df

output:

    key count   text
0   foo    12   hello
1   foo     3   i
2   foo     5   am
3   foo     5   a
4   bar     3   piece
5   bar     1   of
6   bar     4   text
7   bar     1   have
8   crow    7   a
9   crow    3   nice
10  crow    8   day
11  crow    2   friends

I stacked the dataframe with: df.set_index("key").stack()

To get:

key        
foo   count         12
      text       hello
      count          3
      text           i
      count          5
      text          am
      count          5
      text           a
bar   count          3
      text       piece
      count          1
      text          of
      count          4
      text        text
      count          1
      text        have
crow  count          7
      text           a
      count          3
      text        nice
      count          8
      text         day
      count          2
      text     friends
dtype: object

I am now trying to output the stacked df as a JSON file, but when I use to_json(), I get the error:

ValueError: Series index must be unique for orient='index'

The expect output would text and count grouped by the key:

[
  {
    "key": "19",
    "values": [
        {
            text: 'hello',
            count: 12
        },
        {
            content: 'i',
            count: 3
        },
        {
            content: 'am',
            count: 5
        },
        ...
    ]
]

Apologies, I modified my post to include "some_key" as "values" — monopoly
– monopoly, Commented Jan 4, 2021 at 18:01

Quang Hoang · Accepted Answer · 2021-01-04 17:58:53Z

3

As mentioned in the comment, your expected output is not a valid JSON string. You need "some_key":[...] at the same level with "key":"bar".

For example groupby:

json_str = json.dumps([ {'key':k, 'values':d.to_dict('records')}
                       for k,d in df.drop('key',axis=1).groupby(df['key'])
                      ], indent=2)

Output:

[
  {
    "key": "bar",
    "values": [
      {
        "count": 3,
        "text": "piece"
      },
      {
        "count": 1,
        "text": "of"
      },
      {
        "count": 4,
        "text": "text"
      },
      {
        "count": 1,
        "text": "have"
      }
    ]
  },
  {
    "key": "crow",
    "values": [
      {
        "count": 7,
        "text": "a"
      },
      {
        "count": 3,
        "text": "nice"
      },
      {
        "count": 8,
        "text": "day"
      },
      {
        "count": 2,
        "text": "friends"
      }
    ]
  },
  {
    "key": "foo",
    "values": [
      {
        "count": 12,
        "text": "hello"
      },
      {
        "count": 3,
        "text": "i"
      },
      {
        "count": 5,
        "text": "am"
      },
      {
        "count": 5,
        "text": "a"
      }
    ]
  }
]

answered Jan 4, 2021 at 17:58

Quang Hoang

151k11 gold badges64 silver badges86 bronze badges

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

2 Comments

monopoly Over a year ago

This worked perfectly. Question: why did you drop the key column in the for loop?

Quang Hoang Over a year ago

@mehsheenman otherwise the key will still present in each record inside values.

Collectives™ on Stack Overflow

Getting a JSON from a stacked pandas dataframe

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related