我想弄清楚如何在 pandas 中构建一个表，让 pandas 计算唯一值，从 excel 表中检索。
表格:
|--------------|--------------------|
| location | signal |
|--------------|--------------------|
| New York | Vehicle 20 open |
| New York | Vehicle 22 open |
| Washington | Vehicle 20 open |
| Washington | Vehicle 21 open |
| New York | Vehicle 20 open |
| New York | Vehicle 22 open |
| Washington | Vehicle 20 open |
| Washington | Vehicle 21 open |
| New York | Vehicle 20 open |
| New York | Vehicle 22 open |
| Washington | Vehicle 20 closed |
| Washington | Vehicle 21 closed |
| New York | Vehicle 20 closed |
| New York | Vehicle 22 closed |
| Washington | Vehicle 20 closed |
| Washington | Vehicle 21 closed |
| New York | Vehicle 20 open |
| New York | Vehicle 20 open |
| New York | Vehicle 20 open |
|--------------|--------------------|
我怎么不希望它被打印出来(并导出到 Excel 中)
|--------------|-------------------|------------------|
| Alarmtype | Vehicle open | Vehicle Closed |
|--------------|-------------------|------------------|
| New York | 9 | 2 |
| Washington | 4 | 4 |
|--------------|-------------------|------------------|
所以我想计算每个事件(组)在每个位置发生的次数，并将它们的一些放到一个表中
这是我试过的
top = df.groupby(['Location', 'Sign Descr']).count()
or
sorted = df.sort_values(["Location", "Sign Descr"]).groupby(['Location', 'Sign Descr']).nunique()
请您参考如下方法:
首先替换signal 列中的数字，然后使用pd.pivot_table :
df['signal'] = df['signal'].str.replace('([0-9])', '')
pd.pivot_table(df, index='location', columns='signal', aggfunc='size')
signal Vehicle closed Vehicle open
location
New York 2 9
Washington 4 4
如果你想将Alarmtype 作为索引名称。添加rename_axis:
pd.pivot_table(df, index='location', columns='signal', aggfunc='size').rename_axis('Alarmtype')
signal Vehicle closed Vehicle open
Alarmtype
New York 2 9
Washington 4 4
