读取CSV转置 pandas [英] Read CSV Transpose pandas

问题描述

我有一个看起来如下的数据集:

I have a dataset that looks as follows:

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

我读入并获取数据，如下所示:

I read in and get the data as follows:

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

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

Output of above: (just as an example)

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

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

Ultimately, I would like to take my dataset from above, and transform it into a dataset like below by combining all headers together like below:

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

输出:

这篇关于读取CSV转置 pandas 的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！