Merging the dataframe with different indices

Question

I have two data frames with data, now I want to merge the fields of the second data frame to first. If I call the index of the first data frame needs to get all the subject and topic names like below. Can anyone help me with this?

import pandas as pd
sub_data = {'Subjectid':['10','11'],'Author':['Author1','Author2'],'SubjectName':['Maths', 'English']}
df1 = pd.DataFrame(sub_data)
print(df1)

topic_data = {'Topicid':['100','101','102'],'Subjectid':['10','10','11'],'TopicName':['Geometry','Trignometry', 'Tenses']}
df2 = pd.DataFrame(topic_data)
print(df2)

subtopic_data = {'Subtopicid':['1000','1001','1002'],'Topicid':['100','101','102'],'Subjectid':['10','10','11'],'SubtopicTopicName':['Lines','Angles', 'PresentTenses']}
df3 = pd.DataFrame(subtopic_data)
print(df3)

Desired Output:

    Author    SubjectName   topicid   TopicName       Subopicid SubtopicName
10  Author1       Maths     100       Geometry        1000       Lines
10  Author1       Maths     100       Trignometry     1001      Angles

jezrael · Answer 1 · 2019-09-11T06:43:32.113

1

Use DataFrame.merge with convert index to column by DataFrame.reset_index and column to index by DataFrame.set_index:

df = df1.merge(df2.reset_index().set_index('Subjectid'), left_index=True, right_index=True)
print (df)
     Author SubjectName index    TopicName
10  Author1       Maths   100     Geometry
10  Author1       Maths   101  Trignometry
11  Author2     English   102       Tenses

Solution with changed data in question:

df = df1.merge(df2, on='Subjectid').set_index('Subjectid').rename_axis(None)
print (df)
     Author SubjectName Topicid    TopicName
10  Author1       Maths     100     Geometry
10  Author1       Maths     101  Trignometry
11  Author2     English     102       Tenses

For merge DataFrame df3:

df = (df1.merge(df2, on='Subjectid').set_index('Subjectid')
         .merge(df3, on=['Topicid','Subjectid']))
print (df)
    Author Subjectid SubjectName Topicid    TopicName Subtopicid  \
0  Author1        10       Maths     100     Geometry       1000   
1  Author1        10       Maths     101  Trignometry       1001   
2  Author2        11     English     102       Tenses       1002   

  SubtopicTopicName  
0             Lines  
1            Angles  
2     PresentTenses

Last for filtr only Math rows use boolean indexing:

df4 = df[df['SubjectName'] == 'Maths']
print (df4)
    Author Subjectid SubjectName Topicid    TopicName Subtopicid  \
0  Author1        10       Maths     100     Geometry       1000   
1  Author1        10       Maths     101  Trignometry       1001   

  SubtopicTopicName  
0             Lines  
1            Angles

edited Sep 11 '19 at 06:43

answered Sep 11 '19 at 06:21

jezrael

629,482
62
918
895

How do I call if I need only 10 index numbers I mean only maths subjects? – lohith devapatla Sep 11 '19 at 06:27
@lohithdevapatla - I see change data in question, you cannot do it, because then invalidate answers. – jezrael Sep 11 '19 at 06:30
If i need only maths subject and related topic name, Isn't possible to do so? – lohith devapatla Sep 11 '19 at 06:31
@lohithdevapatla - yes, then use `out = df.loc[df['SubjectName'] == 'Maths', 'TopicName']` – jezrael Sep 11 '19 at 06:33
@lohithdevapatla - or `out = df.loc[df['SubjectName'] == 'Maths', ['SubjectName','TopicName']]` – jezrael Sep 11 '19 at 06:35
can you please add the above comment on your code and execute. Thanks – lohith devapatla Sep 11 '19 at 06:42
@lohithdevapatla - Done, added `df3` and filterd `Month` – jezrael Sep 11 '19 at 06:44
Thank you so much. Ill reach you if i stuck anywhere. – lohith devapatla Sep 11 '19 at 06:44

score 1 · Answer 2 · answered Sep 11 '19 at 06:26

1

You can use merge:

pd.merge(df1, df2, on ='Subjectid').set_index('Subjectid')

            Author  SubjectName Topicid TopicName
Subjectid               
10          Author1 Maths       100     Geometry
10          Author1 Maths       101     Trignometry
11          Author2 English     102     Tenses

answered Sep 11 '19 at 06:26

Allen

16,421
4
42
54

How to combain if we have df3 also? – lohith devapatla Sep 11 '19 at 06:29
How do i call if i need only maths subjects related topics and subtopics(third one).I ll add soon – lohith devapatla Sep 11 '19 at 06:30
@lohithdevapatla - check my answer for merge multiple dataframes – jezrael Sep 11 '19 at 06:33

Merging the dataframe with different indices

2 Answers2