识别数据框中的元素

Question

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

Answer 1

这是一种更有效的方法。您首先从字典中构建一个数据框，然后在该数据框上执行实际工作。

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: object