I read my pandas dataframe as:

ERA5RS12 = pd.read_csv('F:/ERA5_RS/[12]-PIRATA-2017_20171127_100021/wspd_hl.csv')

ERA5RS12

My output is

I want some like this:

Latitude,Longitude,Value,Level

9,33,8.4,20

9,33,10,40

9,33,11,100

CodePudding user response：

If I were you, I would write my own reader to read data like this. With the following code, you can have your column names in header list, and your table rows in lines list.

with open('test.csv', 'r') as file:
    
    header = []
    lines = []
    
    for i, line in enumerate(file):
        
        if i == 0:
            header = line.split()
            continue
            
        if i % 2 == 0:
            continue
            
        lines.append(line.split())

When the data are processed, you can easily create pd.DataFrame, and clean the data.

df = pd.DataFrame(lines, columns=header)
df['Value'] = df.Value.str.strip(',').astype(float)

The result is following.

>> df.head()
  Latitude Longitude   Value level
0    9.000   321.990  6.6732    20
1    9.000   321.990  6.8282    30
2    9.000   321.990  6.9814    50
3    9.000   321.990  7.0756    70
4    9.000   321.990  7.1705   100

CodePudding user response：

Ensure that the delimiter of your CSV file is ,. Otherwise, you can specify a custom delimiter corresponding to the file with parameter sep while calling pd.read_csv. see a documentation.

Edited: According to the Excel file you provided, I convert it to CSV format. Here is a snippet to produce what you need.

import pandas as pd

with open("wspd_hl.csv", "r") as f:
    lines = [line.strip() for line in f.readlines()]

df = pd.DataFrame(columns=lines[0].split(" "))
for i, line in enumerate(lines[1:], 1):
    if i % 2 == 1:
        line = line.replace(",", "")
        df.loc[len(df)] = [value for value in line.split(" ") if value.strip() != ""]

Output: