5

I have the following pandas data frame where I have NDVI value of 5 different points on different dates-

print (df) >>> PSC Intel FID Lat Lon 23-May 18-May 25-May 28-May 0 51.62 -63.81 -0.04 0.08 0.10 0.13 1 51.62 -63.80 -0.05 0.09 0.10 0.13 2 51.62 -63.80 -0.05 0.08 0.07 0.12 3 51.62 -63.80 -0.06 0.08 0.11 0.14 4 51.62 -63.80 -0.05 0.09 0.11 0.16

However, I need them converted where NDVI value will be stacked, and a new column will be there to indicate the data collection date. The required format is as follows - 

FID Lat Lon NVAL Date SAT 0 51.62 -63.81 -0.04 23-May PSC 1 51.62 -63.80 -0.05 23-May PSC 2 51.62 -63.80 -0.05 23-May PSC 3 51.62 -63.80 -0.06 23-May PSC 4 51.62 -63.80 -0.05 23-May PSC 0 51.62 -63.81 0.08 18-May PSC 1 51.62 -63.80 0.09 18-May PSC 2 51.62 -63.80 0.08 18-May PSC 3 51.62 -63.80 0.08 18-May PSC 4 51.62 -63.80 0.09 18-May PSC 0 51.62 -63.81 0.10 25-May Inter 1 51.62 -63.80 0.10 25-May Inter 2 51.62 -63.80 0.07 25-May Inter 3 51.62 -63.80 0.11 25-May Inter 4 51.62 -63.80 0.11 25-May Inter 0 51.62 -63.81 0.13 28-May Inter 1 51.62 -63.80 0.13 28-May Inter 2 51.62 -63.80 0.12 28-May Inter 3 51.62 -63.80 0.14 28-May Inter 4 51.62 -63.80 0.16 28-May Inter

Is there any way to do that using the pandas or any other library of python?

Improve this question

2 Answers 2

Reset to default
9

Setup:

from io import StringIO import pandas as pd df = pd.read_table(StringIO("""FID Lat Lon 23-May 18-May 25-May 28-May 0 51.62 -63.81 -0.04 0.08 0.10 0.13 1 51.62 -63.80 -0.05 0.09 0.10 0.13 2 51.62 -63.80 -0.05 0.08 0.07 0.12 3 51.62 -63.80 -0.06 0.08 0.11 0.14 4 51.62 -63.80 -0.05 0.09 0.11 0.16"""), sep='\s+')

Use melt:

df = pd.melt(df,id_vars=['FID','Lat','Lon'], var_name='Date', value_name='Value')

Output: df[['FID','Lat','Lon','Value','Date']]

 FID Lat Lon Value Date 0 0 51.62 -63.81 -0.04 23-May 1 1 51.62 -63.80 -0.05 23-May 2 2 51.62 -63.80 -0.05 23-May 3 3 51.62 -63.80 -0.06 23-May 4 4 51.62 -63.80 -0.05 23-May 5 0 51.62 -63.81 0.08 18-May 6 1 51.62 -63.80 0.09 18-May 7 2 51.62 -63.80 0.08 18-May 8 3 51.62 -63.80 0.08 18-May 9 4 51.62 -63.80 0.09 18-May 10 0 51.62 -63.81 0.10 25-May 11 1 51.62 -63.80 0.10 25-May 12 2 51.62 -63.80 0.07 25-May 13 3 51.62 -63.80 0.11 25-May 14 4 51.62 -63.80 0.11 25-May 15 0 51.62 -63.81 0.13 28-May 16 1 51.62 -63.80 0.13 28-May 17 2 51.62 -63.80 0.12 28-May 18 3 51.62 -63.80 0.14 28-May 19 4 51.62 -63.80 0.16 28-May
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

I have edited the question as it is coming with two level of indexes. Dates are now going inside the satellite name first. Can you please take a look at the data and suggest some solution?
Is that a pivot table now? If so, its not exactly clear to me what is the index, columns, values. Perhaps you could write something as I did in the "Setup" part of my answer, so that I could start with the same dataframe as you've printed.
6

Use the function stack()

#Creating DataFrame df=pd.DataFrame({'FID':[0,1,2,3,4],'Lat':[51.62,51.62,51.62,51.62,51.62],'Lon':[-63.81,-63.80,-63.80,-63.80,-63.80],'23-May':[-.04,-.05,-.05,-.06,-.05],'18-May':[0.08,0.09,0.08,0.08,0.09],'25-May':[.1,.1,.07,.11,.11],'28-May':[0.13,.13,.12,.14,.16]}) df=df[['FID','Lat','Lon','23-May','18-May','25-May','28-May']] df FID Lat Lon 23-May 18-May 25-May 28-May 0 0 51.62 -63.81 -0.04 0.08 0.10 0.13 1 1 51.62 -63.80 -0.05 0.09 0.10 0.13 2 2 51.62 -63.80 -0.05 0.08 0.07 0.12 3 3 51.62 -63.80 -0.06 0.08 0.11 0.14 4 4 51.62 -63.80 -0.05 0.09 0.11 0.16 df_stacked=df.set_index(['FID','Lat','Lon']).stack().reset_index() df_stacked=df_stacked.rename(columns={'level_3':'Date',0:'Value'}) df_stacked=df_stacked[['FID','Lat','Lon','Value','Date']] df_stacked FID Lat Lon Value Date 0 0 51.62 -63.81 -0.04 23-May 1 0 51.62 -63.81 0.08 18-May 2 0 51.62 -63.81 0.10 25-May 3 0 51.62 -63.81 0.13 28-May 4 1 51.62 -63.80 -0.05 23-May 5 1 51.62 -63.80 0.09 18-May 6 1 51.62 -63.80 0.10 25-May 7 1 51.62 -63.80 0.13 28-May 8 2 51.62 -63.80 -0.05 23-May 9 2 51.62 -63.80 0.08 18-May 10 2 51.62 -63.80 0.07 25-May 11 2 51.62 -63.80 0.12 28-May 12 3 51.62 -63.80 -0.06 23-May 13 3 51.62 -63.80 0.08 18-May 14 3 51.62 -63.80 0.11 25-May 15 3 51.62 -63.80 0.14 28-May 16 4 51.62 -63.80 -0.05 23-May 17 4 51.62 -63.80 0.09 18-May 18 4 51.62 -63.80 0.11 25-May 19 4 51.62 -63.80 0.16 28-May
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.