如何在Stata中汇总关系数据？ [英] How to Aggregate Relational Data in Stata?

问题描述

我无法解决以下Stata编程问题：

I can't wrap my head around the following Stata programming problem:

我有一张表，列出了客户购买的所有汽车并制造：

I have a table listing all car purchases by customers and make:

Customer | Make | Price
-----------------------
      c1 |   m1 |     1
      c1 |   m1 |     2
      c1 |   m3 |     1
      c2 |   m2 |     2
      c3 |    . |     .

我想将此表转换为每个客户一个观察/行的表，列出支付的最高价格

I want to transform this into a table with one observation/row per customer, listing the maximum price paid for every make:

Customer | m1 | m2 | m3
-----------------------
      c1 |  2 |  0 |  1
      c2 |  0 |  1 |  0
      c3 |  0 |  0 |  0

如何实现？我知道重塑宽，但是由于 c1 | m1 行。另外， c3 的缺失值也会引起麻烦。

How do I achieve this? I know reshape wide, but that doesn't work because of the doubled c1 | m1 row. Also, the missing values for c3 are causing troubles.

推荐答案

在您要执行的操作上，我建议采取一些不同的方法。例如，使用-bysort-，您可以找到每个客户的最高价格。

Depending on what you want to do, I suggest approaching this a little differently. For example using -bysort- you can find the maximum price by customer for each make.

bysort Customer Make : egen maxPrice = max( Price )

或者，您可以使用折叠根据客户查找最高价格并进行以下操作：

Or, you can use collapse to find the max price by customer and make:

collapse (max) Price, by( Customer Make )

但是，如果您确实想要使用-reshape-发布的表，则可以运行以下命令：

But, if you really want the table you posted using -reshape- you could run the following:

collapse (max) Price, by( Customer Make )
drop if Price == .
reshape wide Price, i( Customer ) j( Make ) string
renpfix Price 

请注意，如果价格列中的数据丢失，重整将失败。我将这些观察结果放在了上面的代码中，但是您可以选择执行其他操作，例如在发布的目标表中显示时，将丢失的数据替换为零。

Note that reshape will fail if it encounters missing data in the Price column. I dropped these observations in the code above but you may choose to do something different like replace the missing data with zeros as you show in your posted target table.

这篇关于如何在Stata中汇总关系数据？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！