熊猫从多个其他列和行子集中计算新列。

Question

我很好奇如何根据其他现有列和行子集从dataframe中计算新列。

我有一个有111,222,333,444个房间的数据，每个房间都可能有AAA、BBB床。而且每个房间我都有床位，无论是被占用的还是免费的。我想用以下标准计算一个新列-- "Active“：

active.

• Occupied床优先在房间内活动。

1. ，如果房间只有一张床，它必须是活动的，而不管床的状态如何

。

样本DataFrame：

   Room  Bed    Status
0   111  AAA  Occupied
1   111  BBB      Free
2   222  AAA      Free
3   333  AAA      Free
4   333  BBB  Occupied
5   444  BBB  Occupied

预期产出：

   Room  Bed    Status  Active
0   111  AAA  Occupied    True
1   111  BBB      Free   False
2   222  AAA      Free    True
3   333  AAA      Free   False
4   333  BBB  Occupied    True
5   444  BBB  Occupied    True

我想把它转换成一本字典，然后循环它，但是我有强烈的感觉，这可以通过熊猫内置的功能来实现。

提亚

Answer 1

使用：

print (df)
   Room  Bed    Status
0   111  AAA  Occupied
1   111  BBB      Free
2   222  AAA      Free
3   333  AAA      Free
4   333  BBB  Occupied
5   444  BBB  Occupied
6   555  AAA  Occupied
7   555  BBB  Occupied

#test Occupied
m1 = df['Status'].eq('Occupied')
#test first active if multiple active per Room (555)
m2 = (~df.assign(Status = df['Status'].where(df['Status'].eq('Occupied')))
         .duplicated(['Room','Status']))
#test if one bed room
m3 = df['Room'].map(df['Room'].value_counts()).eq(1)

#chain masks with & for bitwise AND and | for bitwise OR
df['Active'] = m1 & m2 | m3
print (df)
   Room  Bed    Status  Active
0   111  AAA  Occupied    True
1   111  BBB      Free   False
2   222  AAA      Free    True
3   333  AAA      Free   False
4   333  BBB  Occupied    True
5   444  BBB  Occupied    True
6   555  AAA  Occupied    True
7   555  BBB  Occupied   False

编辑：

#test Occupied
m1 = df['Status'].eq('Occupied')
#test first active if multiple active per Room (555)
m2 = (~df.assign(Status = df['Status'].where(df['Status'].eq('Occupied')))
         .duplicated(['Room','Status']))

#test Free
m4 = df['Status'].eq('Free')
#test first active if multiple active per Room (666)
m5 = (~df.assign(Status = df['Status'].where(m4))
         .duplicated(['Room','Status']))

#test if all bed room has Free
m6 = m4.groupby(df['Room']).transform('all')

#chain masks with & for bitwise AND and | for bitwise OR
df['Active'] =  (m5 & m6) | (m1 & m2)
print (df)
   Room  Bed    Status  Active
0   111  AAA  Occupied    True
1   111  BBB      Free   False
2   222  AAA      Free    True
3   333  AAA      Free   False
4   333  BBB  Occupied    True
5   444  BBB  Occupied    True
6   555  AAA  Occupied    True
7   555  BBB  Occupied   False
8   666  AAA      Free    True
9   666  BBB      Free   False