CODEWITHSUNDEEP
pythonpandasdataframe
coding
coding

Reputation: 1207

How to stack groups of columns in and pandas dataframe?

I am not sure how to do this, but I have a data frame like this one,

State   Homicides  State2   Homicides2
-----------------------------------------
Cal       1         Mas         5
Tex       2         NY          6
Tenn      3         Chi         7 
Pen       4         Mon         8

I would like to append below "State" and "Homicides" the columns "State2" and "Homicides2"

State   Homicides  
------------------
Cal       1         
Tex       2        
Tenn      3         
Pen       4         
Mas       5
NY        6
Chi       7 
Mon       8

I tried with unlist and stack but I don't know how to do it for multiple columns, Thanks!

Upvotes: 1

Views: 633

Answers (4)

Scott Boston
Scott Boston

Reputation: 153460

Let's use pd.wide_to_long to handle this simultaneous melting situation.

First we need to rename a column headers to create a format for columns to have common "stubs".

# Here we are adding '1' on the end of columns without the number 2 on thend
df = df.rename(columns=lambda x: x+'1' if x[-1] != '2' else x)

# Now, let's reshape using pd.wide_to_long
pd.wide_to_long(df.reset_index(), ['State', 'Homicides'], 'index', 'No').reset_index(level=1, drop=True)

Ouptut:

      State  Homicides
index                 
0       Cal        1.0
1       Tex        2.0
2      Tenn        3.0
3       Pen        4.0
0       Mas        5.0
1        NY        6.0
2       Chi        7.0
3       Mon        8.0

Upvotes: 1

Trenton McKinney
Trenton McKinney

Reputation: 62393

  • One way is to use pandas.concat, to combine the two sets of columns
    • This will stack columns with the same name.
  • Use .iloc to select the groups.
    • .iloc was used because it seems easier to select adjacent groups of columns.
    • Alternatively, select the columns by name (e.g. df[['State','Homicides']])
  • The trick is to .rename the columns of the 2nd set, to match the names of the first set of columns.
import pandas as pd

# setup test dataframe
df = pd.DataFrame({'State': ['Cal', 'Tex', 'Tenn', 'Pen'], 'Homicides': [1, 2, 3, 4], 'State2': ['Mas', 'NY', 'Chi', 'Mon'], 'Homicides2': [5, 6, 7, 8]})

# concat the 2 sets of columns
df = pd.concat([df.iloc[:, 0:2], df.iloc[:, 2:4].rename(columns={'State2': 'State', 'Homicides2': 'Homicides'})]).reset_index()

display(df)

  State  Homicides
0   Cal          1
1   Tex          2
2  Tenn          3
3   Pen          4
4   Mas          5
5    NY          6
6   Chi          7
7   Mon          8

Upvotes: 0

G. Anderson
G. Anderson

Reputation: 5955

You can use melt() to stack the columns by name

df.melt(['State','State2'])

    State   State2  variable    value
0   Cal Mas Homicides   1
1   Tex NY  Homicides   2
2   Tenn    Chi Homicides   3
3   Pen Mon Homicides   4
4   Cal Mas Homicides2  5
5   Tex NY  Homicides2  6
6   Tenn    Chi Homicides2  7
7   Pen Mon Homicides2  8

Include drop and rename to remove the unneeded columns and fix the naming

df.melt(['State','State2']).drop(['State2','variable'], axis=1).rename({'value':'Homicides'}, axis=1)

    State   Homicides
0   Cal     1
1   Tex     2
2   Tenn    3
3   Pen     4
4   Cal     5
5   Tex     6
6   Tenn    7
7   Pen     8

Upvotes: 1

IoaTzimas
IoaTzimas

Reputation: 10624

You can concat the columns you want:

result=pd.concat([df[['States','Homicides']], df[['States2','Homicides2']]])

Upvotes: 0

Related Questions