[FIXED] Extracting from a 2D dataframe and adding value to 1D dataframe based on value locations in the 2D dataframe using spyder

Issue

I have two dataframes

df1 = pd.DataFrame({
    1: {'A': 237, 'B': 435, 'C': 900},
    2: {'A': 543, 'B': 313, 'C': 1200},
    3: {'A': 300, 'B': 150, 'C': 1600},
    4: {'A': 256, 'B': 635, 'C': 900},
    5: {'A': 343, 'B': 847, 'C': 1200},
    6: {'A': 122, 'B': 321, 'C': 1600}
})
df2 = pd.DataFrame({'well': ['A1', 'A2', 'A3', 'B1', 'B2', 'B3']})

I want to create a new dataframe:

df3 = pd.DataFrame({
    'value': {'A1': 237, 'A2': 543, 'A3': 300, 'A4': 256,
              'A5': 343, 'A6': 122, 'B1': 435, 'B2': 313,
              'B3': 150, 'B4': 635, 'B5': 847, 'B6': 321},
    'Time': {'A1': 900, 'A2': 1200, 'A3': 1600, 'A4': 900,
             'A5': 1200, 'A6': 1600, 'B1': 900, 'B2': 1200,
             'B3': 1600, 'B4': 900, 'B5': 1200, 'B6': 1600}
})

My actual dataframes are bigger than this and will always have different values, so I would like a way to streamline this so that it can work on any dataframe of the same format.

Solution

Let's try T + melt to go to long-form, then compressing the index with Index.map:

df3 = (
    df1.T.melt(id_vars='C', ignore_index=False)  # Long Form saving C
        .set_index('variable', append=True)  # Add variable to the index
        .rename(columns={'C': 'Time'})  # Rename C column to time
        .reindex(columns=['value', 'Time'])  # re-order columns
)
df3.index = df3.index.swaplevel(0, 1).map(lambda s: ''.join(map(str, s)))

df3:

    value  Time
A1    237   900
A2    543  1200
A3    300  1600
A4    256   900
A5    343  1200
A6    122  1600
B1    435   900
B2    313  1200
B3    150  1600
B4    635   900
B5    847  1200
B6    321  1600

Answered By - Henry Ecker