What I want is I have a DataFrame (click_df2) :-
date L120_active_cohort_logins L120_active_cohort percentage_L120_active_cohort_logins
0 2022-09-03 45000 199000 22.621906
1 2022-09-04 40000 200000 19.092138
Now based on this DataFrame I want to alter the value of all the columns based on date given in the DataFrame
This is how I have created my clickhouse table :-
query = '''CREATE TABLE IF NOT EXISTS repeat_day_by_last_120_active_cohort_v1
(
date Date,
L120_active_cohort_logins Int,
L120_active_cohort Int,
percentage_L120_active_cohort_logins Float
) ENGINE = MergeTree()
ORDER BY date'''
Code is as follows this Is what I am trying to do :-
click_df2 = pd.read_csv(f'{location}/csv_files/main_data.csv',header=0)
click_df2['date'] = pd.to_datetime(click_df2['date'],dayfirst=True)
client.execute(f'''ALTER TABLE repeat_day_by_last_120_active_cohort_v1 \
UPDATE 'L120_active_cohort_logins' = "{click_df2["L120_active_cohort_logins"]}", \
'L120_active_cohort' = "{click_df2["L120_active_cohort"]}", \
'percentage_L120_active_cohort_logins' = "{click_df2["percentage_L120_active_cohort_logins"]}" \
WHERE 'date' = "{click_df2["date"]}"''')
Data present in the clickhouse table repeat_day_by_last_120_active_cohort_v1 :-
date L120_active_cohort_logins L120_active_cohort percentage_L120_active_cohort_logins
0 2022-09-01 32679 195345 16.728865
1 2022-09-02 32938 196457 16.766010
2 2022-09-03 40746 197586 20.621906
3 2022-09-04 33979 198799 17.092138
after altering the table repeat_day_by_last_120_active_cohort_v1 data should be:-
date L120_active_cohort_logins L120_active_cohort percentage_L120_active_cohort_logins
0 2022-09-01 32679 195345 16.728865
1 2022-09-02 32938 196457 16.766010
2 2022-09-03 45000 199000 22.621906
3 2022-09-04 40000 200000 19.092138