python - 如何从熊猫中所有列的字符串中提取数字并取数字的中位数？

Question

我有这个熊猫数据框：

name,a,b,c,d,e,f,g,h,i,j
"Female, n (%)",1991 (38.26%),1018 (41.52%),438 (35.12%),771 (35.16%),244 (35.72%),343 (32.48%),316 (40.51%),177 (33.84%),133 (41.18%),792 (35.92%)
"Male, n (%)",3190 (61.30%),1426 (58.16%),803 (64.39%),1415 (64.52%),436 (63.84%),711 (67.33%),463 (59.36%),345 (65.97%),187 (57.89%),1403 (63.63%)
"Age, years",44.00 [38.00 - 50.00],43.00 [37.00 - 49.00],43.00 [37.00 - 49.00],44.00 [38.00 - 50.00],44.00 [39.00 - 50.00],44.00 [38.00 - 50.00],43.00 [37.00 - 49.00],45.00 [39.00 - 51.00],44.00 [37.00 - 50.00],45.00 [38.00 - 51.00]

我想要做的是取值的中位数，但符合以下标准：

1. 如果该行由 组成(\d%)，那么我想提取该值
2. 如果该行由 组成[\d - \d]，那么我想提取方括号之前的数字。

需要注意的是，每一行都将具有相同类型的数据。

预期结果：

标签： pythonpandasdataframe

Answer 1

方法一

我们可以replace将数据框中的多余字符，使每一行只包含括号中的数值或方括号前的数值，然后将提取值的 dtype 更改为float并median沿列轴

d = {r'.*?\((.*)%\)': r'\1', r'^(\S+)\s\[.*': r'\1'}
df['median'] = df.set_index('name').replace(d, regex=True).astype(float).median(axis=1).values

见网上regex demo

方法二

stack要重塑的数据extract框，然后是堆叠帧中的数值，然后将dtype提取值更改为并float计算medianlevel=0

df['median'] = df.set_index('name').stack()\
                 .str.extract(r'((?<=\()\S+(?=%\))|^\S+(?=\s\[))', expand=False)\
                 .astype(float).median(level=0).values

见网上regex demo

结果

            name                      a                      b                      c                      d                      e                      f                      g                      h                      i                      j  median
0  Female, n (%)          1991 (38.26%)          1018 (41.52%)           438 (35.12%)           771 (35.16%)           244 (35.72%)           343 (32.48%)           316 (40.51%)           177 (33.84%)           133 (41.18%)           792 (35.92%)  35.820
1    Male, n (%)          3190 (61.30%)          1426 (58.16%)           803 (64.39%)          1415 (64.52%)           436 (63.84%)           711 (67.33%)           463 (59.36%)           345 (65.97%)           187 (57.89%)          1403 (63.63%)  63.735
2     Age, years  44.00 [38.00 - 50.00]  43.00 [37.00 - 49.00]  43.00 [37.00 - 49.00]  44.00 [38.00 - 50.00]  44.00 [39.00 - 50.00]  44.00 [38.00 - 50.00]  43.00 [37.00 - 49.00]  45.00 [39.00 - 51.00]  44.00 [37.00 - 50.00]  45.00 [38.00 - 51.00]  44.000