CODEWITHSUNDEEP
pythonpandas
azmirfakkri
azmirfakkri

Reputation: 601

Arrange row values across multiple columns in pandas

I have 3 pandas data frames, each containing 1 column.

df1 = pd.DataFrame({'col1':[111, 222, 333, 444, 555]})
df2 = pd.DataFrame({'col2':[222, 333, 555]})
df3 = pd.DataFrame({'col3':[111, 222, 333, 666]})

I know how to concatenate them along columns:

pd.concat([df1, df2, df3], axis=1)

col1   col2   col3
111    222    111
222    333    222
333    555    333
444    NaN    666
555    NaN    NaN

What I want is that, the first row of all columns must be 111, if 111 is not available, it will be NaN, and this applies to the subsequent rows.

I would like to sort numbers so that the final output is like this:

col1   col2   col3
111    NaN    111
222    222    222
333    333    333
444    NaN    NaN
555    555    NaN
NaN    NaN    666

Is this possible in pandas?

Upvotes: 1

Views: 61

Answers (2)

Naga kiran
Naga kiran

Reputation: 4607

You can try of merging the dataframes on first column

df= f1
for f in [df2,df3]:
    df = df.merge(f,left_on=df.columns[0],right_on=f.columns[0],how='outer')

Out:

    col1    col2    col3
0   111.0   NaN     111.0
1   222.0   222.0   222.0
2   333.0   333.0   333.0
3   444.0   NaN      NaN
4   555.0   555.0    NaN
5   NaN     NaN    666.0

Upvotes: 2

jezrael
jezrael

Reputation: 863166

Yes, it is possible, use set_index with parameter drop=False for index from column:

df1 = pd.DataFrame({'col1':[111, 222, 333, 444, 555]})
df2 = pd.DataFrame({'col2':[222, 333, 555]})
df3 = pd.DataFrame({'col3':[111, 222, 333, 666]})

df11 = df1.set_index('col1', drop=False)
df22 = df2.set_index('col2', drop=False)
df33 = df3.set_index('col3', drop=False)

dfs1 = [df11, df22, df33]
df = pd.concat(dfs1, axis=1)
print (df)

      col1   col2   col3
111  111.0    NaN  111.0
222  222.0  222.0  222.0
333  333.0  333.0  333.0
444  444.0    NaN    NaN
555  555.0  555.0    NaN
666    NaN    NaN  666.0

df = pd.concat(dfs1, axis=1).reset_index(drop=True)
print (df)

    col1   col2   col3
0  111.0    NaN  111.0
1  222.0  222.0  222.0
2  333.0  333.0  333.0
3  444.0    NaN    NaN
4  555.0  555.0    NaN
5    NaN    NaN  666.0

If want join by first column:

L = [x.set_index(x.columns[0], drop=False) for x in dfs]
df = pd.concat(L, axis=1).reset_index(drop=True)
print (df)

    col1   col2   col3
0  111.0    NaN  111.0
1  222.0  222.0  222.0
2  333.0  333.0  333.0
3  444.0    NaN    NaN
4  555.0  555.0    NaN
5    NaN    NaN  666.0

Upvotes: 3

Related Questions