从数据框中删除方括号和撇号

Question

我是一名外科医生，正在尝试分析一些患者数据。我有一个患者数据框（271x15），这些患者进行了多次手术。这是来自单个操作的更大（4010x71）数据帧，使用@Arne的大量帮助。基本上（见下文） original post）使用透视表，然后查找多个（>；=2）操作。这太棒了。我感兴趣的是前两次手术以及两次手术之间的天数，以确定植入手术的持续时间。 数据框头部位于此处，显示植入物插入和移除的患者ID和代码（V011和V014）

                                 OPERTN_01      OPDATE_01
ID      
11                              [V011, V014]    [2016-06-21, 2017-02-27]
13                              [V011, V014]    [2016-07-14, 2016-01-14]
14                              [V014, V011]    [2014-02-25, 2014-07-01]
15                              [V014, V011]    [2014-06-26, 2015-04-16]

我希望把两次手术的日期减去

1. 删除方括号
2. 将？元组拆分为两列
3. 确保日期为pd.datetime
4. 减去两个日期

我一直在拆支架。我尝试过替换df.replace("[", "")，这对数据帧或序列OPERTN_01没有影响。理想情况下，我希望在整个数据帧中删除方括号，而不是逐列删除

在这个数据框架中产生的列表（感谢@Arne）产生了非常好的描述性统计数据，但对我来说很难操作

我还有一个问题，OPDATE_01中的日期没有排序，因此日期之间的差异通常为负数。可能是因为我想在一个时间做的太多了

Answer 1

你在找这样的东西吗：

from io import StringIO
import ast
import pandas as pd

#     create sample data    
s = """ID;OPERTN_01;OPDATE_01
11;["V011", "V014"];["2016-06-21", "2017-02-27"]
13;["V011", "V014"];["2016-07-14", "2016-01-14"]
14;["V014", "V011"];["2014-02-25", "2014-07-01"]
15;["V014", "V011"];["2014-06-26", "2015-04-16"]"""

df = pd.read_csv(StringIO(s), sep=';')
df['OPERTN_01'] = df['OPERTN_01'].apply(ast.literal_eval)
df['OPDATE_01'] = df['OPDATE_01'].apply(ast.literal_eval)
df = df.set_index('ID')

#     end sample data    

# list comprehension to sort and convert str to datetime
df['OPDATE_01'] = [sorted([pd.to_datetime(x[0]), pd.to_datetime(x[1])]) for x in df['OPDATE_01']]

# if your values in the list are already datetime then ignore what is above and do
# df['OPDATE_01'] = df['OPDATE_01'].apply(sorted)

# apply pd.Series to explode your list into columns and then rename col if you want
date = df['OPDATE_01'].apply(pd.Series).rename(columns={0:'OPDATE_01_0', 1:'OPDATE_01_1'})
# calculate the difference between dates
date.diff(axis=1)

   OPDATE_01_0 OPDATE_01_1
ID                        
11         NaT    251 days
13         NaT    182 days
14         NaT    126 days
15         NaT    294 days

或

# list comprehension to sort and convert list to datetime
df['OPDATE_01'] = [sorted([pd.to_datetime(x[0]), pd.to_datetime(x[1])]) for x in df['OPDATE_01']]

# if your values in the list are already datetime then ignore what is above and do
# df['OPDATE_01'] = df['OPDATE_01'].apply(sorted)

# apply pd.Series to explode your list into columns and then rename col if you want
date = df['OPDATE_01'].apply(pd.Series).rename(columns={0:'OPDATE_01_0', 1:'OPDATE_01_1'})
# merge two frames on ID to maintain all columns
m = df['OPERTN_01'].to_frame().merge(date, left_index=True, right_index=True)
# calc diff and assign to new column
m['diff'] = m.diff(axis=1)['OPDATE_01_1']

       OPERTN_01 OPDATE_01_0 OPDATE_01_1     diff
ID                                               
11  [V011, V014]  2016-06-21  2017-02-27 251 days
13  [V011, V014]  2016-01-14  2016-07-14 182 days
14  [V014, V011]  2014-02-25  2014-07-01 126 days
15  [V014, V011]  2014-06-26  2015-04-16 294 days

根据你的评论

# just changing variable name to match your comment
df_implants = m

# convert OPERTN_01 to a string
s = df_implants['OPERTN_01'].apply(str)

# boolean indexing to filter df_implants where OPERTN_01 is equal to ['V011', 'V014']
v011v014 = df_implants[(s == "['V011', 'V014']")]

# boolean indexing to filter df_implants where OPERTN_01 is equal to ['V014', 'V011']
v014v011 = df_implants[(s == "['V014', 'V011']")]

v011v014

       OPERTN_01 OPDATE_01_0 OPDATE_01_1     diff
ID                                               
11  [V011, V014]  2016-06-21  2017-02-27 251 days
13  [V011, V014]  2016-01-14  2016-07-14 182 days

v014v011

       OPERTN_01 OPDATE_01_0 OPDATE_01_1     diff
ID                                               
14  [V014, V011]  2014-02-25  2014-07-01 126 days
15  [V014, V011]  2014-06-26  2015-04-16 294 days