Assign column values to unique row in pandas dataframe [duplicate]

Question

I have the foll. dataframe:

AA          AB          AC                  AD              Col_1       Col_2     Col_3    
Northeast   Argentina   Northeast Argentina South America   Corrientes  Misiones        
Northern    Argentina   Northern  Argentina South America   Chaco       Formosa   Santiago Del

I want to convert it to:

AA          AB          AC                  AD              Col
Northeast   Argentina   Northeast Argentina South America   Corrientes
Northeast   Argentina   Northeast Argentina South America   Misiones        
Northern    Argentina   Northern  Argentina South America   Chaco
Northern    Argentina   Northern  Argentina South America   Formosa
Northern    Argentina   Northern  Argentina South America   Santiago Del

i.e. I want to preserve the first 4 columns but assign each of the remaining column values into a separate row. Is there a way to accomplish this without using a for loop?

oppressionslayer · Accepted Answer · 2019-12-21 05:26:06Z

3

You can try this:

df = df.melt(id_vars=['AA','AB','AC','AD']) 
df.dropna(inplace=True)
df.drop(columns='variable', inplace=True) 
df = df.sort_values('AA').reset_index(drop=True)
df.rename(columns={'value':'Col'}, inplace=True)

          AA         AB                   AC             AD           Col
0  Northeast  Argentina  Northeast Argentina  South America    Corrientes
1  Northeast  Argentina  Northeast Argentina  South America      Misiones
2   Northern  Argentina   Northern Argentina  South America         Chaco
3   Northern  Argentina   Northern Argentina  South America       Formosa
4   Northern  Argentina   Northern Argentina  South America  Santiago Del

edited Dec 21, 2019 at 5:26

answered Dec 21, 2019 at 5:20

oppressionslayer

7,2242 gold badges11 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

jezrael Over a year ago

You can improve answer with not use inplace=True - check this

jezrael Over a year ago

No, I am not pandas dev, but more times see this opinion and agree with it. I think it is more recommendation, check also this

oppressionslayer Over a year ago

Thanks, this helps me. I appreciate you taking the time to help me become a better pandas expert.

U13-Forward · Accepted Answer · 2019-12-21 05:24:32Z

1

Try using:

df['Col'] = df[['Col_1', 'Col_2', 'Col_3']].values.tolist()
df = df.set_index(df.columns.drop('Col').tolist())['Col'].apply(pd.Series).stack().reset_index().rename(columns={0: 'Col'}).drop(['level_7', 'Col_1', 'Col_2', 'Col_3'], axis=1)
print(df)

Output:

          AA         AB                   AC             AD           Col
0  Northeast  Argentina  Northeast Argentina  South America    Corrientes
1  Northeast  Argentina  Northeast Argentina  South America      Misiones
2   Northern  Argentina   Northern Argentina  South America         Chaco
3   Northern  Argentina   Northern Argentina  South America       Formosa
4   Northern  Argentina   Northern Argentina  South America  Santiago Del

answered Dec 21, 2019 at 5:24

U13-Forward

71.8k15 gold badges100 silver badges125 bronze badges

Collectives™ on Stack Overflow

Assign column values to unique row in pandas dataframe [duplicate]

2 Answers 2

3 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Linked

Related