5 ms·

How did you compute this? I just did another check to verify (wc -l) and it's coming to 15,980,531.

by mfrye0 3y ago

rahimnathwani 3y ago
wc -l will count all newlines, even those that are escaped. Perhaps some company descriptions have newlines?
simonw 3y ago
I used wc -l at first, but I've just imported into SQLite and the count(*) is 15,263,246 - updated my previous comment (which had said 15,263,251). I downloaded the CSV and ran: sqlite-utils insert companies.db company companies-dataset-2023-02-ckgENv.csv --csv sqlite-utils enable-fts companies.db company name specialties sqlite-utils analyze-tables --save companies.db This lets me run searches against the name and specialties columns, and gives me those aggregate stats too.
- mfrye0 3y ago
  Ok. I'm not sure how this happened, but I think the dataset was somehow mislabeled. It appears that this dataset is the Q1 version, not the latest Q2. Can you please try re-downloading it? We're probably going to have to make an public announcement about this...
  - simonw 3y ago
    OK, that one has 15,948,996 rows. Here's what I got from running the same "sqlite-utils analyze-tables companies2.db company" command against it: company.handle: (1/10) Total rows: 15948996 Null rows: 0 Blank rows: 0 Distinct values: 15948996 company.type: (2/10) Total rows: 15948996 Null rows: 0 Blank rows: 5253878 Distinct values: 92 Most common: 5311279: Privately Held 5253878: 1290064: Self-Owned 1055857: Partnership 987045: Public Company 828643: Self-Employed 799655: Nonprofit 334552: Educational 87681: Government Agency 35: De financiación privada company.name: (3/10) Total rows: 15948996 Null rows: 0 Blank rows: 1591 Distinct values: 15549439 Most common: 1591: 1098: . 277: A 246: - 164: None 155: X 142: N/A 132: ... 128: x 122: 1 company.website: (4/10) Total rows: 15948996 Null rows: 0 Blank rows: 3249552 Distinct values: 11272926 Most common: 3249552: 86957: facebook.com 57769: instagram.com 46404: business.site 31397: linktr.ee 27882: indiamart.com 21852: wixsite.com 19008: negocio.site 17366: linkedin.com 13224: yelp.com company.founded: (5/10) Total rows: 15948996 Null rows: 0 Blank rows: 8040264 Distinct values: 1561 Most common: 8040264: 524236: 2020 451742: 2017 441748: 2018 426575: 2019 418318: 2021 411391: 2016 389487: 2015 339212: 2014 299038: 2013 company.industry: (6/10) Total rows: 15948996 Null rows: 0 Blank rows: 1334901 Distinct values: 421 Most common: 1334901: 820156: IT Services and IT Consulting 651746: Construction 651557: Advertising Services 465857: Software Development 455111: Business Consulting and Services 447922: Real Estate 435151: Retail 355049: Financial Services 312937: Wellness and Fitness Services company.size: (7/10) Total rows: 15948996 Null rows: 0 Blank rows: 2655086 Distinct values: 123 Most common: 6646929: 2-10 3584483: 11-50 2655086: 1197530: 51-200 1091094: 1 employee 421595: 201-500 150053: 501-1,000 129373: 1,001-5,000 44755: 10,001+ 27742: 5,001-10,000 company.city: (8/10) Total rows: 15948996 Null rows: 0 Blank rows: 3155391 Distinct values: 410985 Most common: 3155391: 269708: London 124059: Paris 113135: New York 99314: São Paulo 75428: Los Angeles 69691: Madrid 67328: Toronto 63738: Dubai 63456: New Delhi company.state: (9/10) Total rows: 15948996 Null rows: 0 Blank rows: 4524015 Distinct values: 58563 Most common: 4524015: 691167: England 584141: California 329584: Texas 291639: New York 286723: Florida 222552: São Paulo 185925: Maharashtra 171885: Ontario 171657: Île-de-France company.country_code: (10/10) Total rows: 15948996 Null rows: 0 Blank rows: 2961064 Distinct values: 272 Most common: 4059985: US 2961064: 1232403: GB 885302: IN 756411: FR 664235: BR 467501: DE 414433: NL 410372: ES 389535: CA
    - simonw 3y ago
      The thing I find most interesting is this: 269708: London 124059: Paris 113135: New York 99314: São Paulo 75428: Los Angeles 69691: Madrid 67328: Toronto 63738: Dubai 63456: New Delhi I would not have expected São Paulo to come fourth in this list, after New York but in front of Los Angeles. I just learned it's the 4th largest city in the world https://en.wikipedia.org/wiki/List_of_largest_cities https://en.wikipedia.org/wiki/List_of_largest_cities - after Tokyo, Delhi, Shanghai - but I guess it has much more of a representation on LinkedIn than those other cities.

