Issue
I have a dataframe of the following form:
Year 1 Grade | Year 2 Grade | Year 3 Grade | Year 4 Grade | Year 1 Students | Year 2 Students | Year 3 Students | Year 4 Students |
---|---|---|---|---|---|---|---|
60 | 70 | 80 | 100 | 20 | 32 | 18 | 25 |
I would like to somehow transpose this table to the following format:
Year | Grade | Students |
---|---|---|
1 | 60 | 20 |
2 | 70 | 32 |
3 | 80 | 18 |
4 | 100 | 25 |
I created a list of years and initiated a new dataframe with the "year" column. I was thinking of matching the year integer to the column name containing it in the original DF, match and assign the correct value, but got stuck there.
Solution
Here's one way to do it. Feel free to ask questions about how it works.
import pandas as pd
cols = ["Year 1 Grade", "Year 2 Grade", "Year 3 Grade" , "Year 4 Grade",
"Year 1 Students", "Year 2 Students", "Year 3 Students", "Year 4 Students"]
vals = [60,70,80,100,20,32,18,25]
vals = [[v] for v in vals]
df = pd.DataFrame({k:v for k,v in zip(cols,vals)})
grades = df.filter(like="Grade").T.reset_index(drop=True).rename(columns={0:"Grades"})
students = df.filter(like="Student").T.reset_index(drop=True).rename(columns={0:"Students"})
pd.concat([grades,students], axis=1)
Answered By - Joshua Farina
0 comments:
Post a Comment
Note: Only a member of this blog may post a comment.