CODEWITHSUNDEEP
pythonpandasdataframe
alosc
alosc

Reputation: 63

Creating a dataframe from monthly values which dont start on january

So, i have some data in list form, such as:

Q=[2,3,4,5,6,7,8,9,10,11,12]                                     #values
M=[11,0,1,2,3,4,5,6,7,8,9]                                       #months
Y=[2010,2011,2011,2011,2011,2011,2011,2011,2011,2011,2011]       #years

And i want to get a dataframe, with one row per year, and one column per month, adding the data of Q on the positions given by M and Y.

so far i have tried a couple of things, my current code is as follows:

def save_data(data_list,year_info,month_info):
    
    #how many datapoints
    n_data=len(data_list)
    
    #how many years
    y0=year_info[0]
    yf=year_info[n_data-1]
    n_years=yf-y0+1
    
    #creating the list i want to fill out
    df_list=[[math.nan]*12]*n_years
    
    ind=0
    for y in range(n_years):
        for m in range(12):
            if ind<len(data_list):
                if year_info[ind]-y0==y and month_info[ind]==m:
                    df_list[y][m]=data_list[ind]
                    ind+=1
    
        
    df=pd.DataFrame(df_list)
    
    return df

I get this output:

0 1 2 3 4 5 6 7 8 9 10 11
0 3 4 5 6 7 8 9 10 11 12 nan 2
1 3 4 5 6 7 8 9 10 11 12 nan 2

And i want to get:

0 1 2 3 4 5 6 7 8 9 10 11
0 nan nan nan nan nan nan nan nan nan nan nan 2
1 3 4 5 6 7 8 9 10 11 12 nan nan

I have tried doing a bunch of diferent things, but so far nothing has worked, I'm wondering if there's a more straightforward way of doing this, my code seems to be overwriting in a weird way, i do not know for instance why is there a 2 on the last value of second row, since that's the first value of my list.

Thanks in advance!

Upvotes: 1

Views: 29

Answers (1)

Quang Hoang
Quang Hoang

Reputation: 150735

Try pivot:

(pd.DataFrame({'Y':Y,'M':M,'Q':Q})
  .pivot(index='Y', columns='M', values='Q')
)

Output:

M      0    1    2    3    4    5    6     7     8     9    11
Y                                                             
2010  NaN  NaN  NaN  NaN  NaN  NaN  NaN   NaN   NaN   NaN  2.0
2011  3.0  4.0  5.0  6.0  7.0  8.0  9.0  10.0  11.0  12.0  NaN

Upvotes: 2

Related Questions