How did you compute this? I just did another check to verify (wc -l) and it's coming to 15,980,531.

by mfrye0 3y ago

How did you compute this? I just did another check to verify (wc -l) and it's coming to 15,980,531.

rahimnathwani 3y ago

wc -l will count all newlines, even those that are escaped. Perhaps some company descriptions have newlines?

simonw 3y ago

I used wc -l at first, but I've just imported into SQLite and the count(*) is 15,263,246 - updated my previous comment (which had said 15,263,251). I downloaded the CSV and ran: sqlite-utils insert companies.db company companies-dataset-2023-02-ckgENv.csv --csv sqlite-utils enable-fts companies.db company name specialties sqlite-utils analyze-tables --save companies.db This lets me run searches against the name and specialties columns, and gives me those aggregate stats too.

mfrye0 3y ago
Ok. I'm not sure how this happened, but I think the dataset was somehow mislabeled. It appears that this dataset is the Q1 version, not the latest Q2. Can you please try re-downloading it? We're probably going to have to make an public announcement about this...
- simonw 3y ago
  OK, that one has 15,948,996 rows. Here's what I got from running the same "sqlite-utils analyze-tables companies2.db company" command against it: company.handle: (1/10) Total rows: 15948996 Null rows: 0 Blank rows: 0 Distinct values: 15948996 company.type: (2/10) Total rows: 15948996 Null rows: 0 Blank rows: 5253878 Distinct values: 92 Most common: 5311279: Privately Held 5253878: 1290064: Self-Owned 1055857: Partnership 987045: Public Company 828643: Self-Employed 799655: Nonprofit 334552: Educational 87681: Government Agency 35: De financiación privada company.name: (3/10) Total rows: 15948996 Null rows: 0 Blank rows: 1591 Distinct values: 15549439 Most common: 1591: 1098: . 277: A 246: - 164: None 155: X 142: N/A 132: ... 128: x 122: 1 company.website: (4/10) Total rows: 15948996 Null rows: 0 Blank rows: 3249552 Distinct values: 11272926 Most common: 3249552: 86957: facebook.com 57769: instagram.com 46404: business.site 31397: linktr.ee 27882: indiamart.com 21852: wixsite.com 19008: negocio.site 17366: linkedin.com 13224: yelp.com company.founded: (5/10) Total rows: 15948996 Null rows: 0 Blank rows: 8040264 Distinct values: 1561 Most common: 8040264: 524236: 2020 451742: 2017 441748: 2018 426575: 2019 418318: 2021 411391: 2016 389487: 2015 339212: 2014 299038: 2013 company.industry: (6/10) Total rows: 15948996 Null rows: 0 Blank rows: 1334901 Distinct values: 421 Most common: 1334901: 820156: IT Services and IT Consulting 651746: Construction 651557: Advertising Services 465857: Software Development 455111: Business Consulting and Services 447922: Real Estate 435151: Retail 355049: Financial Services 312937: Wellness and Fitness Services company.size: (7/10) Total rows: 15948996 Null rows: 0 Blank rows: 2655086 Distinct values: 123 Most common: 6646929: 2-10 3584483: 11-50 2655086: 1197530: 51-200 1091094: 1 employee 421595: 201-500 150053: 501-1,000 129373: 1,001-5,000 44755: 10,001+ 27742: 5,001-10,000 company.city: (8/10) Total rows: 15948996 Null rows: 0 Blank rows: 3155391 Distinct values: 410985 Most common: 3155391: 269708: London 124059: Paris 113135: New York 99314: São Paulo 75428: Los Angeles 69691: Madrid 67328: Toronto 63738: Dubai 63456: New Delhi company.state: (9/10) Total rows: 15948996 Null rows: 0 Blank rows: 4524015 Distinct values: 58563 Most common: 4524015: 691167: England 584141: California 329584: Texas 291639: New York 286723: Florida 222552: São Paulo 185925: Maharashtra 171885: Ontario 171657: Île-de-France company.country_code: (10/10) Total rows: 15948996 Null rows: 0 Blank rows: 2961064 Distinct values: 272 Most common: 4059985: US 2961064: 1232403: GB 885302: IN 756411: FR 664235: BR 467501: DE 414433: NL 410372: ES 389535: CA
  - simonw 3y ago
    The thing I find most interesting is this: 269708: London 124059: Paris 113135: New York 99314: São Paulo 75428: Los Angeles 69691: Madrid 67328: Toronto 63738: Dubai 63456: New Delhi I would not have expected São Paulo to come fourth in this list, after New York but in front of Los Angeles. I just learned it's the 4th largest city in the world https://en.wikipedia.org/wiki/List_of_largest_cities https://en.wikipedia.org/wiki/List_of_largest_cities - after Tokyo, Delhi, Shanghai - but I guess it has much more of a representation on LinkedIn than those other cities.