Pandas: how to do row in df2 = column in df1 properly?

Question

I have the following dataframe df1:

   A        B     C    D
0  case 1   1950  1.1  0
1  case 1   1951  1.3  0
2  case 1   1952  1.7  0
3  case 2   1950  1.9  0
4  case 2   1951  1.2  0
5  case 2   1952  1.4  0

I want to generate a dataframe df2 like this:

  case    1950  1951  1952
C case 1  1.1   1.3   1.7
D case 1  0     0     0
C case 2  1.9   1.2   1.4
D case 2  0     0     0

This is my attempt:

df2=pd.DataFrame() #Empty final dataframe, "transposed"
cases=['case 1', 'case 2']

for i,s in enumerate(cases): #Iterate over scenario names

    column_c=df1['C'][0+(2*i):2+(2*i)] #Identify the column C series from df1
    column_c_t=column_c.transpose() #Transpose series

    temp=pd.DataFrame({'Case':s}, index=['C','D']) #Empty temp dataframe
    for k,j in enumerate(range(1950,1953)): #Range of years as columns
        temp.insert(loc=k+1,column=str(j),value=0) #Add columns with years with initial value=0

    for index, row in df1.iterrows(): #Iterate over the original dataframe
        temp.loc["C":"C",1950:1952]=column_c_t
        temp.loc["D":"D",1950:1952]=0

    df2=df2.append(temp)

This fails as Python returns

ValueError                                Traceback (most recent call last)
<ipython-input-66-f175a2667647> in <module>()
     11 
     12     for index, row in ebsd3.iterrows(): #Iterate over the original dataframe
---> 13         temp.loc["C":"C",1950:1952]=column_c_t
     14         temp.loc["D":"D",1950:1952]=0
     15 

~\AppData\Local\conda\conda\envs\my_root\lib\site-packages\pandas\core\indexing.py in __setitem__(self, key, value)
    192             key = com._apply_if_callable(key, self.obj)
    193         indexer = self._get_setitem_indexer(key)
--> 194         self._setitem_with_indexer(indexer, value)
    195 
    196     def _has_valid_type(self, k, axis):

~\AppData\Local\conda\conda\envs\my_root\lib\site-packages\pandas\core\indexing.py in _setitem_with_indexer(self, indexer, value)
    597 
    598                     if len(labels) != len(value):
--> 599                         raise ValueError('Must have equal len keys and value '
    600                                          'when setting with an iterable')
    601 

ValueError: Must have equal len keys and value when setting with an iterable

I believe what I am doing wrong is assigning the column_c_t series of df1 to row C of df2. Any insight would be much appreciated.

score 2 · Accepted Answer · answered Jun 25 '18 at 10:23

2

melt + pivot_table

You should look for a vectorised solution. Here's one way using pd.melt + pd.pivot_table.

res = pd.melt(df, id_vars=['A', 'B'], value_vars=['C', 'D'])\
        .pivot_table(index=['variable', 'A'], columns='B',
                     values=['variable'], aggfunc='sum')\
        .reset_index().sort_values(['A', 'variable'])

res.columns = res.columns.droplevel()

print(res)

B            1950  1951  1952
0  C  case1   1.1   1.3   1.7
2  D  case1   0.0   0.0   0.0
1  C  case2   1.9   1.2   1.4
3  D  case2   0.0   0.0   0.0

answered Jun 25 '18 at 10:23

jpp

159,742
34
281
339

I take `variable` is your way of using a dummy name for my variables at hand. Correct? – FaCoffee Jun 25 '18 at 10:27
Pandas creates a `variable` column as part of `melt`. You can split apart each step to see what's happening (the beauty of chaining). – jpp Jun 25 '18 at 10:28

Pandas: how to do row in df2 = column in df1 properly?

1 Answers1

melt + pivot_table