How to reshape pandas dataframe by column values?

user_12196625 0
Viewed 225

I have a dataframe with columns that include latitude, longitude, time, and data values. I would like to reshape it and transform it into an xarray dataarray such that the dimensions are time x lat/long pair but am not sure of the most efficient way to do this.

To make it concrete, the dataframe is structured as follows:

Index   Latitude    Longitude   Time    Data
0       1           2           1       1
1       2           4           1       2
2       1           2           2       3

I want the data to be reshaped such that it ends up as a matrix:

          Latitude 1/Longitude 2    Latitude 2/Longitude 4
Time 1    1                         2
Time 2    3                         Null

I’m currently doing this by taking a for loop over the unique lat/long combinations, saving each as an xarray, and then concatenating them over the lat/long dimension.

Are there any ways to more efficiency reshape the data?

python-xarray pandas python
2 Answers

Pivot is what you want, but first you need the new column names:

df['col'] = 'Latitude' + df['Latitude'].astype(str) + '/Longitude' + df.Longitude.astype(str)

df.pivot(index='Time', columns='col', values='Data')

Output:

col   Latitude1/Longitude2  Latitude2/Longitude4
Time                                            
1                      1.0                   2.0
2                      3.0                   NaN
user_42384080

This is just classic pivot table with customize on columns. As @QuangHoang provided pivot solution. Here is crosstab solution with flatten multiindex column afterwards

df1 = pd.crosstab(index=df.Time, columns=['Latitude '+df.Latitude.astype(str), 
                                          'Longitude '+df.Longitude.astype(str)], 
                                 values=df.Data, aggfunc='first')    

df1.columns = df1.columns.map('{0[0]}/{0[1]}'.format)

Out[382]:
      Latitude 1/Longitude 2  Latitude 2/Longitude 4
Time
1                        1.0                     2.0
2                        3.0                     NaN
user_101892140
Related