Trying to read excel table that looks like this:
| B | C | |
|---|---|---|
| A | data | data |
| data | data | data |
but read excel doesn't recognizes that one column doesn't start from first row and it reads like this:
| Unnamed : 0 | B | C |
|---|---|---|
| A | data | data |
| data | data | data |
Is there a way to read data like i need? I have checked parameters like header = but thats not what i need.
A similar question was asked/solved here. So basically the easiest thing would be to either drop the first column (if thats always the problematic column) with
df = pd.read_csv('data.csv', index_col=0)
or remove the unnamed column via
df = df.loc[:, ~df.columns.str.contains('^Unnamed')]
You can skip automatic column labeling with something like pd.read_excel(..., header=None)
This will skip random labeling.
Then you can use more elaborate computation (e.g. first non empty value) to get the labels such as
df.apply(lambda s: s.dropna().reset_index(drop=True)[0])
It is definitely not the most gracious solution but in terms of directly achieving what you want and not removing a column, this should work:
import pandas as pd
df = pd.read_excel('your-file.xlsx')
column_list = df.columns.to_list()
df.columns = ['' if "Unnamed" in x else x for x in column_list]
print(df)
>>>
Column B Column C
0 Row 1 This Is A
1 Row 2 small test file
For this, you do not need to worry about the header argument, which defaults to 0.
It simply extracts the columns and convert them to a list, and then with a list comprehension replaces any string with Unnamed in it to an empty string.
