You are viewing a plain text version of this content. The canonical link for it is here.
Posted to github@arrow.apache.org by "edandresvan (via GitHub)" <gi...@apache.org> on 2023/07/04 20:32:22 UTC
[GitHub] [arrow-datafusion] edandresvan opened a new issue, #6845: dataframe.with_column_renamed() does not rename properly
edandresvan opened a new issue, #6845:
URL: https://github.com/apache/arrow-datafusion/issues/6845
### Describe the bug
The function Dataframe.with_column_renamed() cannot change uppercase single words to lower case words.
For instance,
```rust
df.with_column_renamed("Package", "package")?; // Produces "Package". Should be "package".
df.with_column_renamed("City Name", "city_name")?; // Produces "city_name". OK.
```
### To Reproduce
I have the following CSV table
|City Name|Package |Variety |Date |Low Price|High Price|Mostly Low|
|---------|------------|-----------|-------|---------|----------|----------|
|BALTIMORE|24 inch bins|HOWDEN TYPE|9/24/16|160 |160 |160 |
|BALTIMORE|24 inch bins|HOWDEN TYPE|9/24/16|160 |160 |160 |
|BALTIMORE|24 inch bins|HOWDEN TYPE|11/5/16|90 |100 |90 |
I load the CVS data with this code:
```rust
let mut config = SessionConfig::new();
config = config.with_information_schema(true);
let csv_options = CsvReadOptions::new()
.has_header(true)
.schema_infer_max_records(2000);
let mut df = context.read_csv("data/pumpkins.csv", CsvReadOptions::new()).await?;
```
Then, I try to change the column names from uppercase to lower case and replacing white spaces with underscores using this code:
```rust
let field_names: Vec<String> = df.schema().fields().iter().map(|f| f.name().to_string()).collect::<Vec<String>>();
println!("{:?}", field_names);
let re = Regex::new(r"\s+").unwrap();
for field in field_names {
let new_field = field.clone().trim().to_lowercase();
let new_field = re.replace_all(&new_field, " ");
let new_field: String = new_field.to_lowercase().trim().replace(" ", "_");
println!("{} -> {}", field, new_field);
df = df.with_column_renamed(field, &new_field)?;
}
df = df.with_column_renamed("Package", "package")?;
let field_names: Vec<String> = df.schema().fields().iter().map(|f| f.name().to_string()).collect::<Vec<String>>();
println!("{:?}", field_names);
```
And I get this results:
```text
["City Name", "Package", "Variety", "Date", "Low Price", "High Price", "Mostly Low"]
City Name -> city_name
Package -> package
Variety -> variety
Date -> date
Low Price -> low_price
High Price -> high_price
Mostly Low -> mostly_low
["city_name", "Package", "Variety", "Date", "low_price", "high_price", "mostly_low"]
```
As you can see, the function Dataframe.with_column_renamed() does not change the columns with an initial uppercase letter, even calling outside the for loop.
```rust
df = df.with_column_renamed("Package", "package")?;
```
### Expected behavior
I would like the function changes the column name regardless of the case
```rust
df = df.with_column_renamed("Package", "package")?; // produces ‘package’
df = df.with_column_renamed("PACKAGE", "package")?; // produces ‘package’
```
I had to use the format function and escaping quotation marks in the for loop in order to get my desired output from the Dataframe.with_column_renamed() function.
```rust
// (...)
for field in field_names {
//(...)
df = df.with_column_renamed(format!("\"{}\"", field), &new_field)?;
}
// (...)
```
The dataframe.with_column_renamed() should change the column name properly. In my case, I want his output:
```text
["City Name", "Package", "Variety", "Date", "Low Price", "High Price", "Mostly Low"]
City Name -> city_name
Package -> package
Variety -> variety
Date -> date
Low Price -> low_price
High Price -> high_price
Mostly Low -> mostly_low
["city_name", "package", "variety", "date", "low_price", "high_price", "mostly_low"]
```
|city_name|package |variety |date |low_price|high_price|mostly_low|
|---------|------------|-----------|-------|---------|----------|----------|
|BALTIMORE|24 inch bins|HOWDEN TYPE|9/24/16|160 |160 |160 |
|BALTIMORE|24 inch bins|HOWDEN TYPE|9/24/16|160 |160 |160 |
|BALTIMORE|24 inch bins|HOWDEN TYPE|11/5/16|90 |100 |90 |
I think it would be also useful to add a function to normalize all columns names to lower case and underscores
```rust
dataframe.to_lowercase() // produces ["city_name", "date", "low_price", "high_price"]
CsvReadOptions::new()::to_lowercase(true) // produces ["city_name", "date", "low_price", "high_price"]
```
### Additional context
_No response_
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] Jefffrey commented on issue #6845: dataframe.with_column_renamed() does not rename properly
Posted by "Jefffrey (via GitHub)" <gi...@apache.org>.
Jefffrey commented on issue #6845:
URL: https://github.com/apache/arrow-datafusion/issues/6845#issuecomment-1633183449
@comphead I think your example is flawed as you're using the original DF at the end which doesn't have a column named `CoLuMn1`, instead of the renamed one
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] alamb closed issue #6845: dataframe.with_column_renamed() does not rename properly
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb closed issue #6845: dataframe.with_column_renamed() does not rename properly
URL: https://github.com/apache/arrow-datafusion/issues/6845
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] Jefffrey commented on issue #6845: dataframe.with_column_renamed() does not rename properly
Posted by "Jefffrey (via GitHub)" <gi...@apache.org>.
Jefffrey commented on issue #6845:
URL: https://github.com/apache/arrow-datafusion/issues/6845#issuecomment-1636906066
@comphead
Yeah I believe it's because Spark's query analyzer is case insensitive by default:
https://github.com/apache/spark/blob/master/sql/catalyst/src/main/scala/org/apache/spark/sql/internal/SQLConf.scala#L889-L895
So selecting with a column name of wrong case will still find correct column.
Whereas for DataFusion, I guess for now it is case sensitive. Could introduce config like Spark does to toggle the case sensitivity for analyzer/planner, but that would require more work.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] comphead commented on issue #6845: dataframe.with_column_renamed() does not rename properly
Posted by "comphead (via GitHub)" <gi...@apache.org>.
comphead commented on issue #6845:
URL: https://github.com/apache/arrow-datafusion/issues/6845#issuecomment-1633227931
@Jefffrey ouch, you are right. I'm adding valid test case
```
#[tokio::test]
async fn with_column_renamed_case_sensitive() -> Result<()> {
let df = test_table()
.await?
.filter(col("c2").eq(lit(3)).and(col("c1").eq(lit("a"))))?
.limit(0, Some(1))?
.sort(vec![
// make the test deterministic
col("c1").sort(true, true),
col("c2").sort(true, true),
col("c3").sort(true, true),
])?
.select_columns(&["c1"])?;
let df_renamed = df
.clone()
.with_column_renamed("c1", "CoLuMn1")?;
let res = &df_renamed.clone().collect().await?;
assert_batches_sorted_eq!(
vec![
"+---------+",
"| CoLuMn1 |",
"+---------+",
"| a |",
"+---------+",
],
res
);
let df_renamed = df_renamed
.with_column_renamed("CoLuMn1", "c1")?
.collect()
.await?;
assert_batches_sorted_eq!(
vec![
"+----+",
"| c1 |",
"+----+",
"| a |",
"+----+",
],
&df_renamed
);
Ok(())
}
```
So as I confused everyone I will try to fix the issue then :)
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] Jefffrey commented on issue #6845: dataframe.with_column_renamed() does not rename properly
Posted by "Jefffrey (via GitHub)" <gi...@apache.org>.
Jefffrey commented on issue #6845:
URL: https://github.com/apache/arrow-datafusion/issues/6845#issuecomment-1632369943
Seems root cause is how when searching for the column to rename, `Package`, you need to ensure the string you passed in is properly quoted as noted in the original issue:
```rust
.with_column_renamed("\"Package\"", "package")
```
Since otherwise it'll look for column `package` which doesn't exist.
https://github.com/apache/arrow-datafusion/blob/d6985b0fc1e4362b80beea059857c6bde65b4d04/datafusion/core/src/dataframe.rs#L1015-L1029
Maybe make the search for the field on line 1022, `field_from_column(...)` more smarter by allowing to do case insensitive match as fallback if can't find an exact match?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] comphead commented on issue #6845: dataframe.with_column_renamed() does not rename properly
Posted by "comphead (via GitHub)" <gi...@apache.org>.
comphead commented on issue #6845:
URL: https://github.com/apache/arrow-datafusion/issues/6845#issuecomment-1636890794
@Jefffrey DF works correctly with quota style scenario
```
df.withColumnRenamed("c1", "`CoLuMn1`").withColumnRenamed("`CoLuMn1`", "c1")
```
Do we really want to make it the same behaviuor as spark? this may be confusing for the system
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] comphead commented on issue #6845: dataframe.with_column_renamed() does not rename properly
Posted by "comphead (via GitHub)" <gi...@apache.org>.
comphead commented on issue #6845:
URL: https://github.com/apache/arrow-datafusion/issues/6845#issuecomment-1632741386
```
#[tokio::test]
async fn with_column_renamed_case_sensitive() -> Result<()> {
let df = test_table()
.await?
.filter(col("c2").eq(lit(3)).and(col("c1").eq(lit("a"))))?
.limit(0, Some(1))?
.sort(vec![
// make the test deterministic
col("c1").sort(true, true),
col("c2").sort(true, true),
col("c3").sort(true, true),
])?
.select_columns(&["c1"])?;
let df_renamed = df
.clone()
.with_column_renamed("c1", "CoLuMn1")?
.collect()
.await?;
assert_batches_sorted_eq!(
vec![
"+---------+",
"| CoLuMn1 |",
"+---------+",
"| a |",
"+---------+",
],
&df_renamed
);
let df_renamed = df
.with_column_renamed("CoLuMn1", "c1")?
.collect()
.await?;
assert_batches_sorted_eq!(
vec![
"+----+",
"| c1 |",
"+----+",
"| a |",
"+----+",
],
&df_renamed
);
Ok(())
}
```
Looks like it works on latest DF. Please verify @edandresvan
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] alamb commented on issue #6845: dataframe.with_column_renamed() does not rename properly
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #6845:
URL: https://github.com/apache/arrow-datafusion/issues/6845#issuecomment-1637090986
> Whereas for DataFusion, I guess for now it is case sensitive. Could introduce config like Spark does to toggle the case sensitivity for analyzer/planner, but that would require more work.
https://arrow.apache.org/datafusion/user-guide/configs.html
This one might work:
datafusion.sql_parser.enable_ident_normalization | true | When set to true, SQL parser will normalize ident (convert ident to lowercase when not quoted)
-- | -- | --
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] comphead commented on issue #6845: dataframe.with_column_renamed() does not rename properly
Posted by "comphead (via GitHub)" <gi...@apache.org>.
comphead commented on issue #6845:
URL: https://github.com/apache/arrow-datafusion/issues/6845#issuecomment-1622459714
Hi @edandresvan
you can achieve the same with SQL API
```
❯ select 1 as "Package";
+---------+
| Package |
+---------+
| 1 |
+---------+
```
As for Dataframe API you I have checked how Spark does and it renames the column as per your suggestion
```
scala> sql("select 1 a").withColumnRenamed("a", "Packe").printSchema
root
|-- Packe: integer (nullable = false)
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org