如何在Pandas数据框中填充缺失的秒数

huangapple
go评论88阅读模式
英文:

how to fill missing seconds in pandas dataframe

问题

这是你要的翻译结果：

  1. 我有一个数据框，我想要在时间数据框中填充丢失的秒值，应该如何做到这一点
  2. 这是我的数据
  4. df = pd.DataFrame({
  5. 'sec': [1, 2, 3, 4, 5, 6, 7, 8, 9, 10],
  6. 'Date': ['7/7', '0', '0', '7/7', '7/7', '0', '7/7', '7/7', '0', '0'],
  7. 'Time': ['12:47:30', '0', '0', '12:47:33', '12:47:34', '0', '12:47:36', '12:47:37', '0', '0'],
  8. 'rpm': [0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0]
  9. })
  11. 在时间列中，从12:47:30开始，0的位置应该是12:47:31。换句话说，我的期望输出是：
  13. df = pd.DataFrame({
  14. 'sec': [1, 2, 3, 4, 5, 6, 7, 8, 9, 10],
  15. 'Date': ['7/7', '0', '0', '7/7', '7/7', '0', '7/7', '7/7', '0', '0'],
  16. 'Time': ['12:47:30', '12:47:31', '12:47:32', '12:47:33', '12:47:34', '12:47:35', '12:47:36', '12:47:37', '12:47:38', '12:47:39'],
  17. 'rpm': [0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0]
  18. })
英文:

I have a data frame I want to fill missing seconds values in Time data frame how to do that
this is my data

  1. df = pd.DataFrame({
  2. 'sec': [1, 2, 3, 4, 5, 6, 7, 8, 9, 10],
  3. 'Date': ['7/7', '0', '0', '7/7', '7/7', '0', '7/7', '7/7', '0', '0'],
  4. 'Time': ['12:47:30', '0', '0', '12:47:33', '12:47:34', '0', '12:47:36', '12:47:37', '0', '0'],
  5. 'rpm': [0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0]
  6. })

In Time column after 12:47:30 in place of 0 it has to be 12:47:31. In other words, my expected output is:

  1. df = pd.DataFrame({
  2. 'sec': [1, 2, 3, 4, 5, 6, 7, 8, 9, 10],
  3. 'Date': ['7/7', '0', '0', '7/7', '7/7', '0', '7/7', '7/7', '0', '0'],
  4. 'Time': ['12:47:30', '12:47:31', '12:47:32', '12:47:33', '12:47:34', '12:47:35', '12:47:36', '12:47:37', '12:47:38', '12:47:39'],
  5. 'rpm': [0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0]
  6. })

答案1

得分: 2

