Combine columns from two dataframes

Q&A DQ Courses
#1

I have dataframes of same shape but different index datatypes. i want to create a new datatframe from the combined columns
Below is the dataframes
df1

totals
2014_Q1 0
2014_Q2 0
2014_Q3 0
2014_Q4 0
2015_Q1 0
2015_Q2 0
2015_Q3 0
2015_Q4 0
2016_Q1 0
2016_Q2 0
2016_Q3 0
2016_Q4 0
2017_Q1 0
2017_Q2 0
2017_Q3 0
2017_Q4 0
2018_Q1 0
2018_Q2 0
2018_Q3 0
2018_Q4 0
2019_Q1 0
2019_Q2 0
2019_Q3 0
2019_Q4 0

df2

0 1
0 2014 Q1
1 2014 Q2
2 2014 Q3
3 2014 Q4
4 2015 Q1
5 2015 Q2
6 2015 Q3
7 2015 Q4
8 2016 Q1
9 2016 Q2
10 2016 Q3
11 2016 Q4
12 2017 Q1
13 2017 Q2
14 2017 Q3
15 2017 Q4
16 2018 Q1
17 2018 Q2
18 2018 Q3
19 2018 Q4
20 2019 Q1
21 2019 Q2
22 2019 Q3
23 2019 Q4

I want something like this
image

Tried this code
pd.concat([df,dframe], axis=1, ignore_index=True)
gives me a long dataframe with NaN’s and push the second dataframe down

#2

If you know that both rows are aligned in exactly the right sequence, then one thing that comes to mind is you can make a 3rd column for both that serves as the column to merge on, and then use pd.merge.

E.g.:

df.insert(0, 'id', range(0,len(df)))
df

This will insert a column called id like so:

id year
0 2014
1 2014
2 2014
3 2014
4 2015
5 2015

Then, you do exactly the same for the second dataframe, and you perform the merge using this new id column you created in both tables.

This will only work if you are completely certain all rows in both tables are already aligned perfectly, which it sounds like you are.

1 Like
#3

Thanks much will go with this

#4

Hi @ekkpodogui , if a post does answer your question, please do mark it as the ‘Solution’, as this helps others find the answer too :slight_smile:

1 Like
#5

So sorry was in a hurry to go implement the solution forgive :pray:t5: