to_json without header and index pandas-CodePudding

I have the following pandas DF

                                              data
day                                           2021-09-30
value1                                        730716000
value2                                        974689000
value3                                        375689000
value4                                        369077000

How can I convert this DF to json to be like:

{
    "day": "2021-09-30",
    "value1": 702228000,
    "value2": 924465000,
    "value3": 309753000,
    "value4": 306252
}

My best try was:

df.columns = range(df.shape[1])   # Delete headers
print(df.to_json(orient='index', indent=2))

But I got this output:

{
  "day":{
    "0":"2021-09-30"
  },
  "value1":{
    "0":"730716000"
  },
  "value2":{
    "0":"974689000"
  },
  "value3":{
    "0":"375689000"
  },
  "value4":{
    "0":"369077000"
  }
}

Bonus Doubt: Is it possible to parse only the values 1,2,3 and 4 of column data to int?

CodePudding user response：

There doesn't seem to be an orient value that produces what you want.

Use to_dict() to create a dictionary, then fix it up to what you want.

d = df.to_dict(orient = 'index')
for k, v in d.items():
    try:
        d[k] = int(v["0"])
    except ValueError:
        d[k] = v["0"]
print(json.dumps(d, indent=2))

The try/except will convert the values to integers when possible.

CodePudding user response：

The first approach is to squeeze your dataframe before use to_json

>>> print(df.squeeze().to_json(indent=4))
{
    "day":"2021-09-30",
    "value1":"730716000",
    "value2":"974689000",
    "value3":"375689000",
    "value4":"369077000"
}

For the bonus, use a different strategy

import json

data = json.dumps({k: int(v) if v.isdigit() else v
                      for k, v in df.squeeze().to_dict().items()}, indent=2)
print(data)

# Output
{
  "day": "2021-09-30",
  "value1": 730716000,
  "value2": 974689000,
  "value3": 375689000,
  "value4": 369077000
}

CodePudding user response：

You can select column "data" and use to_json with the default (orient='index'):

out = df['data'].to_json(indent=2)
print(out)

Output:

{
  "day":"2021-09-30",
  "value1":"730716000",
  "value2":"974689000",
  "value3":"375689000",
  "value4":"369077000"
}

For the bonus question, you can use to_dict dict comprehension:

out2 = {k: int(v) if k.startswith('value') else v for k,v in df['data'].to_dict().items()}

Output:

{'day': '2021-09-30',
 'value1': 730716000,
 'value2': 974689000,
 'value3': 375689000,
 'value4': 369077000}

CodePudding user response：

You can use to_dict with orient = records:

import pandas as pd
import json

df = pd.DataFrame({'day':['2021-09-30'], 'value1':[730716000]})
json.dumps(df.to_dict(orient='records')[0])

CodePudding user response：

import pandas as pd
import json 


json.dumps(df.to_dict()["data"])

dynamic way with out magic number or static strings

json.dumps(df.to_dict()[next(iter(df.columns))])

//output

{
"day": "2021-09-30",
"value1": 702228000,
"value2": 924465000,
"value3": 309753000,
"value4": 306252
}
``