Python Dataframe: pivot rows as columns-CodePudding

I have raw files from different stations. When I combine them into a dataframe, I see three columns with matching id and name with different component. I want to convert this into a dataframe where name entries become the column names Code:

df = 
      id                      name      component
0      1             Serial Number          103
1      2              Station Name           DC
2      1             Serial Number          114
3      2              Station Name           CA
4      1             Serial Number          147
5      2              Station Name           FL

Expected answer:

new_df = 
      Station Name      Serial Number     
0     DC                  103
1     CA                  114
2     FL                  147

My answer:

# Solution1
df.pivot_table('id','name','component')
name 
NaN NaN NaN NaN
# Solution2
df.pivot(index=None,columns='name')['component']
name 
NaN NaN NaN NaN

I am not getting desired answer. Any help?

CodePudding user response：

First you have to make every 2 rows with the same id, after that you can use pivot table.

import pandas as pd

df = pd.DataFrame({'id': ["1", "2", "1", "2", "1", "2"],
                    'name': ["Serial Number", "Station Name", "Serial Number", "Station Name", "Serial Number", "Station Name"],
                   'component': ["103", "DC", "114", "CA", "147", "FL"]})
new_column = [x//2 1 for x in range(len(df))]
df["id"] = new_column

df = df.pivot(index='id',columns='name')['component']

CodePudding user response：

If your Serial Number is just before Station Name, you can pivot on name columns then combine the every two rows:

df_ = df.pivot(columns='name', values='component').groupby(df.index // 2).first()

print(df_)

name Serial Number Station Name
0              103           DC
1              114           CA
2              147           FL