Crunchbase Database - Revision history

Ed at 17:46, 21 September 2020

2020-09-21T17:46:27Z

Ed at 17:34, 21 September 2020

2020-09-21T17:34:27Z

Ed at 16:29, 20 September 2020

2020-09-20T16:29:10Z

Ed: /* Incubators in Crunchbase */

2019-10-02T21:13:00Z

Incubators in Crunchbase

Ed at 21:12, 2 October 2019

2019-10-02T21:12:32Z

Hiep at 17:51, 29 March 2019

2019-03-29T17:51:20Z

Hiep at 17:47, 29 March 2019

2019-03-29T17:47:19Z

Hiep at 17:43, 29 March 2019

2019-03-29T17:43:31Z

Hiep at 17:43, 29 March 2019

2019-03-29T17:43:09Z

Hiep at 20:30, 28 March 2019

2019-03-28T20:30:01Z

@@ Line 1: / Line 1: @@
 {{Project
-|Has project output=
+|Has project output=Data,Tool,How-to
 |Has sponsor=Kauffman Incubator Project
 |Has title=Crunchbase Database

@@ Line 1: / Line 1: @@
 {{Project
 |Has sponsor=Kauffman Incubator Project
 |Has title=Crunchbase Database

@@ Line 1: / Line 1: @@
 {{Project
 |Has title=Crunchbase Database
 |Has owner=Hiep Nguyen

@@ Line 98: / Line 98: @@
   uuid	company_name	description	Score	Notes	Source
-RetrievingIncubators.sql was then modified to load this data, locate distinct UUIDs and output Organizational records. The resulting file is CrunchbaseIncubators.txt (456 records, all USA), which has the following fields:
+RetrievingIncubators.sql was then modified to load this data, locate distinct UUIDs and output Organizational records. The resulting file is CrunchbaseIncubators.txt (456 unique records, all USA), which has the following fields:
   company_name	uuid	address	city	state_code	region	status	domain	category_list	short_description

← Older revision		Revision as of 21:12, 2 October 2019
Line 82:		Line 82:
	\dt		\dt
	4) Perform regular SQL queries		4) 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
		+	'CrunchbaseShortOrgDescsUSAIncubat.txt' WITH DELIMITER AS E'\t' HEADER NULL AS CSV
		+	--466
		+
		+	\COPY (SELECT A.uuid, A.company_name, B.description FROM Organizations AS A JOIN organization_descriptions AS B on A.uuid=B.uuid WHERE
		+	country_code='USA' AND description LIKE '%incubat%') TO 'CrunchbaseLongOrgDescsUSAIncubat.txt' 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
		+
		+	These 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. The file has 564 (not necessarily unique) records and the following columns:
		+	uuid company_name description Score Notes Source
		+
		+	RetrievingIncubators.sql was then modified to load this data, locate distinct UUIDs and output Organizational records. The resulting file is CrunchbaseIncubators.txt (456 records, all USA), which has the following fields:
		+	company_name uuid address city state_code region status domain category_list short_description

@@ Line 57: / Line 57: @@
   data\people_descriptions.csv
-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 tables, 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.
+To keep track of the data type from each csv file used to copy to the 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.
 All the crunchbase3 data from drive E are now also in drive Z:/crunchbase3
@@ Line 64: / Line 64: @@
 Since the data will be changing a lot compared to previous years, using \i load_crunchbase.sql might not very useful, and one may need to copy one table at a time by pasting the sql script into the terminal.
-All the dataset (17 of them) from the API has been copied to the PostgreSQL server in drive Z under /bulk/crunchbase3. To make date-time format in postgres works properly, all the empty string with quotes ("") in CSV files have been replaced by NULL with the command line
+All the dataset (17 of them) from the API have been copied to the PostgreSQL server in 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
    sed 's/""//g' file.csv >file_clean.csv
 The 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.

@@ Line 63: / Line 63: @@
 Since the data will be changing a lot compared to previous years, using \i load_crunchbase.sql might not very useful, and one may need to copy one table at a time by pasting the sql script into the terminal.
 All the dataset (17 of them) from the API has been copied to the PostgreSQL server in drive Z under /bulk/crunchbase3. To make date-time format in postgres works properly, all the empty string with quotes ("") in CSV files have been replaced by NULL with the command line
@@ Line 70: / Line 68: @@
 The 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.
-All the scripts in load_crunchbase.sql have been updated. It now works perfectly with the current data crawled from crunchbaseAPI and includes the correct number of rows copied from the csv files at the end of each \COPY command.
+==Working with the database==
 To see and use the data in the postgres server: