Search code examples
python-3.xpandasdataframereshape

Narrow to wide data

I have a following Data frame (df) as shown below:

df

Is it possible to reshape my (df) into (df1) as shown below:

df1

I have provided a sample of my code, but it does not work.

Appreciate any through or suggestion here.

Many Thanks

The sample of my code here:

data = {'Source': ['Rainfall', 'Inflow', 'SWCD', 'SWCD','Rainfall','Inflow', 'SP1', 'SP1'],
        'Target': ['SWCD', 'SWCD', 'Evp', 'Outflow','SP1','SP1', 'Evp', 'Overflow']}  
    
df = pd.DataFrame(data)
df1 = df.loc[:, ["Source", "Target"]]
df1 = df.values.flatten()
df.set_index('Target').T
Solution

  • Another, generic, option without a loop:

    vals = ['SWCD', 'SP1']

tmp = (df.mask(df['Target'].isin(vals))
         .fillna(df.rename(columns={'Target': 'Source', 'Source': 'Target'}))
       )
# or, if you only have Source/Target
# tmp = (df.mask(df['Target'].isin(vals))
#          .fillna(df.set_axis(['Target', 'Source'], axis=1))
#        )

out = pd.DataFrame(columns=pd.MultiIndex.from_frame(tmp))

    Output (as image as it's only a MultiIndex):

    enter image description here

    Alternative output:

    tmp.set_index('Source').T

    Output:

    Source      SWCD    SWCD SWCD     SWCD       SP1     SP1  SP1       SP1
Target  Rainfall  Inflow  Evp  Outflow  Rainfall  Inflow  Evp  Overflow