[FIXED] How to append dependent columns into rows?

Issue

The code to reproduce data is

 data = {
    'datetime': ['2019-01-01 08:03:00', '2019-01-01 08:04:00', '2019-01-01 08:04:00'],
    'own_mmsi': [236385000, 236385000, 244013009],
    'target_mmsi': [244013009, 244013009, 236385000],
    'own_lat': [59.330711, 59.330400, 59.327970],
    'own_lon': [10.515833, 10.514336, 10.515833],
    'target_lat': [59.327478, 59.327970, 59.330400],
    'target_lon': [10.515475, 10.515833, 10.514336],
    
}

An image of the sample df :

I wanted to get the target_lat and target_lon into lat lon columns and own_mmsi and target_mmsi into mmsi column which are dependent on datetime as each rows for entire df.

This is the desired first 2 rows of the output.

datetime	mmsi	lat	lon
2019-01-01 08:03:00	236385000	59.330711	10.515833
2019-01-01 08:03:00	244013009	59.327478	10.515475
2019-01-01 08:04:00	236385000	59.330400	10.514336

I tried to merge the df with melt and join logics and merge does no work wihtin the same df.

melted_df = pd.melt(
    df,
    id_vars=['datetime', 'own_mmsi'],
    value_vars=['target_mmsi', 'target_lat', 'target_lon'],
    var_name='attribute',
    value_name='value'
)

reshaped_df = melted_df.pivot_table(
    index=['datetime', 'own_mmsi'],
    columns='attribute',
    values='value',
    aggfunc='first'
).reset_index()
   
reshaped_df.columns.name = None  # Remove the name of the columns index
reshaped_df = reshaped_df.rename(columns={'target_mmsi': 'own_target_mmsi', 'target_lat': 'own_target_lat', 'target_lon': 'own_target_lon'})

print(reshaped_df)

Solution

IIUC, you are looking for pd.wide_to_long:

df.columns = df.columns.str.split('_').map(lambda x : '_'.join(x[::-1]))
out = (pd.wide_to_long(df.reset_index(), stubnames=['mmsi', 'lat', 'lon'],
                                         i=['index', 'datetime'], j='var',
                                         sep='_', suffix='\w+')
         .reset_index('datetime').reset_index(drop=True))

Output:

>>> out
              datetime       mmsi        lat        lon
0  2019-01-01 08:03:00  236385000  59.330711  10.515833
1  2019-01-01 08:03:00  244013009  59.327478  10.515475
2  2019-01-01 08:04:00  236385000  59.330400  10.514336
3  2019-01-01 08:04:00  244013009  59.327970  10.515833
4  2019-01-01 08:04:00  244013009  59.327970  10.515833
5  2019-01-01 08:04:00  236385000  59.330400  10.514336

Answered By - Corralien