Python Pandas Change Column to Headings-CodePudding

I have data in the following format: Table 1

This data is loaded into a pandas dataframe. The date column is the index for this dataframe. How would I have it so the names become the column headings (must be unique) and the values correspond to the right dates.

So it would look something like this:

Table 2

CodePudding user response：

Consider the following toy DataFrame:

>>> df = pd.DataFrame({'x': [1,2,3,4], 'y':['0 a','2 a','3 b','0 b']})
>>> df
   x    y
0  1  0 a
1  2  2 a
2  3  3 b
3  4  0 b

Start by processing each row into a Series:

>>> new_columns = df['y'].apply(lambda x: pd.Series(dict([reversed(x.split())])))
>>> new_columns
     a    b
0    0  NaN
1    2  NaN
2  NaN    3
3  NaN    0

Alternatively, new columns can be generated using pivot (the effect is the same):

>>> new_columns = df['y'].str.split(n=1, expand=True).pivot(columns=1, values=0)

Finally, concatenate the original and the new DataFrame objects:

>>> df = pd.concat([df, new_columns], axis=1)
>>> df
   x    y    a    b
0  1  0 a    0  NaN
1  2  2 a    2  NaN
2  3  3 b  NaN    3
3  4  0 b  NaN    0

Drop any columns that you don't require:

>>> df.drop(['y'], axis=1)
   x    a    b
0  1    0  NaN
1  2    2  NaN
2  3  NaN    3
3  4  NaN    0

CodePudding user response：

You will need to split out the column’s values, then rename your dataframe’s columns, and then you can pivot() the dataframe. I have added the steps below.

df[0].str.split(' ' , expand = True) # assumes you only have the one column

df.columns = ['col_name','values'] # use whatever naming convention you like

df.pivot(columns = 'col_name',values = 'values')

Please let me know if this helps.