Changes

The sql script get_data.sql from last year is copied to the current Crunchbase3 directory. However, two databases are very different now and adjustments are necessary. To keep track of the data type from each csv file used to copy to sql tablesthe SQL database, a file get_type.py is included in E:\projects\crunchbase3. This python script will print the first 5 rows of every data frame in the current directory.

Right now, a modification of load_crunchbase.sql is in both Z:/crunchbase3 and E:/projects/crunchbase3. Changes in dataset, datatype, and data columns have been made a lot compared to the previous version. The columns that are not yet added to the postgresql db are noted inside two lines of ################'s in the sql script. Since the data has changed will be changing a lot compared to last yearprevious years, using \i load_crunchbase.sql was might not very useful, and one may need to copy one table at a time by pasting the sql script into the terminal.

Files that All the dataset (17 of them) from the API have not yet been copied to the postgresql PostgreSQL server arein drive Z under /bulk/crunchbase3. To make date-time format in postgres work properly, all the empty string with quotes ("") in CSV files have been replaced by NULL with the command line degreessed 's/""//g' file.csv >file_clean.csv eventsThe script that I used to do that is in the file clean_data.sh in E:/projects/crunchbase3. A shorter script to do that for all the files in the directory is possible but might not be necessary and not all files require such edit.csv funding_round==Working with the database== All the scripts in load_crunchbase.sql have been updated.It now works perfectly with the current data (as of 03/29/2019) crawled from crunchbaseAPI and includes the correct number of rows copied from the csvfiles at the end of each \COPY command. To see and use the data in the postgres server: funds1) Connect to reseacher@199.188.177.215.csvA password is required ( ask Prof Egan for details) 2) Go to /bulk/crunchbase3 cd /bulk/crunchbase33) Connect to the database psql crunchbase3 \dt4) Perform regular SQL queries ==Incubators in Crunchbase==  \COPY (SELECT uuid, company_name, short_description FROM Organizations WHERE country_code='USA' AND short_description LIKE '%incubat%') TO investors 'CrunchbaseShortOrgDescsUSAIncubat.csvtxt' WITH DELIMITER AS E'\t' HEADER NULL AS CSV --466 ipos \COPY (SELECT A.uuid, A.company_name, B.description FROM Organizations AS A JOIN organization_descriptions AS B on A.uuid=B.csvuuid WHERE jobs country_code='USA' AND description LIKE '%incubat%') TO 'CrunchbaseLongOrgDescsUSAIncubat.csvtxt' WITH DELIMITER AS E'\t' HEADER NULL AS CSV --933 The two queries above were run against the Crunchbase database (see [[Ecosystem Organization Classifier]]), then their results were manually reviewed in two xlsx files (CrunchbaseLongOrgDescsUSAIncubat_IncubatorScore and CrunchbaseShortOrgDescsUSAIncubat_IncubatorScore), stored in E:\projects\crunchbase3  organizationsThese files were then combined into IncubatorsFromCrunchbase.xlsx providing they scored 1 in the Long file or were marked keep and did not score 0 (social impact or virtual) in the Short file.csvThe file has 564 (not necessarily unique) records and the following columns: uuid company_name description Score Notes Source  peopleRetrievingIncubators.sql was then modified to load this data, locate distinct UUIDs and output Organizational records. The resulting file is CrunchbaseIncubators.csvtxt (456 unique records, all USA), which has the following fields: company_name uuid address city state_code region status domain category_list short_description