本文是笔者前两天修改论文数据过程中的思路记录，行文没有太多写作及引用文献的规范性，更多是笔者对于数据处理的一个思路备份，其中涉及的天眼查爬虫代码将在下一次进行分享。

问题描述

最近关于climate change的文章又来了一个RP，其中一个Reviewer给了这样的修改意见：

Instead, they could have compared philanthropy funds and flows versus public and private sector funds and flows in China and in the World.

大概意思是关于文章中的philanthropy funds and flows可以进一步划分为public sector 和 private sector进行对比分析，并进一步查找世界上公共部分和私人部门在气候变化慈善基金上的数据，跟中国的情况进行对比分析。

可能的Solutions是:

Add more information about funds of climate philanthropy vs. private sectors. We can add two analyses here: first, some of the data can be obtained from 2020年中国企业慈善发展报告; second, we can do a preliminary analysis of different donors, differentiating regions, ownership, etc.

今天晚上（2022/07/30）笔者要解决这个问题，并得出结论进行回答。可以划分为以下几个子问题：

1. 如何定义public sector 和 private sector ？
2. 我们文章中关于 philanthropy funds and flows in China的数据按照上述定义划分是什么结果？public sector 和 private sector 的投入分别是多少？进行对比分析
3. 怎么找到 philanthropy funds and flows in World 在public sector 和 private sector 的数据？
4. 对比China和World的数据结果可以发现什么？

如何定义public secor 和 private sector ？

首先，通过谷歌直接检索，可以大概对二者进行区分，主要区别是是否有政府的干预。公共部门单位的所有权可以由中央、州或地方政府机构所有，这种所有权可以是全部的，也可以是部分的；私营部门单位的所有权归个人或实体所有，政府零干预。

维基百科：

• 公共部门是指由公共服务和国有企业所组成的经济部分[1]。公共服务是面向全社会的服务，任何人都有权利免费使用。国有企业是公有制下的自负盈亏的商业企业，它们一般销售各种非公共商品和服务。公共部门之外还有私营部门或第三部门。私营部门由旨在为私人企业主赚取利润的经济部门所组成。而第三部门则是由民间社会所开设的非营利组织所组成。
• 私营部门是经济的一部分，有时被称为公民部门（英语：citizen sector），由个人或私人团体拥有，而不是由国家所有，与之相对的是公共部门。私营部门分为两个子部门：家庭部门和私营企业部门。前者从事个人消费活动，后者从事私人投资生产；前者的目标是个人或家庭效用最大化，后者的目标是利润最大化。私营部门经济行为的共同特点是其活动依赖于个人收入和个人资产。

但是这个定义不够准确，下一步需要回归文献查找：

关于公有部门和私有部门的定义，主要有两种分类依据，一种是根据功能来分类（functional grounds）；一种是根据所有权（ownership）来分类；

比如《Exploring the public-private sector wage gap in European countries》就通过NACE Code的功能来划分公有部门和私有部门。

注意：文中说由于缺乏公有部门的指标，所有在划分的时候没有很精确。这个解释可以我们的数据中，因为我们的数据「公司」部门也很难去区分公有和私有

We define the public sector as NACE Code L, which is “Public Administration and defence, compulsory social security” (Public Administration for short). The remaining NACE categories are defined as private and we conduct most of our analysis in these terms. However, in light of the fact that, in some countries, some portion of the health and education sectors is public, we pay special attention to NACE categories M (Education), and N (Health, social work). There is no public sector indicator in the data and it is, therefore, impossible to define and analyse the broader public sector with precision. In our sample, for most countries, the health and education sectors have a pay disadvantage, suggesting that policy concern should be focused on Public Administration relative to the remaining sectors.

但是，《Where Does the Public Sector End and the Private Sector Begin? 》这篇文章也说，通过功能来划分很难准确定义共有部门和私有部门，所以目前大部分都是通过ownership来划分。

It is, therefore, impossible to delineate the public and private sectors solely on functional grounds, since nearly all functions undertaken by government are also performed by the private sector.

The concept of ownership is crucial for defining the public and private sectors.

划分依据如下：可以看到文献所说，跟我们开头的谷歌查找是相互验证的。主要看是否有政府控制（《Where Does the Public Sector End and the Private Sector Begin? 》）

但这里还有一个问题：按照是否以盈利为目的，是否应该将第三部门单独划分出来？根据《Defining and Theorizing the Third Sector》,是否划分都有理论支持（American & European）:

我们的数据的“社会组织和红十字会”部分没法进一步区分，所以可以使用这种观点，当作第三部门作为单独一部门。注意：红十字会也属于第三部门。*（根据中国红十字会官网的组织简介和《中华人民共和国红十字会法（2017 修订）》第二条：中国红十字会是中华人民共和国统一的红十字组织，是从事人道主义工作的社会救助团体。可以明显看出其不以盈利为目的，设置目的是为了公共利益，但不属于政府职能部门，表现为第三部门。）

This category can be subdivided into an American and a European view (though no neat geographical division exists). The former sees the third sector as a discrete sector characterized by certain qualities such as civility, whereas European theorists tend to take “the hybrid view” that views third sector organizations essentially as mixtures of other kinds of social organization such as private and public, or hierarchic and anarchic. The second approach conceives of the third sector not as an object out there waiting to be authoritatively defined but as a kind of societal process.

