Convert Pandas DataFrame to JSON format

Question

I have a Pandas DataFrame with two columns     one with the filename and one with the hour in which it was generated     File       Hour   F1         1   F1         2   F2         1   F3         1   I am trying to convert it to a JSON file with the following format     File   F1   Hour   1      File   F1   Hour   2     File   F2   Hour   1     File   F3   Hour   1     When I use the command DataFrame to json orient    records    I get the records in the below format      File   F1   Hour   1       File   F1   Hour   2       File   F2   Hour   1       File   F3   Hour   1      I m just wondering whether there is an option to get the JSON file in the desired format  Any help would be appreciated

User · Accepted Answer

The output that you get after DF to json is a string  So  you can simply slice it according to your requirement and remove the commas from it too   out   df to json orient  records   1 -1  replace                 To write the output to a text file  you could do   with open  file name txt    w   as f      f write out

User · Answer

convert data-frame to list of dictionary   list dict       for index  row in list df iterrows         list dict append dict row     save file   with open  output json   mode  as f      f write   n  join str item  for item in list dict

User · Answer

To transform a dataFrame in a real json  not a string  I use       from io import StringIO     import json     import DataFrame      buff StringIO        df is your DataFrame     df to json path or buf buff orient  records       dfJson json loads buff

User · Answer

Try this one   json dumps json loads df to json orient  quot records quot

User · Answer

instead of using dataframe to json orient      records      use dataframe to json orient      index     my above code convert the dataframe into json format of dict like  index -   column -  value

User · Answer

In newer versions of pandas  0 20 0   I believe   this can be done directly   df to json  temp json   orient  records   lines True    Direct compression is also possible   df to json  temp json gz   orient  records   lines True  compression  gzip

User · Answer

I think what the OP is looking for is   with open  temp json    w   as f      f write df to json orient  records   lines True     This should do the trick

User · Answer

Here is small utility class that converts JSON to DataFrame and back  Hope you find this helpful     - - coding  utf-8 - - from pandas io json import json normalize  class DFConverter        Converts the input JSON to a DataFrame     def convertToDF self dfJSON           return json normalize dfJSON         Converts the input DataFrame to JSON      def convertToJSON self  df           resultJSON   df to json orient  records           return resultJSON

User · Answer

use this formula to convert a pandas DataFrame to a list of dictionaries   import json json list   json loads json dumps list DataFrame T to dict   values

[json] Convert Pandas DataFrame to JSON format

Examples related to json

Examples related to pandas

Examples related to dataframe