0

Can someone help me, I've been trying to concat number and values based on conditions but still error.

this is the document example that I can create from google sheet. https://docs.google.com/spreadsheets/d/1lESXb_DBcoH9y0UVNokB9HCKjsgBdQHlyHX5je78yR0/edit#gid=0

this is my previous code

    conditions = [
    (df['hour_generated'] == '8'),
    (df['hour_generated'] == '12'),
    (df['hour_generated'] == '17')
            ]

values = [[df_presto.segment_name + df_presto.date_generated-1 + 17].astype(str).str[-2:]
          , [df_presto.segment_name + df_presto.date_generated-1 + '18'].astype(str).str[-2:]
          , [df_presto.segment_name + df_presto.date_generated-1 + '12'].astype(str).str[-2:]
         ]

thanks and really appreciate

zanis
  • 5
  • 1
  • Can you please clarify what you are trying to do here? You are referencing **df** and **df_presto**. The spreadsheet you attached doesn't have the columns **hour_generated**. Please clarify and someone will be able to help you. – Jimmy Dec 24 '20 at 17:35
  • Hi, thanks for the comment, pls ignore df_presto, pls assume all use df, – zanis Dec 24 '20 at 17:40
  • what im trying to do is to get values like what exact I get in google sheet – zanis Dec 24 '20 at 17:40
  • It's still not very clear what you are trying to do here with the conditions and values. Either way, maybe one of the things that can help are the following: `str(datetime.strptime(df.date[1],'%d-%b-%y')- timedelta(days=1))` will make your date column go one day back and create a string (don't forget to **import datetime**) You also have the 17 in the first row that's not in quotes like the other rows, by the way. – Jimmy Dec 24 '20 at 17:52
  • basically what im trying to do is, IF hour generated = 8 then concat (segment_name + date_generate-1 + 17) notes date_generate -1 is pervious date of date_generate 17 is basically number – zanis Dec 24 '20 at 17:55

1 Answers1

0

This code below tries to recreate whatever you are doing in excel.

  1. convert datetime to ordinal/integer in which format excel stores dates(more here )
  2. Use np.select to use create new column using the condition and values we have defined.

Code

import datetime as dt

df['date'] = pd.to_datetime(df['date'])
df['ordinal_date'] = df['date'].map(dt.datetime.toordinal)-dt.datetime(1900, 1, 1).toordinal()+2

conditions = [ (df['hour'] == 8), 
               (df['hour'] == 12),
               (df['hour'] == 17) 
             ]

values = [df.segment + df.ordinal_date.astype(str)+"17",
          df.segment + df.ordinal_date.astype(str)+"8",
          df.segment + df.ordinal_date.astype(str)+"12"
         ]

df['new_col'] = np.select(conditions, values)
df

Out:

  segment       date  hour  ordinal_date   new_col
0       A 2020-12-23     8         44188  A4418817
1       B 2020-12-23    12         44188   B441888
2       C 2020-12-23    17         44188  C4418812
3       D 2020-12-23    17         44188  D4418812
Equinox
  • 6,483
  • 3
  • 23
  • 32
  • Hi @venky__ thank you, this is exactly what I want, thanks so much, 1 more questions, for segment A | 2020-12-23 | 8 what im trying to achieve is when we found 8, we will convert to yesterday, in above example is 2020-12-23 convert to 2020-12-22 so for the row 0 the result should be A4418717 instead of A4418817 – zanis Dec 26 '20 at 15:07