Turning 4 columns into 2

user_13500938 0
Viewed 65

I have a csv with 4 columns and put it into pandas

username   likes  username2   likes2

Mill       2      Tim        10
John       3      Kim         2 
Tye        7      Rory        4
Clyde      8      Tann        9
Ellie      2      Ken         2

I am trying to:

  1. Add username2 below username
  2. Add likes2 below likes

Desired Output

username   likes 

Mill       2      
John       3      
Tye        7      
Clyde      8     
Ellie      2     
Tim        10
Kim        2 
Rory       4
Tann       9
Ken        2
pandas python
5 Answers

Try the following code:

  • I know, there might be more concise way to do, this worked for me.
df1 = df['username'].append(df['username2']).reset_index(drop=True)
df2 = df['likes'].append(df['likes2']).reset_index(drop=True)
result = pd.concat([df1, df2], axis=1)
user_27721180

You need top .melt()

 pd.melt(df, id_vars=['username'], value_vars=['likes', 'likes2'],value_name='likes').drop(columns=['variable'])

enter image description here

user_89869750

A dirty one-liner:

res = pd.concat([df[['username', 'likes']], df[['username2', 'likes2']].rename(columns={'username2': 'username', 'likes2': 'likes'})])
user_85395330

A detailed way of Approach !!! Am not sure how many of you will like this. 

first_df = df2.iloc[:,:2]
second_df = df2.iloc[:,2:]
a = []
for each in first_df.columns:
    a.append(each)
second_df.columns = a
Result = first_df.append(second_df)
user_85793540

One way about it is reshaping the underlying numpy array and creating a new dataframe. Get the total number of entries in the dataframe, and divide by 2 (since you are aiming for only two columns) to get the number of rows in the final shape.

res = (pd.DataFrame(np.reshape(df.to_numpy(),(df.size//2,2))
       ,columns=['username','likes'])
       )

    username    likes
0       Mill    2
1       Tim     10
2       John    3
3       Kim     2
4       Tye     7
5       Rory    4
6       Clyde   8
7       Tann    9
8       Ellie   2
9       Ken     2

Update

The data has a defined pattern (some columns start with username, others start with like). As such, you can use the pivot_longer function from pyjanitor

# pip install pyjanitor
import janitor
import pandas as pd
df.pivot_longer(names_to=("username", "likes"), 
                names_pattern=("^username", "likes"))

username    likes
0   Mill    2
1   John    3
2   Tye     7
3   Clyde   8
4   Ellie   2
5   Tim     10
6   Kim     2
7   Rory    4
8   Tann    9
9   Ken     2
user_71757130
Related