Combining two columns with the same name in a df

user_8763752 0
Viewed 29

I have two Id columns in my df which are split into either a Id or NaN.

Date ... ID ID
12/12/2019 ... DE1253 NaN
12/12/2018 ... eg562r NaN
12/12/2021 ... gse233 NaN
12/12/2019 ... NaN wefg32
12/11/2010 ... NaN rte422
12/10/2021 ... NaN 3fdes4

How can I combine these two fields into one ignores the NaN so that there the desired output is

Date ... ID
12/12/2019 ... DE1253
12/12/2018 ... eg562r
12/12/2021 ... gse233
12/12/2019 ... wefg32
12/11/2010 ... rte422
12/10/2021 ... 3fdes4

Any help would be much appreciated thanks

pandas python
3 Answers

Just try with groupby

out = df.groupby(level=0,axis=1).first()
user_79645270

If the columns to be merge have identical names and non overlapping data:

out = df.stack().unstack()

Or, to keep the original order of the columns:

df.stack().unstack()[df.columns.unique()]

output:

         Date  ...      ID
0  12/12/2019  ...  DE1253
1  12/12/2018  ...  eg562r
2  12/12/2021  ...  gse233
3  12/12/2019  ...  wefg32
4  12/11/2010  ...  rte422
5  12/10/2021  ...  3fdes4
user_163434640

Another shorter solution. My ID column names are ID1 and ID2. For some reason I can't create 2 column with same name. Also, you can drop ID1 and ID2. I kept them for completeness.

df = pd.DataFrame({'dates': ["12/12/2019", "12/12/2018", "12/12/2021", "12/12/2019",
                                 "12/11/2010", "12/10/2021"],
                       'ID1': ['DE1253', 'eg562r', 'gse233', np.NaN,np.NaN,np.NaN],
                       'ID2': [np.NaN,np.NaN,np.NaN,'wefg32', 'rte422', '3fdes4']})
    df['ID'] = df.ID1.combine_first(df.ID2)
    print(df)



dates     ID1     ID2      ID
0  12/12/2019  DE1253     NaN  DE1253
1  12/12/2018  eg562r     NaN  eg562r
2  12/12/2021  gse233     NaN  gse233
3  12/12/2019     NaN  wefg32  wefg32
4  12/11/2010     NaN  rte422  rte422
5  12/10/2021     NaN  3fdes4  3fdes4
user_193461010
Related