python - Is there a way to convert a time-series of rows (same feature, 5 rows [1 for each year]) into a single feature with 20 columns using Pandas?

Question

all. I'm sorry this is similar to other questions, but I can't find a good answer that matches my particular case.

I'm working with a time-series of image data. I have five rows of data for each feature in my dataframe: 1 row for each year. I'd like to convert these five rows into columns so that I just have on row per feature, but I can't lose or aggregate any of the data.

My dataframe looks like this:

df = pd.DataFrame([[1,      3,       4,       9,      31,    2016],
[1,     23,      12,      47,      3 ,    2017],
[1,      5,       7,      13,      48,    2018],
[1,     16,      11,       6,      39,    2019],
[1,     12,       2,      53,      26,    2020]], columns=['ID' , 'r-val' , 'b-val' , 'g-val' , 'ndvi' , 'year'])

ID | r-val | b-val | g-val | ndvi | year
1      3       4       9      31    2016
1     23      12      47      3     2017
1      5       7      13      48    2018
1     16      11       6      39    2019
1     12       2      53      26    2020  

I need it to look like this:

ID | r-val_2016 | b-val_2016 | g-val_2016 | ndvi_2016 | r-val_2017 | b-val_2017 | g-val_2017 | ndvi_2017 | r-val_2018 | b-val_2018 | g-val_2018 | ndvi_2018 ... and so on.
1       3            4           9             31           23          12           47             3           5          7           13             48  

I've tried concat, merge, and groupby, but I just can't seem to get the data in the shape that I need. Does anyone have any suggestions?

question from:https://stackoverflow.com/questions/65925336/is-there-a-way-to-convert-a-time-series-of-rows-same-feature-5-rows-1-for-eac

Answer 1

Use pivot

df = df.pivot(index='ID', columns=['year'], )
df.columns = ['_'.join(map(str,i)) for i in df.columns.values]