CODEWITHSUNDEEP
pythonpandas
Softwareaddict
Softwareaddict

Reputation: 87

How to map from csv file to another one in python?

I have 2 csv files . The first one looks like this : 

ID , Exersice 
1 , 1.1
1 , 1.2
3 , 1.4
.
.

it simply contains the IDs of students and exercises they had done . the second one contains the IDs and the grade of each one :

ID , 1.1 , 1.2 ,1.3 ...
1  , 5   , 9   ,8   ...
3  , 4   , 10  ,6   ...
.
.

so how to map from the second file to the first one to be like this :

ID , Exercise , grade 
1  , 1.1      , 5
1  , 1.2      , 9
.
.

Upvotes: 1

Views: 1867

Answers (3)

jezrael
jezrael

Reputation: 863166

Create MultiIndex Series with DataFrame.set_index and DataFrame.stack, if necessary convert all columns without first to floats and last use DataFrame.join:

s = df2.set_index('ID').rename(columns=float).stack().rename('grade')
df = df1.join(s, on=['ID','Exersice'])
print (df)
   ID  Exersice  grade
0   1       1.1    5.0
1   1       1.2    9.0
2   3       1.4    NaN

Another similar solution:

df3 = df2.melt('ID', var_name='Exersice', value_name='new')
df3['Exersice'] = df3['Exersice'].astype(float)

df = df1.merge(df3, on=['ID','Exersice'], how='left')
print (df)
   ID  Exersice  new
0   1       1.1  5.0
1   1       1.2  9.0
2   3       1.4  NaN

Upvotes: 0

anttilip
anttilip

Reputation: 3

One way is to create the grades column to the first data frame by mapping values from the second table.

Here the ID column of the second table is set to index for easier mapping. Also, second table column values are strings, so while applying the cell values from the first are cast to strings.

import pandas as pd

df_exercises = pd.read_csv("student_exercises.csv")
df_grades = pd.read_csv("student_grade.csv")

df_grades.set_index("ID", inplace=True)
df_exercises['grades'] = df_exercises.apply(lambda x: df_grades.loc[x.ID, str(x.Exersice)], axis=1)

Upvotes: 0

Anthony R
Anthony R

Reputation: 2939

Merge, join and concatenate

The link provides examples on how to do this

specific pd.concat(data, axis=1) should do the trick

Upvotes: 2

Related Questions