How to convert format in Pandas [duplicate]
I have a data file in cvs format like this:
StudentID Math Physics Chemistry History
001 80 90 95 98
002 99 85 90 70
003 77 80 95 90
The format I want is like:
StudentID Math Score
001 Math 80
001 Physics 90
001 Chemistry 95
001 History 98
002 Math 99
002 Physics 85
002 Chemistry 90
002 History 70
003 Math 77
003 Physics 80
003 Chemistry 95
003 History 90
How to do this format conversion in Pandas? Thanks in advance.
Considering that I am recreating your dataframe
and use the pandas.melt
import pandas as pd
df = pd.DataFrame({"StudentID":['001','002','003'],
"Math":[80,99,77],
"Physics":[90,85,80],
"Chemistry":[95,90,95],
"History":[98,70,90]})
df1 = df.melt(id_vars=['StudentID'])
output df1
:
StudentID variable value
0 001 Math 80
1 002 Math 99
2 003 Math 77
3 001 Physics 90
4 002 Physics 85
5 003 Physics 80
6 001 Chemistry 95
7 002 Chemistry 90
8 003 Chemistry 95
9 001 History 98
10 002 History 70
11 003 History 90
Thanks to Jon Clements for the inputs
Try my way....
import pandas as pd
d = {'ID': [0, 1, 2, 3], 'history': pd.Series([3, 4,5,6]), 'math': pd.Series([5, 6,7,8])}
df = pd.DataFrame(data=d)
print(df)
df2 = df
df.drop(['ID'],axis=1)
print(df)
columns = df.columns
df_results = pd.DataFrame()
for i in columns:
df_temp = pd.DataFrame()
df_temp['ID'] = df2['ID']
df_temp['Course']= i
df_temp['Score']= df2[i]
df_results = pd.concat([df_results,df_temp])
print(df_results)