Create panel data from a dataframe

user_14675230 0
Viewed 647

I couldn't find a solution to my problem here and hope you could solve it. Here is my current dataframe:

| Index | Col_1_data | Col_2_data | Col_3_data | Col_1_new_data | Col_2_new_data | Col_3_new_data |
|   1   |     a      |     d      |     g      |     j          |     m          |     p          |
|   2   |     b      |     e      |     h      |     k          |     n          |     q          |
|   3   |     c      |     f      |     i      |     l          |     o          |     r          |

And the idea is to melt it so that it will look like this:

| Index | data | new_data | id_col |
|   1   |    a |     j    | Col_1  |   
|   2   |    b |     k    | Col_1  |  
|   3   |    c |     l    | Col_1  |
|   1   |    d |     m    | Col_2  |
|   2   |    e |     n    | Col_2  |
|   3   |    f |     o    | Col_2  |
....etc....

So make it a panel but without any ID column, rather a partial string that is the same per each column (e.g. Col_1, Col_2, etc.) Is there a way to do it 'pythonically' or do I need to loop and stack?

dataframe panel python
2 Answers

Here's one way of doing it, but perhaps there's a more efficient way.

What you could do is split your dataset into 2 parts, based on whether the column is 'new', and then use pd.melt with pd.concat.

# Create an index column (if not exists already)
df['index']=np.arange(len(df))+1

# melt both datasets and concat together
out = pd.concat([pd.melt(pd.concat([df.filter(like='new'),df[['index']]],axis=1),
                         id_vars='index',var_name=['id_col']).rename({'value':'data'},
                                                                     axis=1).drop(['id_col'],axis=1),
                 pd.melt(df[[i for i in df.columns if 'new' in i or 'index' in i]],
                         id_vars='index',var_name=['id_col']).rename({'value':'new_data'},
                                                                     axis=1)],
                axis=1)

# Remove the '_new_data' from the 'id_col'
out['id_col'] = out['id_col'].str.replace('_new_data','')

# Rearrange the columns to match your output
out = out[['index','data','new_data','id_col']]

Prints:

   index data new_data id_col
0      1    a        j  Col_1
1      2    b        k  Col_1
2      3    c        l  Col_1
3      1    d        m  Col_2
4      2    e        n  Col_2
5      3    f        o  Col_2
6      1    g        p  Col_3
7      2    h        q  Col_3
8      3    i        r  Col_3

The last two steps are only needed to reach precisely your desired outcome.

user_91673820

Another not so good way to doing this -

import pandas as pd
import numpy as np

df= pd.read_csv('test.csv')

col1 = ['Index', 'Col_1_data', 'Col_2_data', 'Col_3_data']
col2 =  ['Index', 'Col_1_new_data', 'Col_2_new_data', 'Col_3_new_data']
df1  = df[col1].transpose()
df2 = df[col2]
df2 = df2.rename(columns= dict(zip(col2,col1))).transpose()
def update_header(df):
    new_header = df.iloc[0] #grab the first row for the header
    df = df[1:] #take the data less the header row
    df.columns = new_header 
    return df
    
df1 = update_header(df1)
df2 = update_header(df2)
df1['data'] = df1[df1.columns.values].values.tolist()
df2['new_data'] = df2[df2.columns.values].values.tolist()
merged_df = df1.merge(df2,left_index=True, right_index=True)
merged_df = merged_df[['data', 'new_data']]
final_df = merged_df.apply(pd.Series.explode).reset_index()

output-

Index   index   data    new_data
0   Col_1_data  a   j
1   Col_1_data  b   k
2   Col_1_data  c   l
3   Col_2_data  d   m
4   Col_2_data  e   n
5   Col_2_data  f   o
6   Col_3_data  g   p
7   Col_3_data  h   q
8   Col_3_data  i   NaN
user_154380330
Related