From fairmans at physik.hu-berlin.de Mon Sep 2 17:03:43 2024 From: fairmans at physik.hu-berlin.de (Sam Fairman) Date: Mon, 2 Sep 2024 16:03:43 +0200 Subject: [Cod-bugs] Information for PhD thesis In-Reply-To: <24021c4c-d173-4333-9570-8e0d2a7b282d@physik.hu-berlin.de> References: <24021c4c-d173-4333-9570-8e0d2a7b282d@physik.hu-berlin.de> Message-ID: <545c4751-feb3-4440-b8aa-7495d33b0fe8@physik.hu-berlin.de> Hello, I would like to get some information for my PhD defense. I would like to show plots of the number of structures added to the database by year, and also compare numbers of structures by year solved by electron diffraction vs XRD. Would you be able to provide me with that information? I tried to download the whole database to get the numbers myself but it's too large for my PC. -- Best regards, Sam Fairman PhD Student Humboldt Universit?t zu Berlin Department of Physics Structure Research & Electron Microscopy group Room 3?305 Newtonstra?e 15 12489 Berlin, Germany +49 30 20937866 -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. From antanas.vaitkus90 at gmail.com Tue Sep 3 14:42:34 2024 From: antanas.vaitkus90 at gmail.com (Antanas Vaitkus) Date: Tue, 3 Sep 2024 14:42:34 +0300 Subject: [Cod-bugs] Information for PhD thesis In-Reply-To: <545c4751-feb3-4440-b8aa-7495d33b0fe8@physik.hu-berlin.de> References: <24021c4c-d173-4333-9570-8e0d2a7b282d@physik.hu-berlin.de> <545c4751-feb3-4440-b8aa-7495d33b0fe8@physik.hu-berlin.de> Message-ID: Hello, Sam, The COD allows to retrieve core pieces of information (unit cell constants, determination method, bibliography, etc.) by directly querying the MySQL database [1]. The following queries might be useful to you: 1. Get the number of entries deposited in the COD each year: mysql -u cod_reader -h www.crystallography.net cod -e \ 'SELECT year, count(*) FROM data GROUP BY year ORDER BY year DESC'; Note, that there are ~500 COD entries with the NULL year. These entries are either prepublication entries that are still to be publicly released or historic entries with incomplete information. 2. Get information on entries solved using electron diffraction*: mysql -u cod_reader -h www.crystallography.net cod -e \ 'SELECT file, year, radiation, radType, onhold FROM data WHERE radiation LIKE "%electron%" OR \ radType LIKE "%electron%" order by year desc, file desc;' Note, however, that this query includes all entries where the radiation type is declared as the probing particle (including transmission electron microscope and similar techniques), therefore you might need to further refine the query (e.g. filter on the publication title) or even do some manual filtering based on your criteria. Currently, there are only 217 such entries in the COD so this should still be feasible. For entries without a year, the 'onhold' field can be used to approximate the deposition date since it corresponds to the planned public release date of the entries. As for comparing the number of structures solved via by electron diffraction vs XRD, you can more or less reasonably assume that all entries that were not solved via electron diffraction or neutron diffraction were solved by XRD (single crystal, powder, etc.). Then the per-year XRD entry deposition can be retrieved by slightly modifying the first query: mysql -u cod_reader -h www.crystallography.net cod -e \ 'SELECT year, count(*) FROM data \ WHERE radiation is NULL OR \ ( \ radiation NOT LIKE "%electron%" AND \ radiation NOT LIKE "%neutron%" \ ) AND \ radType is NULL OR \ ( \ radType NOT LIKE "%electron%" AND \ radType NOT LIKE "%neutron%" \ ) \ GROUP BY year ORDER BY year DESC'; I attach files with the query results that were run today (2024-09-03) for your convenience. [1] https://wiki.crystallography.net/howtoquerycod/ Hope this helps and please let us know if you have any further questions. Sincerely Antanas On Tue, 3 Sept 2024 at 09:18, Sam Fairman wrote: > Hello, > > I would like to get some information for my PhD defense. I would like to > show plots of the number of structures added to the database by year, > and also compare numbers of structures by year solved by electron > diffraction vs XRD. Would you be able to provide me with that > information? I tried to download the whole database to get the numbers > myself but it's too large for my PC. > > -- > Best regards, > Sam Fairman > PhD Student > Humboldt Universit?t zu Berlin > Department of Physics > Structure Research & Electron Microscopy group > Room 3?305 > Newtonstra?e 15 > 12489 Berlin, Germany > +49 30 20937866 > > > -- > This message has been scanned for viruses and > dangerous content by MailScanner, and is > believed to be clean. > > _______________________________________________ > Cod-bugs mailing list > Cod-bugs at lists.crystallography.net > http://lists.crystallography.net/cgi-bin/mailman/listinfo/cod-bugs > -- Antanas Vaitkus, Vilnius University, Life Sciences Center, Institute of Biotechnology, room C521, Saul?tekio al. 7, LT-10257 Vilnius, Lithuania -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -------------- next part -------------- An HTML attachment was scrubbed... URL: -------------- next part -------------- A non-text attachment was scrubbed... Name: cod-growth-by-year_2024-09-03.tsv Type: text/tab-separated-values Size: 1039 bytes Desc: not available URL: -------------- next part -------------- A non-text attachment was scrubbed... Name: cod-electron-probe_2024-09-03.tsv Type: text/tab-separated-values Size: 7845 bytes Desc: not available URL: -------------- next part -------------- A non-text attachment was scrubbed... Name: cod-growth-by-year-XRD_2024-09-03.tsv Type: text/tab-separated-values Size: 1039 bytes Desc: not available URL: