基于多个列表在Dataframe中创建新列

Question

我试图在数据框中创建一个新的列“BroadCategory”，它基于数据中另一个名为“场馆类别”的列中的值是否出现在特定列表中。我有5个列表，用于填写新列中的值

例如：

df['BroadCategory'] = np.where(df['VenueCategory'].isin(Bar),'Bar','Other') 
df['BroadCategory'] = np.where(df['VenueCategory'].isin(Museum_ArtGallery),'Museum/Art Gallery','Other')
df['BroadCategory'] = np.where(df['VenueCategory'].isin(Public_Transport),'Public Transport','Other')
df['BroadCategory'] = np.where(df['VenueCategory'].isin(Restaurant_FoodVenue),'Restaurant/Food Venue','Other')

我最终希望列表栏中出现的VenueCography列中的值被标记为“Bar”，列表Museum\u ArtGallery中出现的值被标记为“Museum\u ArtGallery”，等等。我上面的代码无法实现这一点

我尝试这样做是为了保留我以前填写的值，但它仍然会覆盖我根据以前的条件填写的值：

df['BroadCategory'] = np.where(df[df.VenueCategory!='Other'].isin(Entertainment_Venue),'Entertainment Venue','Other')

如何根据VenueContegory列中的值是否出现在指定的列表栏、餐厅、公共交通、博物馆艺术画廊等中，用特定值填充BoardCategory列

Answer 1

您的解决方案已经接近。只是为了不覆盖以前的值，您应该获得行的子集，并且只在子集上设置新值

为此，您可以首先将新列BroadCategory初始化为“Other”。然后，通过使用.isin()函数（就像您现在使用的那样）使用布尔掩码为新列下标，设置每个类别的行子集。代码如下所示：

df['BroadCategory'] = 'Other'
df['BroadCategory'][df['VenueCategory'].isin(Bar)] = 'Bar' 
df['BroadCategory'][df['VenueCategory'].isin(Museum_ArtGallery)] = 'Museum/Art Gallery'
df['BroadCategory'][df['VenueCategory'].isin(Public_Transport)] = 'Public Transport'
df['BroadCategory'][df['VenueCategory'].isin(Restaurant_FoodVenue)] = 'Restaurant/Food Venue'
df['BroadCategory'][df['VenueCategory'].isin(Entertainment_Venue)] = 'Entertainment Venue'

Answer 2

venue_list = [['Bar', Bar],
               ['Museum_ArtGallery',Museum_ArtGallery]
               #etc
               ]
venue_lookup = pd.concat([
    pd.DataFrame({
        'BroadCategory':venue[0],
        'VenueCategory':venue[1]}) for venue in venue_list]
        )
pd.merge(df, venue_lookup, how='left', on = 'VenueCategory')

Answer 3

支持你的数据是这样的

df=pd.DataFrame({'VenueCategory':['drink','wine','MOMA','MTA','sushi','Hudson']})
Bar=['drink','wine','alcohol']
Museum_ArtGallery=['MOMA','MCM']
Public_Transport=['MTA','MBTA']
Restaurant_FoodVenue=['sushi','chicken']

准备字典：

from collections import defaultdict
d=defaultdict(lambda:'other')
d.update({x:'Bar' for x in Bar})
d.update({x:'Museum_ArtGallery' for x in Museum_ArtGallery})
d.update({x:'Public_Transport' for x in Public_Transport})
d.update({x:'Restaurant_FoodVenue' for x in Restaurant_FoodVenue})

生成新列并打印结果：

df['BroadCategory']=df['VenueCategory'].apply(lambda x:d[x])
df