0

I have this table, weather_tb, where some values are misplaced into other columns. I want to move those values into their matching columns.

showing what values need to be moved

The value is not being set in the respective position in the Ta column and is not removed from the original column. what am I missing?

import re  # regex 
for i in (6, 12, 23, 34, 45, 56):  # rows with misplaced data
  for j in range(1, 19):  # columns in range
    if weather_tb.iloc[i][j] == None:
        continue  # testing if cell is a null value to skip

    if re.match("Ta=\d\.?\d+.*", weather_tb.iloc[i][j]) and j != 7:  # testing if there is a match for Ta and it's not on the Ta column
      x = re.search("Ta=\d\.?\d+.*", weather_tb.iloc[i][j])  # assigning the findings to x
      weather_tb.iloc[i][7] = x.group()  # writing the value in Ta column
      print(weather_tb.iloc[i][7])  # printing to verify .. it's showing none which is not expected 
      print(x.group())             # showing the found values no problem
      weather_tb.iloc[i][j] = None  # removing the value from the wrong cell ( current cell)

The value is not being set in the respective position in the Ta column and is not removed from the original column. what am I missing?

timestamp   Dn  Dm  Dx  Sn  Sm  Sx  Ta  Ua  Pa  ...     Rd  Ri  Hc  Ha  Hd  Hi  Vs  Vr  Th  R_type
0   1676907187  Dn=000#     Dm=000#     Dx=000#     Sn=0.0#     Sm=99.9#    Sx=0.0#\n\r     None    None    None    ...     None    None    None    None    None    None    None    None    None    R1
1   1676907188  Dn=000#     Dm=000#     Dx=000#     Sn=0.0#     Sm=99.9#    Sx=0.0#\n\r     None    None    None    ...     None    None    None    None    None    None    None    None    None    R1
2   1676907189  Dn=000#     Dm=000#     Dx=000#     Sn=0.0#     Sm=99.9#    Sx=0.0#\n\r     None    None    None    ...     None    None    None    None    None    None    None    None    None    R1
3   1676907190  Dn=000#     Dm=000#     Dx=000#     Sn=0.0#     Sm=99.9#    Sx=0.0#\n\r     None    None    None    ...     None    None    None    None    None    None    None    None    None    R1
4   1676907191  Dn=000#     Dm=000#     Dx=000#     Sn=0.0#     Sm=99.9#    Sx=0.0#\n\r     None    None    None    ...     None    None    None    None    None    None    None    None    None    R1
5   1676907192  Dn=000#     Dm=000#     Dx=000#     Sn=0.0#     Sm=99.9#    Sx=0.0#\n\r     None    None    None    ...     None    None    None    None    None    None    None    None    None    R1
6   1676907192  Th=11.5C    Vh=24.8V    Vs=25.2V    Vr=3.641V\n\r   None    None    None    None    None    ...     None    None    None    None    None    None    None    None    None    R5
7   1676907193  Dn=000#     Dm=000#     Dx=000#     Sn=0.0#     Sm=99.9#    Sx=0.0#\n\r     None    None    None    ...     None    None    None    None    None    None    None    None    None    R1
8   1676907194  Dn=000#     Dm=000#     Dx=000#     Sn=0.0#     Sm=99.9#    Sx=0.0#\n\r     None    None    None    ...     None    None    None    None    None    None    None    None    None    R1
9   1676907194  Dn=000#     Dm=000#     Dx=000#     Sn=0.0#     Sm=99.9#    Sx=0.0#     Ta=10.0C    Ua=44.7P    Pa=886.9H   ...     Rd=23580s   Ri=0.0M     Hc=0.0M     Hd=30s  Hi=0.0M     Vs=25.2V    Vr=3.641V\n\r   None    None    R0
10  1676907195  Dn=000#     Dm=000#     Dx=000#     Sn=0.0#     Sm=99.9#    Sx=0.0#\n\r     None    None    None    ...     None    None    None    None    None    None    None    None    None    R1
11  1676907196  Dn=000#     Dm=000#     Dx=000#     Sn=0.0#     Sm=99.9#    Sx=0.0#\n\r     None    None    None    ...     None    None    None    None    None    None    None    None    None    R1
12  1676907196  Ta=10.0C    Ua=44.7P    Pa=886.9H\n\r   None    None    None    None    None    None    ...     None    None    None    None    None    None    None    None    None    R2
13  1676907197  Dn=000#     Dm=000#     Dx=000#     Sn=0.0#     Sm=99.9#    Sx=0.0#\n\r     None    None    None    ...     None    None    None    None    None    None    None    None    None    R1
14  1676907198  Dn=000#     Dm=000#     Dx=000#     Sn=0.0#     Sm=99.9#    Sx=0.0#\n\r     None    None    None    ...     None    None    None    None    None    None    None    None    None    R1
Ian Thompson
  • 2,914
  • 2
  • 18
  • 31
Pansy HB
  • 1
  • 1
  • Something is fishy here. What is your original file? Do you need the var= in each item? – georgwalker45 Feb 20 '23 at 21:04
  • the data is being pulled through a web socket. it comes as strings that I add to an array them convert to a dataframe when I have collected 60 records. I was going to remove the var = after I moved the values so I can verify the numbers are in their right columns – Pansy HB Feb 21 '23 at 01:31

1 Answers1

0

Take a look at this answer. I think that it's your indexing.

https://stackoverflow.com/a/31569794/20649593

iloc is a property. It has setters and getters that are "hidden" so it can behave just like an attribute.

When you do df.iloc[1] you are calling the getter, and this returns a list. An additional modification is effectively this:

col1_list = df.iloc[1]
col1_list1[1] = 'Only changes local variable'

To do assignment with .iloc do this:

col1_list = df.iloc[1, 1] = 'Changes dataframe'

The fact that this code looks very hacky tells me that something is more fundamentally incorrect with the way the data gets into the able in the first place, but this is the error in your current code.

georgwalker45
  • 114
  • 7
  • the code is hacky indeed, I am not aware of another way to find those misplaced values and push them in their respective columns. I tried the iloc[i,j] it didn't change the results I am seeing. – Pansy HB Feb 21 '23 at 01:33
  • I would go the route of converting each response to a dictionary. Also you could assign just every value in the dataframe to something just to verify that you are actually modifying anything. I you put this first in your loop just as a sanity check – georgwalker45 Feb 21 '23 at 16:56