可以在 Pandas 数据框中创建子列吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/51021468/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Can sub-columns be created in a pandas data frame?
提问by Mazz
I am working with a data frame in Jupyter Notebooks and I am having some difficulty with it. The data frame consists of locations and these are represented by coordinates. These points represent a route taken by a driver on a given day.
我正在使用 Jupyter Notebooks 中的数据框，但遇到了一些困难。数据框由位置组成，这些位置由坐标表示。这些点代表驾驶员在给定日期所采取的路线。
There are 3columns at the moment; Start, Intermediary or End.
目前有3列；开始、中间或结束。
A driver begins the day at the Start point, visits 1 or more Intermediary points and returns to the End point at the end of the day. The Start point is like a base location so the End point is identical to the Start point.
司机在起点开始一天，访问 1 个或多个中间点，并在一天结束时返回终点。起点就像一个基本位置，因此终点与起点相同。
It's very basic but I am having trouble visualising this data. I was thinking something like this below to help improve my situation:
这是非常基本的，但我无法可视化这些数据。我在想下面这样的事情来帮助改善我的情况：
| Start | Intermediary | End |
| | | | | | |
_________________________________________________________________
| s_lat | s_lng | i_lat | i_lng | e_lat | e_lng |
Or would it be best if I scrap the top 3 columns (Start, Intermediary, End)?
或者如果我取消前 3 列（开始、中间、结束）会更好吗？
I am keen not to start a discussion here as per the Guidelines so I am keen to learn something new about Python Pandas and if there is a way I can improve my current method.
我不想根据指南在这里开始讨论，所以我很想学习一些关于 Python Pandas 的新东西，如果有办法改进我目前的方法。
回答by jezrael
I think need here MultiIndexcreated by MultiIndex.from_product:
我认为这里需要MultiIndex由MultiIndex.from_product以下人员创建：
mux = pd.MultiIndex.from_product([['Start','Intermediary','End'], ['lat','lng']])
df = pd.DataFrame(data, columns=mux)
EDIT:
编辑：
Setup:
设置：
temp=u""" start intermediary end
('54.957055',' -7.740156') ('54.956915136264', ' -7.753690062122') ('54.957055','-7.740156')
('54.8913208', '-7.5740475') ('54.864402885577', '-7.653445692445'),('54','0') ('54.8913208','-7.5740475')
('55.2375819', '-7.2357427') ('55.253936739337', '-7.259624609577'), ('54','2'),('54','1') ('55.2375819','-7.2357427')
('54.5298806', '-8.1350247') ('54.504374314741', '-8.188334960168') ('54.5298806','-8.1350247')
('54.2810187', ' -7.896937') ('54.303836850038', '-8.180136033695'), ('54','3') ('54.2810187','-7.896937')
"""
#after testing replace 'pd.compat.StringIO(temp)' to 'filename.csv'
df = pd.read_csv(pd.compat.StringIO(temp), sep="\s{3,}")
print (df)
start \
0 ('54.957055',' -7.740156')
1 ('54.8913208', '-7.5740475')
2 ('55.2375819', '-7.2357427')
3 ('54.5298806', '-8.1350247')
4 ('54.2810187', ' -7.896937')
intermediary \
0 ('54.956915136264', ' -7.753690062122')
1 ('54.864402885577', '-7.653445692445'),('54','0')
2 ('55.253936739337', '-7.259624609577'), ('54',...
3 ('54.504374314741', '-8.188334960168')
4 ('54.303836850038', '-8.180136033695'), ('54',...
end
0 ('54.957055','-7.740156')
1 ('54.8913208','-7.5740475')
2 ('55.2375819','-7.2357427')
3 ('54.5298806','-8.1350247')
4 ('54.2810187','-7.896937')
import ast
#convert string values to tuples
df = df.applymap(lambda x: ast.literal_eval(x))
#convert onpy pairs values to nested lists
df['intermediary'] = df['intermediary'].apply(lambda x: list(x) if isinstance(x[1], tuple) else [x])
#DataFrame by first Start column
df1 = pd.DataFrame(df['start'].values.tolist(), columns=['lat','lng'])
#DataFrame by intermediary column with reshape for 2 columns df
df2 = (pd.concat([pd.DataFrame(x, columns=['lat','lng']) for x in df['intermediary']], keys=df.index)
.reset_index(level=1, drop=True)
.add_prefix('intermediary_'))
print (df2)
#join all DataFrames together
df3 = df1.add_prefix('start_').join(df2).join(df1.add_prefix('end_'))
#create MultiIndex by split
df3.columns = df3.columns.str.split('_', expand=True)
print (df3)
start intermediary end \
lat lng lat lng lat
0 54.957055 -7.740156 54.956915136264 -7.753690062122 54.957055
1 54.8913208 -7.5740475 54.864402885577 -7.653445692445 54.8913208
1 54.8913208 -7.5740475 54 0 54.8913208
2 55.2375819 -7.2357427 55.253936739337 -7.259624609577 55.2375819
2 55.2375819 -7.2357427 54 2 55.2375819
2 55.2375819 -7.2357427 54 1 55.2375819
3 54.5298806 -8.1350247 54.504374314741 -8.188334960168 54.5298806
4 54.2810187 -7.896937 54.303836850038 -8.180136033695 54.2810187
4 54.2810187 -7.896937 54 3 54.2810187
lng
0 -7.740156
1 -7.5740475
1 -7.5740475
2 -7.2357427
2 -7.2357427
2 -7.2357427
3 -8.1350247
4 -7.896937
4 -7.896937
回答by Charles R
You can read an Excel file with 2 headers (2 levels of columns).
您可以读取带有 2 个标题（2 级列）的 Excel 文件。
df = pd.read_excel(
sourceFilePath,
index_col = [0],
header = [0, 1]
)
You can reshape your df like this in order to keep just 1 header (its easier to work with only 1 header):
您可以像这样重塑 df 以仅保留 1 个标题（仅使用 1 个标题更容易）：
df = df.stack([0,1], dropna=False).to_frame('Valeur').reset_index()
