How to concatenate multiple column values into a single column in Panda dataframe based on start and end time

Question

I am new to Python and I am trying to create a database that is similar to this using pandas.

Below a simplified version of my df:

    Timestamp   A   B   C
0   2013-02-01  1   0   0
1   2013-02-02  2   10  18
2   2013-02-03  3   0   19
3   2013-02-04  4   12  20
4   2013-02-05  0   13  21
5   2013-02-06  6   14  22
6   2013-02-07  7   15  23
7   2013-02-08  0   0   0

First thing I did is create a new empty data frame to store the data in using this code:

# Create frequent pattern source database
df_frequent_pattern = pd.DataFrame(columns = ["Start Time", "End Time", "Active Appliances"])

# Create start_time and end_time series using pd.date_range
df_frequent_pattern["Start Time"] = pd.date_range("2013-02-1", "2013-02-08", freq = "D")
df_frequent_pattern["End Time"] = pd.date_range("2013-02-2", "2013-02-09", freq = "D")

Which gives the output below:

    Start Time  End Time    Active Appliances
0   2013-02-01  2013-02-02  NaN
1   2013-02-02  2013-02-03  NaN
2   2013-02-03  2013-02-04  NaN
3   2013-02-04  2013-02-05  NaN
4   2013-02-05  2013-02-06  NaN
5   2013-02-06  2013-02-07  NaN
6   2013-02-07  2013-02-08  NaN
7   2013-02-08  2013-02-09  NaN

Based on this and this Stack overflow post I wrote the following code to assign the appliances to the right time resolution:

# Add the data to the correct 'active' period based on interval and merge the active appliances in the "active appliances column"
# Row counter for the loop
rows = 8

for row in range(rows):
  # Check if appliance is active during time resoltuion
  if df_frequent_pattern["Start Time"] <= df["Timestamp"] | df["Timestamp" <= df_frequent_pattern["End Time"]:
    # Add all the appliance active during the time resolution to the column as a string value (e.g. "A, B, C")
     df_frequent_pattern["Active Appliances"] = df["A", "B", "C"].apply(lambda row: '_'.join(row.values.astype(str)), axis = 1)

Unfortunately the code doesn't work and I get the following error

df_frequent_pattern["Active Appliances"] = df["A", "B", "C"].apply(lambda row: '_'.join(row.values.astype(str)), axis = 1)
                                         ^
SyntaxError: invalid syntax

Yet, the '=' seems to be correctly placed based on the second post. Any idea on how I can use my df to get the expected results as illustrated above?

It should looks like this:

   Start Time   End Time    Active Appliances
0   2013-02-01  2013-02-02  "A"
1   2013-02-02  2013-02-03  "A,B,C"
2   2013-02-03  2013-02-04  "A,C"
3   2013-02-04  2013-02-05  "A,B,C"
4   2013-02-05  2013-02-06  "A,B,C"
5   2013-02-06  2013-02-07  "A,B,C"
6   2013-02-07  2013-02-08  "A,B,C"
7   2013-02-08  2013-02-09  ""

Answer 1

Let's do this in a few steps.

First, let's make sure your Timestamp is a datetime.

df['Timestamp'] = pd.to_datetime(df['Timestamp'])

Then we can create a new dataframe based on a min and max values of your timestamp.

df1 = pd.DataFrame({'start_time' : pd.date_range(df['Timestamp'].min(), df['Timestamp'].max())})

df1['end_time'] = df1['start_time'] + pd.DateOffset(days=1)

 start_time   end_time
0 2013-02-01 2013-02-02
1 2013-02-02 2013-02-03
2 2013-02-03 2013-02-04
3 2013-02-04 2013-02-05
4 2013-02-05 2013-02-06
5 2013-02-06 2013-02-07
6 2013-02-07 2013-02-08
7 2013-02-08 2013-02-09

---

Now we need to create a dataframe to merge onto your start_time column.

Let's filter out any values that are less than 0 and create a list of active appliances:

df = df.set_index('Timestamp')
# the remaining columns MUST be integers for this to work. 
# or you'll need to subselect them. 
df2 = df.mask(df.le(0)).stack().reset_index(1).groupby(level=0)\
                 .agg(active_appliances=('level_1',list)).reset_index(0)

# change .agg(active_appliances=('level_1',list) > 
# to .agg(active_appliances=('level_1',','.join)
# if you prefer strings.



    Timestamp active_appliances
0 2013-02-01               [A]
1 2013-02-02         [A, B, C]
2 2013-02-03            [A, C]
3 2013-02-04         [A, B, C]
4 2013-02-05            [B, C]
5 2013-02-06         [A, B, C]
6 2013-02-07         [A, B, C]

---

Then we can merge:

final = pd.merge(df1,df2,left_on='start_time',right_on='Timestamp',how='left').drop('Timestamp',1)


  start_time   end_time active_appliances
0 2013-02-01 2013-02-02               [A]
1 2013-02-02 2013-02-03         [A, B, C]
2 2013-02-03 2013-02-04            [A, C]
3 2013-02-04 2013-02-05         [A, B, C]
4 2013-02-05 2013-02-06            [B, C]
5 2013-02-06 2013-02-07         [A, B, C]
6 2013-02-07 2013-02-08         [A, B, C]
7 2013-02-08 2013-02-09               NaN