Why does pandas.dataframe.info() not update when I delete the first row of a dataset?

Question

I am working with a dataset that comes in with nonsense field names in the first row, with the actual field names in the second row.

Currently I'm using this script:

import pandas as pd
import matplotlib.pyplot as plt

df = pd.read_csv('CAR_551.csv')
df1 = df.iloc[1:,:]
print(df1.head(10))
df1.info()

The dataset appears to be successfully updated. When I print the dataset the first row is gone, however when I call df1.info() it still returns the original headers. Example output:

 #   Column                 Non-Null Count  Dtype 
---  ------                 --------------  ----- 
 0   StartDate              10 non-null     object
 1   EndDate                10 non-null     object
 2   Status                 10 non-null     object
 3   IPAddress              10 non-null     object
 4   Progress               10 non-null     object
 5   Duration (in seconds)  10 non-null     object
 6   Finished               10 non-null     object
 7   RecordedDate           10 non-null     object
 8   ResponseId             10 non-null     object
 9   RecipientLastName      0 non-null      object
 10  RecipientFirstName     0 non-null      object
 11  RecipientEmail         0 non-null      object
 12  ExternalReference      0 non-null      object
 13  LocationLatitude       10 non-null     object
 14  LocationLongitude      10 non-null     object
 15  DistributionChannel    10 non-null     object
 16  UserLanguage           10 non-null     object
 17  Q6#1_1                 10 non-null     object
 18  Q6#1_2                 10 non-null     object
 19  Q6#1_3                 10 non-null     object
 20  Q6#1_4                 10 non-null     object
 21  Q6#1_5                 10 non-null     object
 22  Q6#1_6                 10 non-null     object
 23  Q6#1_7                 10 non-null     object
 24  Q6#1_8                 10 non-null     object
 25  Q6#1_9                 10 non-null     object
 26  Q6#2_1                 10 non-null     object
 27  Q6#2_2                 10 non-null     object
 28  Q6#2_3                 10 non-null     object
 29  Q6#2_4                 10 non-null     object
 30  Q6#2_5                 10 non-null     object
 31  Q6#2_6                 10 non-null     object
 32  Q6#2_7                 10 non-null     object
 33  Q6#2_8                 10 non-null     object
 34  Q6#2_9                 10 non-null     object
 35  Q8                     10 non-null     object
 36  Q9                     10 non-null     object
dtypes: object(37)
memory usage: 3.0+ KB

These are all field names from the first row of the dataset. Is there a way to get this to update and show the field names from row 2 of the data?

Can someone explain the underlying mechanism of what's going on here? How is the first row even stored in the new dataframe at all if I specified only the second row on should be included when I declared the variable?

Answer 1

The solution is better achieved via,

df = pd.read_csv('CAR_551.csv', skiprows=[0])

---

I checked the solutions.

Solution 1

df.columns = df.iloc[0]

There's a problem because a blank line (or line of junk) is now carried into the dataframe. The outcome will depend on what the first row of the csv is. So the minimum it will do is append a blank line above dataframe.

Thus, df.to_csv('myfile') will start with a blank line, before the column headers. Its not clear what this is doing to internal dataframe operations. More seriously it can also include elements of the first junk row of the csv file. In my example I had a column name at iloc[24], axis=1 retained, which junk. It appears the behaviour is unpredictable.

It would cause problems if this was reimported, because the same problem of the first row is junk continues.

Solution 2

df = pd.read_csv('CAR_551.csv', skiprows=[0])

Simply doesn't import the junk blank line and new 'phantom' lines are no longer part of the dataframe. The to_write is perfect, the first line is the header.

Answer 2

I was able to figure this out. After removing the original first row, I hade to declare that the new first row were to be used as column names. Code below:

import pandas as pd
import matplotlib.pyplot as plt

df = pd.read_csv('CAR_551.csv')
df.columns = df.iloc[0]

df1 = df.iloc[:,17:]
df2 = df1.replace({pre_substring:"PRE", post_substring:"POST"}, regex=True)
df2.columns = df2.iloc[0]