CODEWITHSUNDEEP
pythonpandastext
Kulwant
Kulwant

Reputation: 681

Converting a string into data frame

I have extracted a file and got the file in the following format. The XXXX's are values to be ignored. What I am attempting to do is extract the values out of the data frame. The values I am looking at are after A. Below is what the string from the file looks like. The second diagram is what I am attempting to get the data frame.

Text

XXXXXX
XXXXX
XXXXXX

Date

Time

A

2021-01-17
2021-01-18
XXXXXX
XXXXX
XXXXXX


12
14

21
2

This is what I am trying to get the data frame to look like.

enter image description here

I tried to read the data frame separating the lines in and not sure how to proceed further.

df = pd.read_csv(io.StringIO(text), sep='\n') # This puts the text into the data frame
df[7:14] # This would return a slice in the database

Upvotes: 0

Views: 52

Answers (1)

Davinder Singh
Davinder Singh

Reputation: 2162

import pandas as pd
import numpy as np

text = '''
     # <your text value
'''
# cleaning the text
text = [j for j in [i for i in text.split('\n') if i != '' ] if j[0] != 'X']
text.insert(5, 'B')
text.insert(8, 'C')
text.remove('Date')
text.remove('Time')

text = [text[i*3:i*3+3] for i in range(3)]   
df = pd.DataFrame(np.array(text).T)
print(df)

OUTPUT :
enter image description here

Upvotes: 1

Related Questions