PANDAS - Rename and combine like columns

user_16178528 0
Viewed 170

I am trying to rename a column and combine that renamed column to others like it. The row indexes will not be the same (i.e. I am not combining 'City' and 'State' from two columns).

df = pd.DataFrame({'Col_1': ['A', 'B', 'C'],
                   'Col_2': ['D', 'E', 'F'],
                   'Col_one':['G', 'H', 'I'],})

df.rename(columns={'Col_one' : 'Col_1'}, inplace=True)

# Desired output: 

({'Col_1': ['A', 'B', 'C', 'G', 'H', 'I'],
  'Col_2': ['D', 'E', 'F', '-', '-', '-'],})

I've tried pd.concat and a few other things, but it fails to combine the columns in a way I'm expecting. Thank you!

pandas rename dataframe concatenation python
4 Answers

This is melt and pivot after you have renamed:

u = df.melt()
out = (u.assign(k=u.groupby("variable").cumcount())
        .pivot("k","variable","value").fillna('-'))

out = out.rename_axis(index=None,columns=None)

print(out)

  Col_1 Col_2
0     A     D
1     B     E
2     C     F
3     G     -
4     H     -
5     I     -
user_98406370

Using append without modifying the actual dataframe:

result = (df[['Col_1', 'Col_2']]
          .append(df[['Col_one']]
          .rename(columns={'Col_one': 'Col_1'}),ignore_index=True).fillna('-')
          )

OUTPUT:

  Col_1 Col_2
0     A     D
1     B     E
2     C     F
3     G     -
4     H     -
5     I     -
user_91363480

Might be a slightly longer method than other answers but the below delivered the required output.

    df = pd.DataFrame({'Col_1': ['A', 'B', 'C'],
                       'Col_2': ['D', 'E', 'F'],
                       'Col_one':['G', 'H', 'I'],})

    # Create a list of the values we want to retain
    TempList = df['Col_one']

    # Append existing dataframe with the values from the list
    df = df.append(pd.DataFrame({'Col_1':TempList}), ignore_index = True)

    # Drop the redundant column
    df.drop(columns=['Col_one'], inplace=True)

    # Populate NaN with -
    df.fillna('-', inplace=True)

Output is

    Col_1 Col_2
    0     A     D
    1     B     E
    2     C     F
    3     G     -
    4     H     -
    5     I     -
user_161787330

Using concat should work.

import pandas as pd

df = pd.DataFrame({'Col_1': ['A', 'B', 'C'],
                   'Col_2': ['D', 'E', 'F'],
                   'Col_one':['G', 'H', 'I'],})

df2 = pd.DataFrame()

df2['Col_1'] = pd.concat([df['Col_1'], df['Col_one']], axis = 0)

df2 = df2.reset_index()

df2 = df2.drop('index', axis =1)

df2['Col_2'] = df['Col_2']

df2['Col_2'] = df2['Col_2'].fillna('-')

print(df2)

prints

  Col_1 Col_2
0     A     D
1     B     E
2     C     F
3     G     -
4     H     -
5     I     -
user_43523170
Related