How do I add a column based on a condition and assign values to it based on the syntax of values fro-CodePudding

I have a column I wish to add called "Survey Name". I am trying to use a conditional statement to add this column if it does not exist in the dataframe.

conditional column inserter code:

if "Survey Name" not in df2:
    df2.assign["Survey Name"]

Next. I wish to add the same value throughout the entire column. This value is based off of the syntax values of a column named "Survey Sent Date"

What I want it to do is to assign this value: "QxYY Our Voice - CBS" where x is the quarter and YY is the year. (E.g. if the date was "2022-03-23 08:00:00" then the value would be: "Q122 Our Voice - CBS"

I already have something that can create syntax based on the "Survey Sent Date" I just need to assign its output as a value to the "Survey Name" column.

Quarter detection and syntax code:

"Q"   str(df2.loc[:, "Survey Sent Date"].iloc[1].quarter) \
  str(df2.loc[:, "Survey Sent Date"].iloc[1].year)\ 
  " Our Voice - CBS"

How would I use this Quarter detection and syntax code and attach it to the conditional column inserting code so that the output of the syntax code is assigned as a value in the newly inserted column?

The "Survey Sent Date" column is displayed like:

  df = pd.DataFrame({'Survey Sent Date': ["2022-03-23 08:00:00", "2022-03-23 08:00:00", "2022-03-23 08:00:00", \
                              "2022-03-23 08:00:00", "2022-03-23 08:00:00"]})
    
df.loc[:, "Survey Sent Date"] = pd.to_datetime(df.loc[:, "Survey Sent Date"], errors='coerce')

print(df)

CodePudding user response：

Since you've converted your columns to datetime, you can do this quite easily using dt and str methods:

if "Survey Name" not in df:
    df["Survey Name"] = ("Q" 
                           df['Survey Sent Date'].dt.quarter.astype(str) 
                           df['Survey Sent Date'].dt.year.astype(str).str[2:] 
                           " Our Voice - CBS")

Output:

       Survey Sent Date     Survey Name
0   2022-03-23 08:00:00     Q122 Our Voice - CBS
1   2022-03-23 08:00:00     Q122 Our Voice - CBS
2   2022-03-23 08:00:00     Q122 Our Voice - CBS
3   2022-03-23 08:00:00     Q122 Our Voice - CBS
4   2022-03-23 08:00:00     Q122 Our Voice - CBS

By the way, you can replace df.loc[:, col] with df[col].

CodePudding user response：

You can use apply:

def my_logic(date):
    return "Q"   str(date.quarter)   str(date.year)   " Our Voice - CBS"

df = pd.DataFrame({'Survey Sent Date': ["2022-03-23 08:00:00", "2022-03-23 08:00:00", "2022-03-23 08:00:00", \
                              "2022-03-23 08:00:00", "2022-03-23 08:00:00"]})

df.loc[:, "Survey Sent Date"] = pd.to_datetime(df.loc[:, "Survey Sent Date"], errors='coerce')

df["Survey Name"] = df["Survey Sent Date"].apply(my_logic)