PythonPandas数据框旋转

pxq42qpu 于 2022-12-10 发布在 Python

关注(0)|答案(2)|浏览(162)

I have such .txt file:
| Field | Value |
| ------------ | ------------ |
| First | 1 |
| Second | alfa |
| | |
| First | 23 |
| Second | beta |
| | |
| First | 55 |
| Second | omega |
I need to read and transform this file to get data like this:
| First | Second |
| ------------ | ------------ |
| 1 | alfa |
| 23 | beta |
| 55 | omega |
I start with this:

file = './data.txt'
df = pd.read_csv(file, sep='\t',header=None, skiprows=89, skipfooter=11, engine='python')
df = df.pivot(values=1, columns=0)

but it looks as I need to generate some indexes otherwise my pivoted table looks not very well
| First | Second |
| ------------ | ------------ |
| 1 | |
| | alfa |
| 23 | |
| | beta |
| 55 | |
| | omega |
Is any other solution hot to read that data and get the results that I need?

pandas

来源：https://stackoverflow.com/questions/74671429/python-pandas-data-frame-pivoting

2条答案

按热度按时间

x4shl7ld1#

The trick is you need to create common keys for the index. Using .assign create a column named CommonKeys which is the cumcount of grouping on the Fields column. Finally chain functions to pivot and clean up the df.

df = (
    df.assign(CommonKeys=df.groupby("Field").cumcount())
    .pivot(index="CommonKeys", columns="Field", values="Value")
    .reset_index(drop=True)
    .rename_axis(None, axis=1)
)

print(df)

Output:

First Second
0     1   alfa
1    23   beta
2    55  omega

赞(0）回复(0）举报 2022-12-10

58wvjzkj2#

为了使代码正常工作，我必须修改访问.csv文件的方式，因为我没有那么多行。

import pandas as pd

file = './data.txt'
df = pd.read_csv(file, sep='\t',header=0, engine='python')
df = df.pivot(values='Value', columns='Field')

# for each column on the dataframe, sort the value and ignore the index
for col in df.columns:
      df[col] = df[col].sort_values(ignore_index=True)

# drop NaN
df.dropna(axis=0, how='all', inplace=True)

# Show dataframe
print(df)

以下是有关.sort_values的更多信息：
https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.sort_values.html
希望能有所帮助：）

赞(0）回复(0）举报 2022-12-10

我来回答

PythonPandas数据框旋转

2条答案

相关问题

热门标签

最新问答