python - 如何根据标题名称移动 csv 中的数据并将其移动到同一列？

Question

我有一个包含机场数据的 csv 文件，如下所示:

Name     | State | Runway | data1 | data2 | data3 | Runway | data1 | data2 | data3 | etc
------------------------------------------------------------------------------------
Abu Dabi | UAE   | 01     | 9292  | 2229  | 8282  | 02     | 9929  | 9922  | 2828  | etc

我怎样才能把它改成这样:

Name     | State | Runway | data1 | data2 | data3 |
---------------------------------------------------
Abu Dabi | UAE   | 01     | 9292  | 2229  | 8282  |
                 | 02     | 9929  | 9922  | 2828  |
                 | etc    | etc   | etc   | etc   |

谢谢。

Answer 1

这是一个带有 groupby 列名和 concat 的:

# if you start from your csv
# pandas will rename repeated columns
# e.g. you would have Runway, Runway.1,...
df = pd.read_csv('data.csv')

# fix repeated column names:
df.columns = [col.split('.')[0] for col in df.columns]

new_df = df.set_index(['Name','State'])
pd.concat(g for x,g in new_df.groupby((new_df.columns =='Runway').cumsum(),
                                      axis=1))

输出:

                Runway  data1  data2  data3
Name     State                             
Abu Dabi UAE         1   9292   2229   8282
         UAE         2   9929   9922   2828