以下是您要翻译的内容：

  1. 首先创建`DatetimeIndex`，然后使用[`DataFrame.resample`](http://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.resample.html)，最后设置列的值：
  3. df.index = pd.to_datetime(df['Date'] + df['Time'].astype(str), 
  4.                           format='%m/%d%H:%M:%S', 
  5.                           errors='coerce')
  7. out = df.resample('S').first()
  9. out['Time'] = out.index.time
  10. out['Date'] = out.index.strftime('%m/%d')
  11. out['rpm'] = out['rpm'].fillna(0)
  12. out['sec'] = out.groupby('Date').cumcount().add(1)
  13. print(out)
  15. out = out.reset_index(drop=True)
  16. print(out)

另一种解决方法是使用Series.ffill进行日期的前向填充，同时使用GroupBy.cumcount和to_timedelta来创建非时间值的秒数：

  1. dates = pd.to_datetime(df['Date'] + df['Time'].astype(str), 
  2.                       format='%m/%d%H:%M:%S', 
  3.                       errors='coerce')
  5. sec = pd.to_timedelta(df.groupby(dates.notna().cumsum()).cumcount(), unit='s')
  7. df['Time'] = dates.ffill().add(sec).dt.strftime('%H:%M:%S')
  8. print(df)
英文:

Create DatetimeIndex first and then use DataFrame.resample, last set columns values:

  1. df.index = pd.to_datetime(df['Date'] + df['Time'].astype(str), 
  2.                           format='%m/%d%H:%M:%S', 
  3.                           errors='coerce')
  5. out = df.resample('S').first()
  7. out['Time'] = out.index.time
  8. out['Date'] = out.index.strftime('%m/%d')
  9. out['rpm'] = out['rpm'].fillna(0)
  10. out['sec'] = out.groupby('Date').cumcount().add(1)
  11. print (out)
  12.                      sec   Date      Time  rpm
  13. 1900-07-07 12:47:30    1  07/07  12:47:30  0.0
  14. 1900-07-07 12:47:31    2  07/07  12:47:31  0.0
  15. 1900-07-07 12:47:32    3  07/07  12:47:32  0.0
  16. 1900-07-07 12:47:33    4  07/07  12:47:33  0.0
  17. 1900-07-07 12:47:34    5  07/07  12:47:34  0.0
  18. 1900-07-07 12:47:35    6  07/07  12:47:35  0.0
  19. 1900-07-07 12:47:36    7  07/07  12:47:36  0.0
  20. 1900-07-07 12:47:37    8  07/07  12:47:37  0.0
  1. out = out.reset_index(drop=True)
  2. print (out)
  3.    sec   Date      Time  rpm
  4. 0    1  07/07  12:47:30  0.0
  5. 1    2  07/07  12:47:31  0.0
  6. 2    3  07/07  12:47:32  0.0
  7. 3    4  07/07  12:47:33  0.0
  8. 4    5  07/07  12:47:34  0.0
  9. 5    6  07/07  12:47:35  0.0
  10. 6    7  07/07  12:47:36  0.0
  11. 7    8  07/07  12:47:37  0.0

Another solution with forward filling dates by Series.ffill with add second for non times values created by GroupBy.cumcount and to_timedelta:

  1. dates = pd.to_datetime(df['Date'] + df['Time'].astype(str), 
  2.                           format='%m/%d%H:%M:%S', 
  3.                           errors='coerce')
  5. sec = pd.to_timedelta(df.groupby(dates.notna().cumsum()).cumcount(), unit='s')
  7. df['Time'] = dates.ffill().add(sec).dt.strftime('%H:%M:%S')
  8. print (df)
  9.    sec Date      Time  rpm
  10. 0    1  7/7  12:47:30  0.0
  11. 1    2    0  12:47:31  0.0
  12. 2    3    0  12:47:32  0.0
  13. 3    4  7/7  12:47:33  0.0
  14. 4    5  7/7  12:47:34  0.0
  15. 5    6    0  12:47:35  0.0
  16. 6    7  7/7  12:47:36  0.0
  17. 7    8  7/7  12:47:37  0.0
  18. 8    9    0  12:47:38  0.0
  19. 9   10    0  12:47:39  0.0

答案2

得分: 2

另一个可能的解决方案，使用线性插值来填充空时间：

  1. from scipy.interpolate import interp1d
  3. df['Time'] = pd.to_datetime(df['Time'], format='%H:%M:%S', errors='coerce')
  4. df_nonan = df[['sec', 'Time']].dropna()
  5. f = interp1d(df_nonan.iloc[:, 0], df_nonan.iloc[:, 1], fill_value='extrapolate')
  6. df['Time'] = pd.to_datetime(f(df['sec']))
  7. df['Time'] = df['Time'].dt.time

输出：

  1.    sec Date      Time  rpm
  2. 0    1  7/7  12:47:30  0.0
  3. 1    2    0  12:47:31  0.0
  4. 2    3    0  12:47:32  0.0
  5. 3    4  7/7  12:47:33  0.0
  6. 4    5  7/7  12:47:34  0.0
  7. 5    6    0  12:47:35  0.0
  8. 6    7  7/7  12:47:36  0.0
  9. 7    8  7/7  12:47:37  0.0
  10. 8    9    0  12:47:38  0.0
  11. 9   10    0  12:47:39  0.0
英文:

Another possible solution, which uses linear interpolation to fill the null times:

  1. from scipy.interpolate import interp1d
  3. df['Time'] = pd.to_datetime(df['Time'], format='%H:%M:%S', errors='coerce')
  4. df_nonan = df[['sec', 'Time']].dropna()
  5. f = interp1d(df_nonan.iloc[:, 0], df_nonan.iloc[:, 1],
  6.              fill_value='extrapolate')
  7. df['Time'] = pd.to_datetime(f(df['sec']))
  8. df['Time'] = df['Time'].dt.time

Output:

  1.    sec Date      Time  rpm
  2. 0    1  7/7  12:47:30  0.0
  3. 1    2    0  12:47:31  0.0
  4. 2    3    0  12:47:32  0.0
  5. 3    4  7/7  12:47:33  0.0
  6. 4    5  7/7  12:47:34  0.0
  7. 5    6    0  12:47:35  0.0
  8. 6    7  7/7  12:47:36  0.0
  9. 7    8  7/7  12:47:37  0.0
  10. 8    9    0  12:47:38  0.0
  11. 9   10    0  12:47:39  0.0

答案3

得分: 1

将Time列转换为.to_datetime，并在前一个时间上添加一秒，如下所示：

代码:

  1. # 将'Time'列转换为datetime，将'0'值转换为NaT（不是时间）
  2. df['Time'] = pd.to_datetime(df['Time'], format='%H:%M:%S', errors='coerce')
  4. # 遍历'Time'列，将NaT值替换为前一个时间加一秒
  5. previous_time = None
  6. for i, time in enumerate(df['Time']):
  7.     if pd.isnull(time):
  8.         new_time = (previous_time + timedelta(seconds=1))
  9.         df.at[i, 'Time'] = new_time
  10.         previous_time = new_time
  11.     else:
  12.         previous_time = time
  14. df['Time'] = df['Time'].apply(lambda x: x.strftime('%H:%M:%S'))

输出:

  1. sec   Date   Time       rpm
  2. 0     1    7/7  12:47:30  0.0
  3. 1     2    0    12:47:31  0.0
  4. 2     3    0    12:47:32  0.0
  5. 3     4    7/7  12:47:33  0.0
  6. 4     5    7/7  12:47:34  0.0
  7. 5     6    0    12:47:35  0.0
  8. 6     7    7/7  12:47:36  0.0
  9. 7     8    7/7  12:47:37  0.0
  10. 8     9    0    12:47:38  0.0
  11. 9    10    0    12:47:39  0.0
英文:

Convert Time column .to_datetime and add one second to previous time, as show below

Code:

  1. # Convert 'Time' column to datetime and '0' values to NaT (Not a time)
  2. df['Time'] = pd.to_datetime(df['Time'], format='%H:%M:%S', errors='coerce')
  4. # Iterate over the 'Time' column and replace NaT values 
  5. # with the time by adding one second to the previous time
  6. previous_time = None
  7. for i, time in enumerate(df['Time']):
  8.     if pd.isnull(time):
  9.         new_time = (previous_time + timedelta(seconds=1))
  10.         df.at[i, 'Time'] = new_time
  11.         previous_time = new_time
  12.     else:
  13.         previous_time = time
  15. df['Time'] = df['Time'].apply(lambda x: x.strftime('%H:%M:%S'))

Output:

  1. sec	Date	Time	rpm
  2. 0	1	7/7	12:47:30	0.0
  3. 1	2	0	12:47:31	0.0
  4. 2	3	0	12:47:32	0.0
  5. 3	4	7/7	12:47:33	0.0
  6. 4	5	7/7	12:47:34	0.0
  7. 5	6	0	12:47:35	0.0
  8. 6	7	7/7	12:47:36	0.0
  9. 7	8	7/7	12:47:37	0.0
  10. 8	9	0	12:47:38	0.0
  11. 9	10	0	12:47:39	0.0

答案4

得分: 0

由于您的列始终以一秒为增量，您可以使用pd.date_range来简单地“创建”它。

以下行提供了所需的输出。

  1. df['Time'] = pd.date_range(start='12:47:30', end='12:47:39', freq='s')

如果您有一个大型数据集，可以使用periods参数来指定要创建的值的数量，而无需指定结束时间。

英文:

Since your column always increments by one second, you can just "create" it with pd.date_range

The following line gives the desired output.

  1. df['Time'] = pd.date_range(start='12:47:30', end='12:47:39', freq='s')

If you have a big dataset, instead of specifying the end, you can simply pass the number of values to create with the periods parameter.

答案5

得分: 0

这是您想要的代码：

  1. import pandas as pd
  3. df = pd.DataFrame({
  4.     'sec': [1, 2, 3, 4, 5, 6, 7, 8, 9, 10],
  5.     'Date': ['7/7', '0', '0', '7/7', '7/7', '0', '7/7', '7/7', '0', '0'],
  6.     'Time': ['12:47:30', '0', '0', '12:47:33', '12:47:34', '0', '12:47:36', '12:47:37', '0', '0'],
  7.     'rpm': [0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0]
  8. })
  10. # 创建一个用于标记具有'0'时间值的掩码
  11. mask = df['Time'] == '0'
  13. # 找到第一个非零时间值的索引
  14. first_nonzero_idx = df.loc[~mask, 'Time'].index[0]
  16. # 将'Time'列转换为列表以便更容易操作
  17. times = df['Time'].tolist()
  19. # 通过从前一个非零时间值递增来填充缺失的时间值
  20. for i in range(first_nonzero_idx + 1, len(times)):
  21.     if mask[i]:
  22.         prev_time = pd.to_datetime(times[i-1])
  23.         times[i] = (prev_time + pd.DateOffset(seconds=1)).strftime('%H:%M:%S')
  25. # 在数据框中更新'Time'列
  26. df['Time'] = times
  28. print(df)

输出：

  1.    sec Date      Time  rpm
  2. 0    1  7/7  12:47:30  0.0
  3. 1    2    0  12:47:31  0.0
  4. 2    3    0  12:47:32  0.0
  5. 3    4  7/7  12:47:33  0.0
  6. 4    5  7/7  12:47:34  0.0
  7. 5    6    0  12:47:35  0.0
  8. 6    7  7/7  12:47:36  0.0
  9. 7    8  7/7  12:47:37  0.0
  10. 8    9    0  12:47:38  0.0
  11. 9   10    0  12:47:39  0.0

希望这对您有所帮助！

英文:

Here is the code that you want:

  1. import pandas as pd
  3. df = pd.DataFrame({
  4.     'sec': [1, 2, 3, 4, 5, 6, 7, 8, 9, 10],
  5.     'Date': ['7/7', '0', '0', '7/7', '7/7', '0', '7/7', '7/7', '0', '0'],
  6.     'Time': ['12:47:30', '0', '0', '12:47:33', '12:47:34', '0', '12:47:36', '12:47:37', '0', '0'],
  7.     'rpm': [0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0]
  8. })
  10. # Create a mask for rows with '0' time values
  11. mask = df['Time'] == '0'
  13. # Find the index of the first non-zero time value
  14. first_nonzero_idx = df.loc[~mask, 'Time'].index[0]
  16. # Convert the 'Time' column to a list for easier manipulation
  17. times = df['Time'].tolist()
  19. # Fill in the missing time values by incrementing from the previous non-zero time value
  20. for i in range(first_nonzero_idx + 1, len(times)):
  21.     if mask[i]:
  22.         prev_time = pd.to_datetime(times[i-1])
  23.         times[i] = (prev_time + pd.DateOffset(seconds=1)).strftime('%H:%M:%S')
  25. # Update the 'Time' column in the dataframe
  26. df['Time'] = times
  28. print(df)

Output:

  1.    sec Date      Time  rpm
  2. 0    1  7/7  12:47:30  0.0
  3. 1    2    0  12:47:31  0.0
  4. 2    3    0  12:47:32  0.0
  5. 3    4  7/7  12:47:33  0.0
  6. 4    5  7/7  12:47:34  0.0
  7. 5    6    0  12:47:35  0.0
  8. 6    7  7/7  12:47:36  0.0
  9. 7    8  7/7  12:47:37  0.0
  10. 8    9    0  12:47:38  0.0
  11. 9   10    0  12:47:39  0.0

huangapple
  • 本文由 发表于 2023年7月10日 18:21:06
  • 转载请务必保留本文链接：https://go.coder-hub.com/76652806.html
  • pandas
  • python
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定