如何使用正则表达式或索引从非日期列中导出日期？

2024-04-27 • 问答

我有一个数据框和字典，如下所示

df = pd.DataFrame({
    'subject_id':[1,2,3,4,5],'age':[42,56,75,48,39],'date_visit':['1/1/2020','3/3/2200','13/11/2100','24/05/2198','30/03/2071'],'a11fever':['Yes','No','Yes','No'],'a12diagage':[36,np.nan,40,np.nan],'a12diagyr':[np.nan,2091,'a12diagyrago':[6,9,'a20cough':['Yes','a21cough':[np.nan,'a22agetold':[37,46,'a22yrsago':[np.nan,6,'a22yrtold':[np.nan,2194,np.nan]

 })
df['date_visit'] = pd.to_datetime(df['date_visit'])
disease_dict = {'a11fever' : 'fever','a20cough' : 'cough','a21cough':'cough'}

此数据框包含有关患者的医疗状况和诊断日期的信息

但是正如您所见，诊断日期不直接可用，我们必须根据包含age，yr，{{ 1}}，ago，它们出现在条件列的下5-6列中（例如：diag）。在此条件列之后查找接下来的5列，您将能够获取导出日期所需的信息。a11fever

等条件的silimary

我希望我的输出如下所示

我正在尝试类似下面的操作，但没有帮助

cough

请注意，我之前已经知道疾病的列名（请参阅dict）。我不知道实际的列名是从哪里获得所需的信息以得出日期的。但我知道它包含df = df[(df['a11fever'] =='Yes') | (df['a20cough'] =='Yes') | (df['a21cough'] =='Yes')] # we filter by `Yes` above because we only nned to get dates for people who had medical condition (`fever`,`cough`) df.fillna(0,inplace=True) df['diag_date'] = df["date_visit"] - pd.DateOffset(years=df.filter('age'|'yr'|'ago')) # doesn't help throws error. need to use regex here to select non-na values any of other columns pd.wide_to_long(df,stubnames=['condition','diag_date'],i='subject_id',j='grp').sort_index(level=0) df.melt('subject_id',value_name='valuestring').sort_values('subject_id')，age，ago，yr

之类的关键字

diag是通过从diag_date列中减去derived date来获得的。

规则屏幕截图

例如：date_vist于subject_id = 1因发烧而去医院，他被诊断出年龄为1/1/2020（36）或a12diagage岁（{{ 1}}）。我们知道他的当前年龄和date_visit，因此我们可以选择从任意列中减去6

如您所见，我无法找出如何基于正则表达式选择一列并将其减去

#get of columns with Yes at least one value mask = df[list(disease_dict.keys())].eq('Yes') #assign mask back df[list(disease_dict.keys())] = mask #rename columns names by dict df = df.rename(columns=disease_dict).max(axis=1,level=0) #filter out False rows df = df[mask.any(axis=1)] #convert some columns to index for get only years and condition columns df = df.set_index(['subject_id','age','date_visit']) #extract columns names - removing aDD values s = df.columns.to_series() df.columns = s.str.extract('(yrago|yrsago)',expand=False).fillna(s.str.extract('(age|yr)',expand=False)).fillna(s) #replace True in condition columns to column names ill = set(disease_dict.values()) df.loc[:,ill] = np.where(df[ill].values,np.array(list(ill)),None) #replace columns names to condition df = df.rename(columns = dict.fromkeys(ill,'condition'))

#create MultiIndex - only necessary condition columns are first per groups cols = np.cumsum(df.columns == 'condition') df.columns = [df.columns,cols] #reshape by stack and convert MultiIndex to columns df = df.stack().rename(columns={'age':'age_ill'}).reset_index().drop('level_3',axis=1) #subtract ages df['age_ill'] = df['age'].sub(df['age_ill']) #priority yrago so yrago is filling missing values by age_ill df['yrago'] = df['yrago'].fillna(df['yrsago']).fillna(df['age_ill']).fillna(0).astype(int) df = df.drop(['yrsago','age_ill'],axis=1) #subtract years df['diag_date1'] = df.apply(lambda x: x["date_visit"] - pd.DateOffset(years=x['yrago']),axis=1) #replace years mask1 = df['yr'].notna() df.loc[mask1,'diag_date'] = df[mask1].apply(lambda x: x["date_visit"].replace(year=int(x['yr'])),axis=1) #because priority yr then fillna diag_date by diag_date1 df['diag_date'] = df['diag_date'].fillna(df['diag_date1']) df = df.drop(['diag_date1','date_visit','yr','yrago'],axis=1)

print (df) subject_id condition diag_date 0 1 fever 2014-01-01 1 1 cough 2015-01-01 2 2 cough 2194-03-03 3 3 fever 2091-11-13 4 4 fever 2190-05-24 5 4 cough 2196-05-24

如何使用正则表达式或索引从非日期列中导出日期？

fuying58 回答：如何使用正则表达式或索引从非日期列中导出日期？

大家都在问