Python Dataframe: pivot rows as columns

Question

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?

Dean Taler · Accepted Answer · 2022-04-03 13:56:17Z

1

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']

answered Apr 3, 2022 at 13:56

Dean Taler

7931 gold badge13 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ynjxsjmh · Accepted Answer · 2022-04-03 15:37:36Z

1

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

answered Apr 3, 2022 at 15:37

Ynjxsjmh

30.3k7 gold badges43 silver badges64 bronze badges

Collectives™ on Stack Overflow

Python Dataframe: pivot rows as columns

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related