python - 阅读 CSV 转置 Pandas

我有一个如下所示的数据集:

Name  : joe
Job   : Crazy Consultant
Hired : 4/12/2011 3:38:55 AM
Stats : crazy, bald head
Pay   : $5000 Monthly

Name  : Matt
Job   : Crazy Receptionist
Hired : 4/12/2014 3:38:55 PM
Stats : crazy, Lots of hair

Name  : Adam
Job   : Crazy Drinker
Hired : 4/12/2017 3:38:55 AM
Stats : crazy, unknown
Term  : 4/12/2017 3:38:55 PM

我读入并获取数据如下:

df = pd.read_csv(r"pathtomycsv.csv", encoding="UTF-16", delimiter='\s+:').transpose()

以上输出:(仅作为示例)

Name      Job                Hired                 Stats                Name      Job                Hired                 Stats
Joe       Crazy Consultant   4/12/2011 3:38:55 AM  crazy, bald head     Matt      Crazy Consultant   4/12/2011 3:38:55 AM  crazy, bald head

最终，我想从上面获取我的数据集，并通过将所有标题组合在一起将其转换为如下所示的数据集，如下所示:

Name      Job                Hired                 Stats                Pay            Term
Joe       Crazy Consultant   4/12/2011 3:38:55 AM  crazy, bald head     $5000 Monthly  N/A
Matt      Crazy Receptionist 4/12/2014 3:38:55 PM  crazy, Lots of hair  N/A            N/A
Adam      Crazy Drinker      4/12/2017 3:38:55 AM  crazy, unknown       N/A            4/12/2017 3:38:55 PM

最佳答案

你可以这样尝试:

import pandas as pd

df = pd.read_csv('file_name',sep='\s+:\s+',header=None).pivot(columns=0, values=1)
df.index = [df.index, df.Name.notnull().cumsum() - 1]
df = df.stack().reset_index(name='val')
df = df.pivot(index='Name', columns=0, values='val')
df

输出:

关于python - 阅读 CSV 转置 Pandas ，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/42037460/