How would I go about extracting data that is horizoantal and formatting vertiacally in python?

Question

I have a database that looks like this dataframe1:

identifier  Start      End       c1   g1    c2    g2    c3    g3
-----------------------------------------------------------------
24r       7/6/2009  7/27/2009   100    1    120    2    110   3
34e       8/6/2009  8/27/2009   200    5    180    4    190   1
34r      9/26/2009  10/17/2009  150         120   23    140   5

And I would like the output to be dataframe2

Week number week start  Week end    Identifier  cAmount
--------------------------------------------------------
    1        7/6/2009   7/13/2009      24r       100
    2       7/13/2009   7/20/2009      24r       120
    3       7/20/2009   7/27/2009      24r       110
    1        8/6/2009   8/13/2009      34e       200
    2       8/13/2009   8/20/2009      34e       180
    3       8/20/2009   8/27/2009      34e       190
    1       9/26/2009   10/3/2009      34r       150
    2       10/3/2009   10/10/2009     34r       120
    3      10/10/2009   10/17/2009     34r       140

I am asking this because I know this can be done using a forand various nested ifs but I wonder if there is a more efficient way of doing this like with a Pandas. So the idea is in simple terms I have a horizontal dataframe that has a start date and the weeks are not separated in columns. Rather each column has vertical information. So the idea I had is to move Column 1 to 3 close to each other and then do a transpose, but this seems rather inefficient because I have more then 3 columns. If anyone knows a good method for this I would appreciate a lot.

To clarify, Dataframe 1 will be the initial data, and Column Start Will be week 1 of every identifier in dataframe 2 'week start', in dataframe 2 'week end' will be 7 days after week start. 'week start' for week number 2 would be the same as 'week end 1'. Now for cAmount in dataframe 2. The first week value will come from dataframe 1 'c1' second week value will be from dataframe 1 'c2', and week 3 cAmount value will come from 'c3'.

A simple representation :

user_7175713 · Answer

Convert the date columns to datetime dtypes:

df.loc[:, ["Start", "End"]] = (df.loc[:, ["Start", "End"]]
                               .transform(pd.to_datetime, format="%m/%d/%Y"))

Let's reshape the data with pandas wide_to_long

result = (pd.wide_to_long(df, 
                          stubnames=["c", "g"], 
                          i=["identifier", "Start", "End"], 
                          j="num")
            .reset_index()
            .drop(columns="g")
            .rename(columns={"c": "cAmount", "num": "WeekNumber"})
             )

result

    
  identifier    Start   End     WeekNumber  cAmount
0   24r     2009-07-06  2009-07-27  1   100
1   24r     2009-07-06  2009-07-27  2   120
2   24r     2009-07-06  2009-07-27  3   110
3   34e     2009-08-06  2009-08-27  1   200
4   34e     2009-08-06  2009-08-27  2   180
5   34e     2009-08-06  2009-08-27  3   190
6   34r     2009-09-26  2009-10-17  1   150
7   34r     2009-09-26  2009-10-17  2   120
8   34r     2009-09-26  2009-10-17  3   140

Let's generate weekly start dates using values from Start and End columns:

date_range = (pd.date_range(start, end, freq="7D")[:3]
              for start, end 
              in pd.unique(list(zip(result.Start, result.End))))

The date range is chunked since we have 3 rows per identifier. Of course, you can adjust this manual part; just seemed the simplest.

Let's rebuild the dataframe with appropriate inputs:

from itertools import chain

result.assign(
    Start=list(chain.from_iterable(date_range)),
    End=lambda df: df.Start.add(pd.Timedelta("7 days")),
)
  
   identifier   Start   End     WeekNumber  cAmount
0   24r     2009-07-06  2009-07-13  1   100
1   24r     2009-07-13  2009-07-20  2   120
2   24r     2009-07-20  2009-07-27  3   110
3   34e     2009-08-06  2009-08-13  1   200
4   34e     2009-08-13  2009-08-20  2   180
5   34e     2009-08-20  2009-08-27  3   190
6   34r     2009-09-26  2009-10-03  1   150
7   34r     2009-10-03  2009-10-10  2   120
8   34r     2009-10-10  2009-10-17  3   140

user_5431791 · Answer

Try the following:

>>> s = df[['c1', 'c2', 'c3']].stack().reset_index(level=1, drop=True)
>>> t = df[['Start', 'End']]
          .apply(lambda x:pd.date_range(*x, freq='7D', closed='right'), axis=1)
