I have the following response from reading a gsheet from google API.
response = [['Owner', 'Database', 'Schema', 'Table', 'Column', 'Comment', 'Status'], ['', 'VICE_DEV', 'AIRFLOW', 'TASK_INSTANCE', '_LOAD_DATETIME', 'Load datetime'], ['', 'VICE_DEV', 'AIRFLOW', 'TEST', '_LOAD_FILENAME', 'load file name', 'ADDED']]
and created a df from the response:
df = pd.DataFrame(response)
Index 0 1 2 3 4 5 6
0 Owner Database Schema ... Column Comment Status
1 VICE_DEV AIRFLOW ... _LOAD_DATETIME Load datetime None
2 VICE_DEV AIRFLOW ... _LOAD_FILENAME load file name ADDED
How do I get the 0 row to become the column names instead?
I tried this:
df.columns = df.iloc[0]
and it works in a way, but then I still see the 0 row as the column names (this is not correct)
Index Owner Database Schema ... Column Comment Status
0 Owner Database Schema ... Column Comment Status
1 VICE_DEV AIRFLOW ... _LOAD_DATETIME Load datetime None
2 VICE_DEV AIRFLOW ... _LOAD_FILENAME load file name ADDED
>Solution :
You can for example directly set the first row as columns and use the rest as rows:
response = [['Owner', 'Database', 'Schema', 'Table', 'Column', 'Comment', 'Status'], ['', 'VICE_DEV', 'AIRFLOW', 'TASK_INSTANCE', '_LOAD_DATETIME', 'Load datetime'], ['', 'VICE_DEV', 'AIRFLOW', 'TEST', '_LOAD_FILENAME', 'load file name', 'ADDED']]
df = pd.DataFrame(response[1:], columns=response[0])
df
Output:
Owner Database Schema Table Column Comment Status
0 VICE_DEV AIRFLOW TASK_INSTANCE _LOAD_DATETIME Load datetime None
1 VICE_DEV AIRFLOW TEST _LOAD_FILENAME load file name ADDED
You can also add one line to the code that you provided and achieve the same result:
df = pd.DataFrame(response)
df.columns = df.iloc[0]
# Remove first row
df = df[1:]