Data manipulation- Python

Question

Here is my dataset:

    COL_1    COL_2    COL_2   COL_4   COL_4  COL_8    COL_9
    A         col2    col3    col4    col5
    B         col2    col3    col4    col5
    C         col2    col3    col4    col5

I need to move pairs of columns (COL_2 , COL_3 and then COL_4, COL_5) to COL_8 and COL_9 and copy the content of the 1st column (COL_1) as well as put the names of columns (COL_2, COL_4) to the other column (COL_10) this way:

   COL_1  COL_8   COL_9   COL10
   A       col2   col3    COL_2
   B       col2   col3    COL_2
   C       col2   col3    COL_2
   A       col4   col5    COL_4
   B       col4   col5    COL_4
   C       col4   col5    COL_4

Please note that some of column names in initial dataset are identical. How can I do it using Python?

When you say move Col_2 to Col_8, Col_3 to Col_9 What happens to Coll_1 and Col_2, do they remain or are they to be removed? Please clarify what you mean by "copy the content of the 1st column (COL_1) as well as put the names of columns (COL_2, COL_4) to the other column (COL_10)" — itprorh66
– itprorh66, Commented Jan 29, 2022 at 16:34
COL_1 remains, COL_2 as well as COL_4 should be removed. By "copy the content of the 1st column (COL_1)" I mean it should be copied and pasted in the COL_1 as you can see in the output in my question. By "put the names of columns (COL_2, COL_4) to the other column (COL_10)" I mean names of those columns should appear in the COL_10. Did my answer shed more light on the issue? — anla01
– anla01, Commented Jan 30, 2022 at 0:54
Names of columns COL_2 and COL_4 should be visible in the column COL_10 in the rows that are corresponding to the content of those columns in the new COL_8, COL_9. I know it may sound confusing. Please try to compare what I wrote with the output I placed in my question — anla01
– anla01, Commented Jan 30, 2022 at 1:02

Giulio Mattolin · Accepted Answer · 2022-01-31 08:24:19Z

1

To achieve the illustrated result you could use the function concat of pandas and create the new dataframe's column like this:

new_df = pd.DataFrame()

new_df['COL_1'] = pd.concat([df['COL_1'], df['COL_1']])
new_df['COL_8'] = pd.concat([df['COL_2'], df['COL_4']])
new_df['COL_9'] = pd.concat([df['COL_3'], df['COL_5']])
new_df['COL_10'] = pd.concat([pd.DataFrame(np.repeat('COL_2', len(df))), pd.DataFrame(np.repeat('COL_4', len(df)))])

>>> new_df

  COL_1  COL_8   COL_9   COL_10
0     A   col2    col3    COL_2
1     B   col2    col3    COL_2
2     C   col2    col3    COL_2
0     A   col4    col5    COL_4
1     B   col4    col5    COL_4
2     C   col4    col5    COL_4

edited Jan 31, 2022 at 8:24

answered Jan 29, 2022 at 16:45

Giulio Mattolin

7501 gold badge4 silver badges17 bronze badges

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

4 Comments

anla01 Over a year ago

Unfortunately, this idea will not work, at least not for COL_10 since it's supposed to contain columns' names according to values of rows that are corresponding to them, which is COL_2 for rows with values col2 and col3 in columns COL_8 and COL_9, but thank you for you answer anyway.

Giulio Mattolin Over a year ago

I've updated the answer changing the construction of COL_10. Does it work for you or you though about something different?

anla01 Over a year ago

Yes. The current output of COL_10 is exactly the one I was looking for. Thanks a million!

Giulio Mattolin Over a year ago

You're welcome! If this is what you were looking for you can select it as the solution of your question by clicking on the check mark beside the answer :)

user17242583 · Accepted Answer · 2022-01-29 16:47:15Z

0

This should work:

part1 = df[['COL_1', 'COL_2']].set_axis([0,1,2], axis=1)
part2 = df[['COL_1', 'COL_4']].set_axis([0,1,2], axis=1)
new_df = pd.concat([part1, part2], ignore_index=True)
new_df[3] = new_df[1]
new_df.columns = ['COL_1', 'COL_8', 'COL_9', 'COL_10']

Output:

>>> new_df
  COL_1 COL_8 COL_9 COL_10
0     A  col2  col3   col2
1     B  col2  col3   col2
2     C  col2  col3   col2
3     A  col4  col5   col4
4     B  col4  col5   col4
5     C  col4  col5   col4

answered Jan 29, 2022 at 16:47

user17242583

1 Comment

anla01 Over a year ago

Thank you for your answer. It is helpful indeed. However, the aim was to insert the names of columns COL_2 and COL_4 into COL_10 and there are values in the solution of yours. Values col2 and col3 are initially in columns named COL_2, so currently COL_10 should contain COL_2 (which is a name of columns) in the corresponding rows and so on. Do you know what can be done to achieve this result?

Collectives™ on Stack Overflow

Data manipulation- Python

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related