如何从熊猫数据帧中的条件下的字符串列表中获取子字符串

Question

这就是我所拥有的：

df = pd.DataFrame({'Name': {0: 'Mark', 1: 'John', 2: 'Rick'},
 'Location': {0: ['Mark lives in UK',
   'Rick lives in France',
   'John Lives in US'],
  1: ['Mark lives in UK', 'Rick lives in France', 'John Lives in US'],
  2: ['Mark lives in UK', 'Rick lives in France', 'John Lives in US']}})

这就是我想要得到的：

desired_output = pd.DataFrame({'Name': ['Mark', 'John', 'Rick'],
                   'Location':[['Mark lives in UK', 'Rick lives in France', 'John Lives in US'], ['Mark lives in UK', 'Rick lives in France', 'John Lives in US'], ['Mark lives in UK', 'Rick lives in France', 'John Lives in US']],
              'Outcome': ['Mark lives in UK', 'John Lives in US', 'Rick lives in France']
            })

这就是我尝试的：

df['Sorted'] = df['Location'].str.split(',')
df.apply(lambda x: [idx for idx,s in enumerate(x.sorted) if x.Name in x.sorted])

事先感谢您！

Answer 1

如果您从一开始就不需要位置列，则可以使用以下方式：

df = pd.DataFrame({'Name': {0: 'Mark', 1: 'John', 2: 'Rick'},
 'Location': {0: ['Mark lives in UK',
   'Rick lives in France',
   'John Lives in US'],
  1: ['Mark lives in UK', 'Rick lives in France', 'John Lives in US'],
  2: ['Mark lives in UK', 'Rick lives in France', 'John Lives in US']}})
df = df.explode('Location')
df['Person_IND'] = df['Location'].apply(lambda x : x.split(' ')[0])
df = df.loc[df['Name'] == df['Person_IND']]
df[['Name', 'Location']]

如果您真的需要该中间列，则可以执行此操作并重新名称列

df = pd.DataFrame({'Name': {0: 'Mark', 1: 'John', 2: 'Rick'},
 'Location': {0: ['Mark lives in UK',
   'Rick lives in France',
   'John Lives in US'],
  1: ['Mark lives in UK', 'Rick lives in France', 'John Lives in US'],
  2: ['Mark lives in UK', 'Rick lives in France', 'John Lives in US']}})
df1 = df.explode('Location')
df1['Person_IND'] = df1['Location'].apply(lambda x : x.split(' ')[0])
df1 = df1.loc[df1['Name'] == df1['Person_IND']]
df1 = df1[['Name', 'Location']]
df_merge = pd.merge(df, df1, on = 'Name')
df_merge

Answer 2

您可以在行上尝试应用

df['Outcome'] = df.apply(lambda row: [loc for loc in row['Location'] if row['Name'] in loc], axis=1)

print(df)

   Name                                                    Location  \
0  Mark  [Mark lives in UK, Rick lives in France, John Lives in US]
1  John  [Mark lives in UK, Rick lives in France, John Lives in US]
2  Rick  [Mark lives in UK, Rick lives in France, John Lives in US]

                  Outcome
0      [Mark lives in UK]
1      [John Lives in US]
2  [Rick lives in France]

，也可以尝试爆炸

df['Outcome'] = (df.explode('Location')
                 .loc[lambda df: df.apply(lambda row: row['Name'] in row['Location'], axis=1), 'Location'])

print(df)

   Name                                                    Location  \
0  Mark  [Mark lives in UK, Rick lives in France, John Lives in US]
1  John  [Mark lives in UK, Rick lives in France, John Lives in US]
2  Rick  [Mark lives in UK, Rick lives in France, John Lives in US]

                Outcome
0      Mark lives in UK
1      John Lives in US
2  Rick lives in France