我在pandas中有一个名为names_and_places的数据帧字典,如下所示。
names_and_places:
Alfred,,,
Date,F_1,F_2,Key
4/1/2020,1,4,NAN
4/2/2020,2,5,NAN
4/3/2020,3,6,"[USA,NY,NY, NY]"
Brett,,,
Date,F_1,F_2,Key
4/1/2020,202,404,NAN
4/2/2020,101,401,NAN
4/3/2020,102,403,"[USA,CT, Fairfield, Stamford] "
Claire,,,
Date,F_1,F_2,Key
4/1/2020,NAN,12,NAN
4/2/2020,NAN,45,NAN
4/3/2020,7,78,"[USA,CT, Fairfield, Darian] "
Dane,,,
Date,F_1,F_2,Key
4/1/2020,4,17,NAN
4/2/2020,5,18,NAN
4/3/2020,7,19,"[USA,CT, Bridgeport, New Haven] "
Edward,,,
Date,F_1,F_2,Key
4/1/2020,4,17,NAN
4/2/2020,5,18,NAN
4/3/2020,7,19,"[USA,CT, Bridgeport, Milford] "(上面的文字或下面的图片)

键列可以是NAN,也可以是Country,State,County,City的形式,但长度可以是3或4个元素(有时没有County )。我需要找到包含在键中的给定元素的所有元素。例如,如果元素= "CT",脚本将返回Edward、Brett、Dane和Claire (顺序并不重要)。如果元素= "Stamford“,则只返回Brett。然而,我正在以一种似乎非常低效的方式来处理识别过程。基本上,我有一些变量可以遍历State、County、City (我目前正在手动输入到变量中)的每个可能的组合,以确定要提取哪些名称,如下所示:
country = 'USA' #this never needs to change
element = 'CT'
#These next two are actually in .txt files that I create once I am asked for
#a given breakdown but I would like to not have to manually input these
middle_node = ['Fairfield','Bridgeport']
terminal_nodes = ['Stamford','Darian','New Haven','Milford']
names=[]
for a in middle_node:
for b in terminal_nodes:
my_key = [country,key_of_interest,a,b]
for s in names_and_places:
for z in names_and_places[s]['Key']:
if my_key == z:
names.append(s)
#Note having "if my_key in names_and_places[s]['Key']": was causing sporadic failures for
#some reason
display(names) 输出:
Edward, Brett, Dane, Claire我希望能够只输入变量元素,这可以是2级(州)、3级(县)或4级(城市)节点。然而,除了添加额外的for循环和进入键列之外,我不知道如何做到这一点。这样做的一个好处(对于像我这样的新手来说)是,double for循环允许我保持扣环的完整性,并使人们更容易看到名字来自哪里,而这也是需要的。
但是有没有更好的方法呢?当key_of_interest为' NY‘并且关键字列中的值可以是美国、纽约或美国、纽约、纽约、皇后区时,如果有一种方法可以处理这种情况,则可以获得奖励积分。
编辑: names_and_places是一个以名称作为索引的字典,因此
display(names_and_places['Alfred'])将会是
Date,F_1,F_2,Key
4/1/2020,1,4,NAN
4/2/2020,2,5,NAN
4/3/2020,3,6,"[USA,NY,NY, NY]"我确实有包含列的原始数据帧:
Date, Field name, Value, Names,其中,字段名称是F_1、F_2或Key,Value是该字段的关联值。然后,我使用字段名称的列来透视名称上的数据,以使提取更容易。
发布于 2020-06-29 12:49:37
这里有一种更有效的方法。您首先在字典之外构建一个数据帧,然后在该数据帧上执行实际工作。
single_df = pd.concat([df.assign(name = k) for k, df in names_and_places.items()])
single_df["Key"] = single_df.Key.replace("NAN", np.NaN)
single_df.dropna(inplace=True)
# Since the location is a string, we have to parse it.
location_df = pd.DataFrame(single_df.Key.str.replace(r"[\[\]]", "").str.split(",", expand=True))
location_df.columns = ["Country", "State", "County", "City"]
single_df = pd.concat([single_df, location_df], axis=1)
# this is where the actual query goes.
single_df[(single_df.Country == "USA") & (single_df.State == "CT")].name输出为:
2 Brett
2 Claire
2 Dane
2 Edward
Name: name, dtype: objecthttps://stackoverflow.com/questions/62610017
复制相似问题