Regarding ORD data conversion to a suitable format

[sherylcp7289-tech]

I have downloaded the ORD data from the repository, using git lfs as mentioned. I am trying to figure out the best way to convert the individual .pb.gz to json or csv and then extract the data I need. While I was successful converting to json, I found that the ORDerly open-source framework can provide the extracted and cleaned data but it is not working for me when I process the .pb.gz files one by one. I'd like to know if there is any way to resolve this, or if there is another way to get the specific data from the json files. I need to go process all 546 datasets available in the ORD, as I require both USPTO and non-USPTO data, yet I cannot do this all at once due to hardware limitations.

[ctapobep]

This creates a dataset_pb2.Dataset:

    with open(path, 'rb') as f:
        return dataset_pb2.Dataset.FromString(gzip.decompress(f.read()))

And then you can navigate reactions from the dataset. I have an example in this repo. It goes over known datasets (defined in datasets.txt), downloads them from the ord-data repository, parses them and submits the data to another service.

Notice the dependencies in pyproject.toml:

dependencies = ["ord-schema", "setuptools", "urllib3>=2.0.2", "psycopg2-binary==2.9.10"]

[sherylcp7289-tech]

@ctapobep thank you so much for your reply. I would like to try the example in the repo you have provided, but the link is not opening.

[ctapobep]

@sherylcp7289-tech oops, made the repo public now.

[bdeadman]

Thanks for your question. I'm putting some notes below for now, but I'm happy to discuss any of these ideas further if it helps you.

Extract to csv

Use messages_to_dataframe from ord-schema message_helpers to turn a single ORD dataset into a Pandas DataFrame. You could then write the DataFrame to csv file. This example Jupyter Notebook shows how to download a dataset and convert it into a DataFrame with the optional parameter to drop any constant columns.

This works well on single datasets, or closely related datasets, but it isn't recommended for trying to work across multiple datasets. The challenge is that the csv files become very very wide to accommodate all the available features in the ORD schema.

Extract to json

Use write_message from ord-schema message_helpers, and specify an output filename ending in '.json'.

Working with the whole ORD database

ORDerly is a good option for working with the whole database, but it sounds like it isn't doing what you need it to. ORDerly was developed by another group so you could try raising your issue there if you want some help to process individual files.

Depending on what data fields you require from ORD, you may have more success using the ORD Object Relational Mapper to convert all the datasets into a Postgres database. You would then use an SQL query to build the table of extracted data that you are interested in. The query will have a lot of table joins due to the complexity of the schema. Let me know if you are interested in trying this approach as I may be able to help get you started. I also have some example SQL queries that I could share.

[sherylcp7289-tech]

@bdeadman Thank you for your response...I did look into ORDerly and contacted them, however I came to know that ORDerly was primarily developed to clean USPTO data and USPTO data may not be very good for reaction yield prediction, which is the problem I'm focusing on. Since USPTO data may not be suitable for my application, I am considering non-USPTO data available in the ORD, specifically the Pfizer dataset. I shall try to extract the relevant datasets to csv using messages_to_dataframe from ord-schema message_helpers which you have mentioned. Thank you once again.