1

hi I have the following txt file

December

line: 285 - event ID: 67511
line: 296 - event ID: 67512

November

line: 305 - event ID: 67515
line: 300 - event ID: 67517

I want to transform it into the following data frame

df1 = pd.DataFrame(
    {   
        "index":     ["December",  "December",  "November", "November"],
        "index1":    ["285",       "296",       "305",      "300"],
        "eventid":   ["67511",     "67512",     "64515",    "64517"]})


     index     index1    eventid
0   December    285       67511
1   December    296       67512
2   November    305       64515
3   November    300       64517

any ideas?

xavi
  • 80
  • 1
  • 12

1 Answers1

2

I have used pattern matching to achieve what you need:

import re
import pandas as pd

res = []
month_pattern = re.compile("^\w+$")
line_pattern = re.compile("\d+")
current_month = ""
with open("FILE_PATH_TO_YOUR_DATA", "r") as f:
    for line in f:
        m = month_pattern.findall(line)
        if len(m) > 0:
            current_month = m[0]
        m = line_pattern.findall(line)
        if len(m) > 0:
            res.append([current_month] + m)

df = pd.DataFrame(res, columns = ["index", "index1", "eventid"])

print(df)

OUTPUT

      index index1 eventid
0  December    285   67511
1  December    296   67512
2  November    305   67515
3  November    300   67517
joanis
  • 10,635
  • 14
  • 30
  • 40
nikeros
  • 3,302
  • 2
  • 10
  • 26
  • 1
    with PATTERN_TO_YOUR_DATA you mean the directory where my data are stored. In that i case, i use the following with open({"C:/Users/.../Desktop/NewData/weather.txt"}, "r") as f: and throws me this error: TypeError: expected str, bytes or os.PathLike object, not set – xavi Dec 04 '21 at 19:42
  • @xavi exaclty - let me make it clearer – nikeros Dec 04 '21 at 19:43
  • can you help with the error? Am I doing something wrong when specifying my data? – xavi Dec 04 '21 at 19:45
  • Replace `{FILE...}` by `"C:..."`, *removing* the curly braces. – joanis Dec 04 '21 at 19:54
  • open("C:/Users/.../Desktop/NewData/weather.txt", "r") – nikeros Dec 04 '21 at 20:31