划分理论：是单独的部门，跟私有部门和公有部门相对。

Most ontological accounts of the third sector place it in relation to the state and the market. For Etzioni, who coined the term “the third sector” in 1973, in his “The third sector and domestic missions,” it was an alternative sector separate from and balancing the state and the market, themselves considered separate sectors. If something is ruled neither primarily by market logic nor via a bureaucratic chain of command, it must be part of the “third” sector. Many current operational definitions follow this basic schema. For the British government, for example, “The term is used to distinguish such organisations from the other two sectors of the economy: the public sector (‘government’) and the private sector (‘businesses’)” (NAO 2009). According to a new textbook on social enterprises, a national economy can be conceptualized as having three sectors: the public sector, a private economy, and a third sector “with organisations established by people on a voluntary basis to pursue social or community goals” (Ridley-Duff and Seanor 2008, p. 1). This view is not restricted geographically to the United States – even though it has been widely associated strongly with researchers at The Johns Hopkins University in Baltimore, Maryland (Salamon and Anheier 1997).

不划分理论:“第三部门”是混合了公有私有的

In contrast, a “European” definition of the third sector does not envisage separate sectors. The third sector or third system is thus conceived not as a corrective addon to the blind spots of a market economy, but as a hybrid form of various kinds of organizations such as firms, bureaucracies, and kinship associations that “act as hybrids, intermeshing different resources and connecting different areas, rather than setting clear demarcation lines around a sector and mapping its size” (Evers 1995, p. 160).

还有最后一个主体，高校怎么划分？同样根据《Where Does the Public Sector End and the Private Sector Begin? 》公立高校属于公有部门，私立高校属于私有部门。

If an extrabudgetary entity is producing market output, it fulfills the GFSM2001 criterion to be a market establishment. Market establishments that have: (1) complete accounting records about it production activities, but not (2) a complete balance sheet, nor (3) the ability to engage in financial transactions in its own name, are deemed to belong to “general government”. Market establishments that also satisfy the last two qualities are deemed to be quasi-corporations and, as such, would belong to the public corporation sector. For example, government-owned universities or public hospitals that are market producers—i.e., charge fees based on the costs of producing the services or that are sufficiently high to have a significant impact on the demand for services provided—would be classified as institutional units of the general government sector, not the nonfinancial corporations sector, when it has accounting records of its output and costs of production, but does not satisfy criteria (2) and (3) above.

Data in China

我们已有的中国慈善基金会数据是这样分类的

根据上述学术定义，各个主体的类别如下：

• 公司：需区分国有和私有企业
• 自然人：私有部门
• 高校：需区分公立学校和私立学校
• 社会组织和红十字会：第三部门
• 政府：公有部门

这里的难题是：如何进一步对公司和高校的数据进行分类？

对于高校，由于数据量很少，可以人工进行分类，比较快速；

首先，整理了「2022全国公办大学名单一览表（1982所）.xlsx」，然后对数据中「学校」的sheet进行匹配，如果匹配成功，就说明是公办的；

剩下的再人工进行检查，遇到不确定的再进行百度检索；

而对于公司，目前有两个思路：

• 用wind下载一下所有上市国企的名单（数据浏览器 -提取所有A股-有个「公司属性」的字段）。提取出来后再匹配的数据，再分析结果怎么样。
• 通过爬虫，批量查询天眼查，抓取天眼查中公司的类型【OK】

根据公司作为关键词进行检索，通过Charles进行小程序抓包cookies，redis抓取代理池缓存，Request和Xpath爬取解析数据，SQL数据库存储，爬取到天眼查的相关信息XX条，与Excel中的「公司」sheet进行匹配，判断属于公有还是私有类型：

在爬取中，受限制了，可能需要一些时间：

跑了一个晚上，一共爬取了1w多条数据。接下来开始进行数据处理：将捐赠人的名称跟所爬取的公司名称进行匹配，从而确定公司的类型。在Excel中通过函数操作如下：

1、进行精确匹配：结果发现很多NA，匹配不上

=VLOOKUP(A2,'/Users/fubo/Desktop/气候变化/[全_20210829所有主体桑基图数据.xlsx]company_0730'!B:Q,16,0)}

2、进行近似匹配，规则很复杂，参考这里（注意，使用的是数组公式，F2进入编辑模式，然后使用ECS快捷键），基本将捐赠人公司跟所爬取的公司匹配成功

=INDIRECT("A"&RIGHT(MAX(MMULT(ISNUMBER(FIND(MID(C2,TRANSPOSE(ROW(INDIRECT("1:"&LEN(C2)))),1),A2:A10875))*1,ROW(INDIRECT("1:"&LEN(C2)))^0)/1%%%+ROW(A2:A10875)),5))

3、vlookup匹配公司类型

=VLOOKUP(C2,'/Users/fubo/Desktop/气候变化/[全_20210829所有主体桑基图数据.xlsx]company_0730'!B:Q,16,0)

4、类型中出现“国有”或者“基层工会”，则归类为公共部门（1），否则为私有部门（0）

=SUMPRODUCT(--ISNUMBER(SEARCH(L4:L5,E2)))

5、通过数据透视表得到金额比例；

将上述所有数据结果进行整理，最终得到了下表：中国气候变化慈善基金的主体分布表。

对于第三点：Data in World 和 第四点：China VS World 已交给团队中的小伙伴完成，此处不再赘述。