Pandas: update a crosstab table from a dataframe

Question

Source Dataframe:

            T1  V1  T2  V2  T3  V3
4/1/2023    A1  10  A4  8   A2  1
4/2/2023    A2  5   A3  10  A1  7
4/3/2023    A3  7   A1  1   A4  9

Target Dataframe:

            A3  A2  A4  A1
4/1/2023    0   1   8   10
4/2/2023    10  5   0   7
4/3/2023    7   0   9   1

I achieved the target dataframe through a loop:

for idx in df1.index:
    for col in df1.columns:
        lst = list(df.loc[idx].values)
        val = (lst[lst.index(col)+1] if col in lst else 0)
        df1.loc[idx, col] = val

Is there any better / direct method like merge() / concat() to accomplish this without the need to loop?

Answer 1

You can use pd.wide_to_long:

out = (pd.wide_to_long(df.reset_index(names='Date'), ['T', 'V'], i='Date', j='var')
         .droplevel('var').set_index('T', append=True)['V']
         .unstack('T', fill_value=0).rename_axis(index=None, columns=None))

Or using pd.concat:

out = (pd.concat([pd.DataFrame(d.values, index=d.index, columns=['T', 'V']) 
                  for _, d in df.groupby(df.columns.str[1:], axis=1)])
         .set_index('T', append=True)['V'].unstack('T', fill_value=0)
         .rename_axis(columns=None))

Output:

          A1  A2  A3  A4
4/1/2023  10   1   0   8
4/2/2023   7   5  10   0
4/3/2023   1   0   7   9