>>> (df.reindex(s.index).assign(cAmount=s)
        .filter(regex=r'[^0-9]$', axis=1)
        .drop(['Start', 'End'], axis=1)
        .groupby(level=0)
        .apply(lambda x: x.assign(WeekNumber=x.index+1)
                          .assign(WeekStart=t[x.name].shift(-1))
                          .assign(WeekEnd=t[x.name]))
        .reset_index(drop=True)
     )

  identifier  cAmount  WeekNumber  WeekStart    WeekEnd
0        24r      100           1 2009-07-06 2009-07-13
1        24r      120           1 2009-07-13 2009-07-20
2        24r      110           1 2009-07-20 2009-07-27
3        34e      200           2 2009-08-06 2009-08-13
4        34e      180           2 2009-08-13 2009-08-20
5        34e      190           2 2009-08-20 2009-08-27
6        34r      150           3 2009-09-26 2009-10-03
7        34r      120           3 2009-10-03 2009-10-10
8        34r      140           3 2009-10-10 2009-10-17

BREAKDOWN

>>> s = df[['c1', 'c2', 'c3']].stack().reset_index(level=1, drop=True)
>>> s
0    100
0    120
0    110
1    200
1    180
1    190
2    150
2    120
2    140

>>> t = df[['Start', 'End']]
          .apply(lambda x:pd.date_range(*x, freq='7D', closed='right'), axis=1)
>>> t

0    DatetimeIndex(['2009-07-13', '2009-07-20', '2009-07-27'], dtype='datetime64[ns]', freq='7D')
1    DatetimeIndex(['2009-08-13', '2009-08-20', '2009-08-27'], dtype='datetime64[ns]', freq='7D')
2    DatetimeIndex(['2009-10-03', '2009-10-10', '2009-10-17'], dtype='datetime64[ns]', freq='7D')
dtype: object

>>> df.reindex(s.index).assign(cAmount=s)
  identifier      Start         End   c1   g1   c2  g2   c3   g3  cAmount
0        24r   7/6/2009   7/27/2009  100  1.0  120   2  110  3.0      100
0        24r   7/6/2009   7/27/2009  100  1.0  120   2  110  3.0      120
0        24r   7/6/2009   7/27/2009  100  1.0  120   2  110  3.0      110
1        34e   8/6/2009   8/27/2009  200  5.0  180   4  190  1.0      200
1        34e   8/6/2009   8/27/2009  200  5.0  180   4  190  1.0      180
1        34e   8/6/2009   8/27/2009  200  5.0  180   4  190  1.0      190
2        34r  9/26/2009  10/17/2009  150  NaN  120  23  140  5.0      150
2        34r  9/26/2009  10/17/2009  150  NaN  120  23  140  5.0      120
2        34r  9/26/2009  10/17/2009  150  NaN  120  23  140  5.0      140
>>> _.filter(regex=r'[^0-9]$', axis=1)

  identifier      Start         End  cAmount
0        24r   7/6/2009   7/27/2009      100
0        24r   7/6/2009   7/27/2009      120
0        24r   7/6/2009   7/27/2009      110
1        34e   8/6/2009   8/27/2009      200
1        34e   8/6/2009   8/27/2009      180
1        34e   8/6/2009   8/27/2009      190
2        34r  9/26/2009  10/17/2009      150
2        34r  9/26/2009  10/17/2009      120
2        34r  9/26/2009  10/17/2009      140

Then you need to drop ['Start', 'End'], and groupby(level=0) i.e. by index. And groupby.apply and pd.DataFrame.assign other columns in the following way

# lets consider the first group as x:
>>> grp = (df.reindex(s.index).assign(cAmount=s)
        .filter(regex=r'[^0-9]$', axis=1)
        .drop(['Start', 'End'], axis=1)
        .groupby(level=0))
>>> x = grp.get_group(name=0)
>>> x
  identifier  cAmount
0        24r      100
0        24r      120
0        24r      110

# inside apply, x.name == 0 for the first group, 
>>> (x.assign(WeekNumber=x.index+1)
      .assign(WeekStart=t[x.name].shift(-1))
      .assign(WeekEnd=t[x.name]))
  identifier  cAmount  WeekNumber  WeekStart    WeekEnd
0        24r      100           1 2009-07-06 2009-07-13
0        24r      120           1 2009-07-13 2009-07-20
0        24r      110           1 2009-07-20 2009-07-27
>>> _.reset_index(drop=True)
  identifier  cAmount
0        24r      100
0        24r      120
0        24r      110

How would I go about extracting data that is horizoantal and formatting vertiacally in python?

2 Answers