619
edits
Changes
Jump to navigation
Jump to search
Women in Entrepreneurship (Issue Brief) (view source)
Revision as of 18:13, 28 February 2017
, 18:13, 28 February 2017→TO DO FOR REAL
#Export!
Done! Here is the code for this part of project:
DROP TABLE titlelookup;
CREATE TABLE titlelookup(
fulltitle varchar(150),
charman int,
ceo int,
cfo int,
coo int,
cio int,
cto int,
otherclvl int,
boardmember int,
president int,
vp int,
founder int,
director int
);
\COPY titlelookup FROM 'Important Titles in Women2017 dataset.txt' WITH DELIMITER AS E'\t' HEADER NULL AS '' CSV
--628
DROP TABLE statelookup;
CREATE TABLE statelookup(
statename varchar(100),
uniquecode int
);
\COPY statelookup FROM 'uniquestateval.txt' WITH DELIMITER AS E'\t' HEADER NULL AS '' CSV
--50
DROP TABLE people;
CREATE TABLE people(
prefix varchar(5),
firstname varchar(50),
lastname varchar(50),
jobtitle varchar(150),
cname varchar(150)
);
\COPY people FROM 'pull5-normal.txt' WITH DELIMITER AS E'\t' HEADER NULL AS '' CSV
--186181
DROP TABLE cleancos;
CREATE TABLE cleancos AS
SELECT pull3info.*
FROM pull3info
JOIN Cleancosbase ON pull3info.companyname=cleancosbase.companyname AND minfirstdate=firstinvestdate;
--43534
DROP TABLE genvalpeople;
CREATE TABLE genvalpeople AS
SELECT *,
CASE WHEN prefix='Ms' THEN 1::int
WHEN prefix='Mrs' THEN 1::int
WHEN prefix='Mr' THEN 0::int
ELSE Null::int END AS genval FROM people;
--186181
DROP TABLE cleanpeople;
CREATE TABLE cleanpeople AS
SELECT genvalpeople.*, titlelookup.*
FROM genvalpeople LEFT JOIN titlelookup ON genvalpeople.jobtitle=fulltitle;
--186181
DROP TABLE uniquevalstate;
CREATE TABLE uniquevalstate AS
SELECT cleancos.*, statelookup.*
FROM cleancos LEFT JOIN statelookup ON cleancos.companystate=statelookup.statename;
--43534
DROP TABLE dataset;
CREATE TABLE dataset AS
SELECT cleanpeople.*, uniquevalstate.*
FROM cleanpeople LEFT JOIN uniquevalstate ON cleanpeople.cname=uniquevalstate.companyname;
--186181
\COPY people TO 'finaldataset.txt' WITH DELIMITER AS E'\t' HEADER NULL AS '' CSV
--186181
==Paper==
