Merging columns using pandas-CodePudding

I am trying to merge multiple-choice question columns using pandas so I can then manipulate them. An example of what my questions look like is:

  C1 C2 C3
0  A    A
1    B  B
2    C  C
3  D    D

The data is currently presented as C1 and C2 but I need it to be combined into 1 column as represented in C3.

CodePudding user response：

One option, assuming NaN in empty cells, is to bfill the first column and copy it:

df['C3'] = df[['C1', 'C2']].bfill(axis=1)['C1']

This way is extensible to any number of initial columns.

Output:

    C1   C2 C3
0    A  NaN  A
1  NaN    B  B
2  NaN    C  C
3    D  NaN  D

CodePudding user response：

You may try with fillna

df['C3'] = df['C1'].fillna(df['C2'])
df
Out[483]: 
    C1   C2 C3
0    A  NaN  A
1  NaN    B  B
2  NaN    C  C
3    D  NaN  D

CodePudding user response：

You can also use combine_first:

df['C3'] = df['C1'].combine_first(df['C2'])
print(df)

# Output
    C1   C2 C3
0    A  NaN  A
1  NaN    B  B
2  NaN    C  C
3    D  NaN  D

If your cells contain empty strings and not null values, replace them temporary by NaN:

df['C3'] = df['C1'].replace('', np.nan).combine_first(df['C2'])
print(df)

# Output
  C1 C2 C3
0  A     A
1     B  B
2     C  C
3  D     D