title: New column by Slicing string from another column on transpose of a dataframe python pandas
body: <p>I am trying to slice a substring from a column and get it in another column. I have read multiple links but this problem exists because my dataframe that I will be working on is Transpose. (This is a minimum reproducible example, the original dataframe is huge and I have to work with the transposed data only).</p>
  
  <p><strong>df</strong>(How to generate df for this question, sample df,minimum reproducible eg.)</p>
  
  <pre><code>[IN]
  import pandas as pd
  df = pd.DataFrame({'SKU': ['BIKES','MATS', 'BLANKETS', 'CREAMS'],
                     'JAN15 SVAL': [1,3,6,10],
                     'FEB15 SVAL': [4,5, 6,16],
                  'MAR15 SVAL':[13, 14,10,12]})
  dft1 = df.set_index('SKU').T
  </code></pre>
  
  <p>Other info:</p>
  
  <pre><code>[IN]dft1.columns
  [OUT]Index(['BIKES', 'MATS', 'BLANKETS', 'CREAMS'], dtype='object', name='SKU')
  [IN]print(dft1.index.name)
  [OUT]None
  </code></pre>
  
  <p><strong>Expected output for dft1:</strong> </p>
  
  <pre><code>     SKU         BIKES  MATS  BLANKETS  CREAMS  month  year month_number  Date
  JAN15 SVAL      1     3         6      10        JAN   2015   1           2015-01-01
  FEB15 SVAL      4     5         6      16        FEB   2015   2           2015-02-01
  MAR15 SVAL     13    14        10      12        MAR   2015   3           2015-03-01
  </code></pre>
  
  <p><strong>Code tried so far</strong> which does not work and gives error as mentioned in [OUT]     </p>
  
  <pre><code>[IN]dft1['month'] = dft1['SKU'].apply(lambda x: x[:3])
  [IN]dft1['year']=2000 + dft1['SKU'].str[3:5].astype(int)
  [IN]cmap = {'JAN': 1, 'FEB': 2, 'MAR': 3, 'APR': 4, 'MAY': 5, 'JUN': 6,'JUL':7, 'AUG':8,'SEP':9,'OCT':10,'NOV':11,'DEC':12}
  [IN]dft1['Month Number']=dft1['month'].map(cmap)
  [IN]dft1['Month Number']=dft1['Month Number'].round(0)
  [IN]dft1['Date']=(pd.to_datetime(dft1['year'].astype(str)  + dft1['month'], format='%Y%b'))
  
  [OUT]KeyError: 'SKU'
  </code></pre>
  
  <p>P.S. <strong>I dont want to set the first column SKU as index</strong> <strong>to slice</strong> the data as there are other dataframes(transposed ones where the column from which data needs to be sliced from strings are not in the first position.</p>