英文:
How can I keep both column value aligned after transposing?
问题
您想要对数据集的Col2列进行转置，并保留来自Col1列的对应值，最终得到类似于您所描述的期望输出。以下是您的代码的翻译部分：
# 创建一个列表
column_data = df['Col2'].tolist()
# 将数字字符串转换为浮点数
column_data2 = []
for item in column_data:
if isinstance(item, str) and item.replace('.', '', 1).isdigit():
column_data2.append(float(item))
else:
column_data2.append(item)
df2 = pd.DataFrame(column_data2, columns=['Col2'])
# 基于浮点数拆分行
rows = (df2.Col2.map(type) == float).cumsum()
# 对行进行分组，并按期望的格式处理
df3 = df2.groupby(rows).agg(list)\
.Col2.astype(str).str[1:-1]\
.str.split(',', expand=True)\
.add_prefix("col_")
df3
请注意，这段代码的目标是将Col2列中的数据进行处理和转置，最终得到df3，其中包含了您所期望的输出格式。如果您需要进一步的帮助或有任何疑问，请随时告诉我。
英文:
My Data:
```data = {
'Col1': [1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2],
'Col2': ['33.5', 'W', 'A to B, OK', 'slinks down to hammer', 'T c V b Rell 10 (82b 6x1) DW: 84.14', '33.4', '•', 'A to B, no', 'Tosses it uo', '33.3', 2, 'A to B, 2 R', 'On a right way', 'slinks down to hammer', 'BAN: 185/4CRR: 5.60', 'T 69 (80b 6x4)', 'Mu 7 (17b)', 'Mark 6-0-29-1', 'George Dockrel', 'Bet 31', '33.2', 2, 'A to T, 2 R', 'slinks down to hammer', '33.1', 2, 'A to T, 2 r', 'angling away, cuts it',
'33.5', 'W', 'A to B, OK', 'slinks down to hammer', 'T c V b Rell 10 (82b 6x1) DW: 84.14', '33.4', '•', 'A to B, no', 'Tosses it uo', '33.3', 2, 'A to B, 2 R', 'On a right way', 'slinks down to hammer', 'BAN: 185/4CRR: 5.60', 'T 69 (80b 6x4)', 'Mu 7 (17b)', 'Mark 6-0-29-1', 'George Dockrel', 'Bet 31', '33.2', 2, 'A to T, 2 R', 'slinks down to hammer', '33.1', 2, 'A to T, 2 r', 'angling away, cuts it']
}
df = pd.DataFrame(data)```
I want to transpose col2 of my dataset and I want to keep the corresponding value from col1.
My desired output:
My try so far:
I transposed it as below but the corresponding value from clo1 is not showing in my output.
#make a list
column_data = df['Col2'].tolist()
# Make overs float
column_data2 = []
for item in column_data:
if isinstance(item, str) and item.replace('.', '', 1).isdigit():
column_data2.append(float(item))
else:
column_data2.append(item)
df2 = pd.DataFrame(column_data2, columns=['Col2'])
# splits rows based on floats
rows = (df2.Col2.map(type)==float).cumsum()
df3 = df2.groupby(rows).agg(list)\
.Col2.astype(str).str[1:-1]\
.str.split(',', expand=True)\
.add_prefix("col_")
df3
答案1
得分: 1
你可以使用 pivot_table。关键是使用正则表达式 ^\d+\.\d+$ 来识别行：
row = df['Col2'].str.contains(r'^\d+\.\d+$').fillna(False).cumsum()
col = df.groupby(row).cumcount()
out = (df.pivot_table(index=['Col1', row], columns=col, values='Col2', aggfunc='first')
.droplevel(1).reset_index().fillna(''))
输出：
>>> out
Col1 0 1 2 3 4 5 6 7 8 9 10
0 1 33.5 W A to B, OK slinks down to hammer T c V b Rell 10 (82b 6x1) DW: 84.14
1 1 33.4 • A to B, no Tosses it uo
2 1 33.3 2 A to B, 2 R On a right way slinks down to hammer BAN: 185/4CRR: 5.60 T 69 (80b 6x4) Mu 7 (17b) Mark 6-0-29-1 George Dockrel Bet 31
3 1 33.2 2 A to T, 2 R slinks down to hammer
4 1 33.1 2 A to T, 2 r angling away, cuts it
5 1 33.5 W
6 2 A to B, OK slinks down to hammer T c V b Rell 10 (82b 6x1) DW: 84.14
7 2 33.4 • A to B, no Tosses it uo
8 2 33.3 2 A to B, 2 R On a right way slinks down to hammer BAN: 185/4CRR: 5.60 T 69 (80b 6x4) Mu 7 (17b) Mark 6-0-29-1 George Dockrel Bet 31
9 2 33.2 2 A to T, 2 R slinks down to hammer
10 2 33.1 2 A to T, 2 r angling away, cuts it
英文:
You can use pivot_table. The key is to identify rows using a regex '^\d+\.\d+$':
row = df['Col2'].str.contains(r'^\d+\.\d+$').fillna(False).cumsum()
col = df.groupby(row).cumcount()
out = (df.pivot_table(index=['Col1', row], columns=col, values='Col2', aggfunc='first')
.droplevel(1).reset_index().fillna(''))
Output:
>>> out
Col1 0 1 2 3 4 5 6 7 8 9 10
0 1 33.5 W A to B, OK slinks down to hammer T c V b Rell 10 (82b 6x1) DW: 84.14
1 1 33.4 • A to B, no Tosses it uo
2 1 33.3 2 A to B, 2 R On a right way slinks down to hammer BAN: 185/4CRR: 5.60 T 69 (80b 6x4) Mu 7 (17b) Mark 6-0-29-1 George Dockrel Bet 31
3 1 33.2 2 A to T, 2 R slinks down to hammer
4 1 33.1 2 A to T, 2 r angling away, cuts it
5 1 33.5 W
6 2 A to B, OK slinks down to hammer T c V b Rell 10 (82b 6x1) DW: 84.14
7 2 33.4 • A to B, no Tosses it uo
8 2 33.3 2 A to B, 2 R On a right way slinks down to hammer BAN: 185/4CRR: 5.60 T 69 (80b 6x4) Mu 7 (17b) Mark 6-0-29-1 George Dockrel Bet 31
9 2 33.2 2 A to T, 2 R slinks down to hammer
10 2 33.1 2 A to T, 2 r angling away, cuts it
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论